Forum Discussion
How to remove ".." in csv
Hi, Gary.
You don't need PowerShell 7.x or anything like that for this, as the requirements are basic.
Here's your original (a little more readable now that it's in a code block) plus a suggestion to check out.
Example
# What you have is this.
Invoke-Sqlcmd -Server 192.168.0.4 -Username PAReadonly2 -Password "<password>" -Database lc_V3_acephoto_net -InputFile "C:\Users\Amber\Documents\SQL Server Management Studio\MPNsimple.sql" |
Export-Csv -NoTypeInformation -Path c:\junk\test.csv |
Select-Object -Skip 1 |
ForEach-Object { $_ -replace '"' } |
Set-Content $NewCSV;
# What you need is this.
Invoke-Sqlcmd -Server 192.168.0.4 -Username PAReadonly2 -Password "<password>" -Database lc_V3_acephoto_net -InputFile "C:\Users\Amber\Documents\SQL Server Management Studio\MPNsimple.sql" |
ConvertTo-Csv -NoTypeInformation |
Select-Object -Skip 1 |
ForEach-Object { $_.Replace("""", "") } |
Out-File -FilePath $NewCSV;
I'm not clear on why the Select-Object -Skip 1 is in there, but I've left it in regardless.
The main conceptual hurdle with your original is the Export-Csv appearing on the second line, as outputting to the file is the final thing you want to do. Basically, you want to - in order:
- Fetch the output from SQL;
- Manipulate it (in this case, remove double quotes;
- Output it to file.
Cheers,
Lain
It complained that encoding was not specified so I was not sure what to use so I chose ASCII...
can you help me understand what I did wrong?
Don't I Have to define the variable $newCSV somehow??
----
PS Invoke-Sqlcmd -Server 192.168.0.4 -Username PAReadonly2 -Password "<password>" -Database lc_V3_acephoto_net -InputFile "C:\Users\Amber\Documents\SQL Server Management Studio\MPNsimple.sql" |
>> ConvertTo-Csv -NoTypeInformation |
>> ForEach-Object { $_.Replace("""", "") } |
>> Out-File -encoding ASCII -FilePath "c:\junk\" $NewCSV;
Out-File : A positional parameter cannot be found that accepts argument '$null'.
At line:4 char:17
+ ... Out-File -encoding ASCII -FilePath "c:\junk\" $NewCSV;
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidArgument: (:) [Out-File], ParameterBindingException
+ FullyQualifiedErrorId : PositionalParameterNotFound,Microsoft.PowerShell.Commands.OutFileCommand
thanks for any additional helpyou can provide
Gary
Hi, Gary.
If this is under Windows PowerShell 5.1 then I'm not sure why it's complaining about needing the -Encoding parameter, as that's not something I've ever needed to be provided. Still, that's not the issue now.
The issue is what you've done with the final line, which currently looks like this:
Out-File -encoding ASCII -FilePath "c:\junk\" $NewCSV;The issue is the space between "c:\junk\" and $NewCSV, as that's not a valid filename. Secondly, if you haven't given $NewCSV a value earlier on then that's probably also why the error mentioned being unable to use a "$null" value.
The last line should look like any of the following:
# Option 1: This will only work if you've assigned a name (excluding the path to the file) to $NewCSV beforehand. Out-File -Encoding ASCII -FilePath "c:\junk\$NewCSV"; # Option 2: Specify the full filename manually, removing $NewCSV entirely. Out-File -Encoding ASCII -FilePath "c:\junk\test.csv"; # Option 3: This will work whether you assign just a filename or the full path (including the filename). Out-File -Encoding ASCII -FilePath $NewCSV;Cheers,
Lain
- thanks everyone for your help.