Export-CSV -Append

Ask your PowerShell-related questions, including questions on cmdlet development!
Forum rules
Do not post any licensing information in this forum.

Any code longer than three lines should be added as code using the 'Select Code' dropdown menu or attached as a file.
This topic is 6 years and 5 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked
User avatar
markcusano
Posts: 21
Last visit: Mon Oct 23, 2017 1:22 pm

Export-CSV -Append

Post by markcusano »

Hello and Thanks in advance.

Here is settings for my app:
Powershell Studio 2016
Platform: Sapien Powershell V2 Host (Command line) x64
Generate .config file = checked
STA Mode = Not checked
Embed a default manifest
Run Mode: Current User
No signing or execution restrictions

I'm trying to export-csv and append a simple query to a csv file using invoke-sqlcmd:

Invoke-Sqlcmd -ServerInstance $InstanceName -Database $DatabaseName -InputFile $TSQLScript | Export-Csv -Path "$OutPutFileName.csv" -Append -NoTypeInformation

Anyone know why I cannot append to the file using Export-CSV?
It is an option to append for Export-CSV, but it kicks back an error: InvalidArgument: Export-Csv, ParameterBindingException
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Export-CSV -Append

Post by jvierra »

Post the complete error.
User avatar
markcusano
Posts: 21
Last visit: Mon Oct 23, 2017 1:22 pm

Re: Export-CSV -Append

Post by markcusano »

$Database.name -InputFile $CurrentDatabaseScript | Export-Csv -Path "$OutputFolderPath\$Server\$OutPutFileName.csv" -Append <<<< -NoTypeInformation
ERROR: + CategoryInfo : InvalidArgument: (:) [Export-Csv], ParameterBindingException
ERROR: + FullyQualifiedErrorId : NamedParameterNotFound,Microsoft.PowerShell.Commands.ExportCsvCommand
ERROR:
ERROR: Export-Csv : A parameter cannot be found that matches parameter name 'Append'.
ERROR: At line:292 char:178
ERROR: + Invoke-Sqlcmd -ServerInstance $Server -Database $Database.name -InputFile $CurrentDatabaseScript | Export-Csv -Path "$OutputFolderPath\$Server\$OutPutFileName.csv" -Append <<<< -NoTypeInformation



ERROR: + CategoryInfo : InvalidArgument: (:) [Export-Csv], Paramete
rBind
ERROR: ingException
ERROR: + FullyQualifiedErrorId : NamedParameterNotFound,Microsoft.PowerShell
.Comm
ERROR: ands.ExportCsvCommand
ERROR:
ERROR: Export-Csv : A parameter cannot be found that matches parameter name 'App
end'.
ERROR:
ERROR: At line:292 char:178
ERROR: + Invoke-Sqlcmd -ServerInstance $Server -Database
$Data
ERROR: base.name -InputFile $CurrentDatabaseScript | Export-Csv -Path "$OutputFo
lderPa
ERROR: th\$Server\$OutPutFileName.csv" -Append <<<< -NoTypeInformation
ERROR: + CategoryInfo : InvalidArgument: (:) [Export-Csv], Paramete
rBind
ERROR: ingException
ERROR: + FullyQualifiedErrorId : NamedParameterNotFound,Microsoft.PowerShell
.Comm
ERROR: ands.ExportCsvCommand
ERROR:
ERROR: Export-Csv : A parameter cannot be found that matches parameter name 'App
end'.
ERROR:
ERROR: At line:292 char:178
ERROR: + Invoke-Sqlcmd -ServerInstance $Server -Database
$Data
ERROR: base.name -InputFile $CurrentDatabaseScript | Export-Csv -Path "$OutputFo
lderPa
ERROR: th\$Server\$OutPutFileName.csv" -Append <<<< -NoTypeInformation
ERROR: + CategoryInfo : InvalidArgument: (:) [Export-Csv], Paramete
rBind
ERROR: ingException
ERROR: + FullyQualifiedErrorId : NamedParameterNotFound,Microsoft.PowerShell
.Comm
ERROR: ands.ExportCsvCommand
ERROR:
ERROR: Export-Csv : A parameter cannot be found that matches parameter name 'App
end'.
ERROR:
ERROR: At line:292 char:178
ERROR: + Invoke-Sqlcmd -ServerInstance $Server -Database
$Data
ERROR: base.name -InputFile $CurrentDatabaseScript | Export-Csv -Path "$OutputFo
lderPa
ERROR: th\$Server\$OutPutFileName.csv" -Append <<<< -NoTypeInformation
ERROR: + CategoryInfo : InvalidArgument: (:) [Export-Csv], Paramete
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Export-CSV -Append

Post by jvierra »

"-Append" is not available on PS V2 and earlier.
User avatar
markcusano
Posts: 21
Last visit: Mon Oct 23, 2017 1:22 pm

Re: Export-CSV -Append

Post by markcusano »

If I bump up to Powershell V3 Host (Command line) x64
I get the following errors.





Excep
ERROR: tion
ERROR: + FullyQualifiedErrorId : System.IO.FileLoadException,Microsoft.SqlSe
rver.
ERROR: Management.PowerShell.GetScriptCommand
ERROR:
ERROR: Invoke-Sqlcmd : Mixed mode assembly is built against version 'v2.0.50727'
of
ERROR: the runtime and cannot be loaded in the 4.0 runtime without additional
ERROR: configuration information.
ERROR: At line:258 char:6
ERROR: + Invoke-Sqlcmd -ServerInstance $Server -InputFile
ERROR: $CurrentServerScript | Exp ...
ERROR: +
ERROR: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ERROR: + CategoryInfo : NotSpecified: (:) [Invoke-Sqlcmd], FileLoad
Excep
ERROR: tion
ERROR: + FullyQualifiedErrorId : System.IO.FileLoadException,Microsoft.SqlSe
rver.
ERROR: Management.PowerShell.GetScriptCommand
ERROR:
ERROR: Invoke-Sqlcmd : Mixed mode assembly is built against version 'v2.0.50727'
of
ERROR: the runtime and cannot be loaded in the 4.0 runtime without additional
ERROR: configuration information.
ERROR: At line:258 char:6
ERROR: + Invoke-Sqlcmd -ServerInstance $Server -InputFile
ERROR: $CurrentServerScript | Exp ...
ERROR: +
ERROR: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Export-CSV -Append

Post by jvierra »

You have the wrong module in your PSModulePath.

Code: Select all

PS D:\scripts> $env:PsModulePath -split ';'
D:\Users\MySelf\Documents\WindowsPowerShell\Modules
C:\WINDOWS\system32\WindowsPowerShell\v1.0\Modules
C:\Program Files\WindowsPowerShell\Modules
C:\Program Files\SharePoint Online Management Shell\
C:\Program Files\Common Files\Skype for Business Online\Modules\
C:\Program Files (x86)\Microsoft SQL Server\140\Tools\PowerShell\Modules\
C:\Program Files (x86)\Microsoft SQL Server\130\Tools\PowerShell\Modules\
C:\Program Files\Microsoft SQL Server\110\Tools\PowerShell\Modules\
Notice that SQL is in the path in reverse order of version: 140,130,110

This ensures that the newest module or component will always be found first.
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Export-CSV -Append

Post by jvierra »

I should lso note that this folder should be at the front of all SQL entries:

C:\WINDOWS\system32\WindowsPowerShell\v1.0\Modules

The newest SQLServer module is installed here by default.
User avatar
markcusano
Posts: 21
Last visit: Mon Oct 23, 2017 1:22 pm

Re: Export-CSV -Append

Post by markcusano »

Thank you for your assistance, jvierra. I've tried arranging my $env:PSModulePath to be like yours and it doesn't fix the problem. I also tried to load the full module path directly in my code. I've found the articles below to be consistent with this error, but I'm not sure how to apply this fix or where to put the code. Can you help me understand where to put this code?




Here is a link that I've found in many forums related to the exact error I'm getting:

https://blogs.msdn.microsoft.com/aseemb ... formation/



From the link above, it states:


One of the release management customer reported that while running a PS/DSC deployment script, the release management release is failing with following error:

Mixed mode assembly is built against version 'v2.0.50727' of the runtime and cannot be loaded in the 4.0 runtime without additional configuration information.

Praneeth Yenugutala from the team investigated this and observed that it occurs because the support for side-by-side runtime from .NET 4.0 onwards has changed w.r.t. the way with which .NET assembly binds to older mixed-mode assemblies (example: - assemblies compiled from C++\CLI, directX assemblies etc). Since the process which carries the PS/DSC deployment in release management is built against .NET framework 4.5 older .NET mixed-mode binaries fail to load in our deployment context.

To get past this error till the actual fix is done in the product, you should not load such mixed-mode dll’s directly in the .ps1 script. You should launch a new process in .ps1 and load them in that context. Having this configuration block in the assembly’s app.config file(which is launched as a new process) should work:

<startup useLegacyV2RuntimeActivationPolicy="true">

</startup>
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Export-CSV -Append

Post by jvierra »

The issue posted Is old and occurs if you only have the 2.0 sql support installed. If you have installed the later version then the fix I posted will work assuming your PowerShell support is installed correctly.
The app.config will affect every PowerShell program.

The line goes in the app.config for PowerShell. As noted in the article this is not a good idea and is a temp fix for C++ or other programs being developed.

You should upgrade to a supported version of PowerShell and this will not be necessary.
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Export-CSV -Append

Post by jvierra »

This is how:

Code: Select all

<?xml version="1.0" encoding="utf-8"?>
<configuration>
    <startup>
      <supportedRuntime version="v4.0.30319" />
    </startup>
</configuration>
This topic is 6 years and 5 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked