Forum Discussion

gahenry's avatar
gahenry
Copper Contributor
Sep 12, 2023

How to remove ".." in csv

Hello

 

I am pretty green at this so please forgive me if this is a real stupid question...

 

I need to run a SQLCMD on a server 2019 machine and export results to a CSV.   I have tried to format the output file in the SQLCMD and while it almost works I Have had some issues with the formatting. 

 

It was suggested that I use PowerShell and the export-CSV command and I can tell the results are better and almost have it working but the export-csv command puts ".." around the data as you see below.

 

the top line are headers and that is OK but I need to find a way to strip the quotes.

If I open it in excel, excel strips the quotes but I do not use excel, it needs to be directly imported into another program and the quotes are causing a problem.

 

I found an article on stackoverflow, but to be honest I get a little intimidated when I try to look at Stackoverflow because I am so green and when I have tried to ask a newbie question, they get frustrated at me.

 

Below you will find the full PowerShell script I am trying to run... I tried to adopt the syntax from an example of the export-csv command from a stackoverflow post, but I had some confusion since they were talking about things that work and do not work on PowerShell version 7.  and based on the error I am getting; I do not think this command is complete...

 

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

 

 

I am running windows server 2019 PowerShell 5.1.14463

 

As FYI.. I have tried to install PowerShell 7 but I got some kind of error when I run my script because SQLCMD would not run.

 

any help or guidance (even a little pity) would be appreciated.

 

thanks 

 

Gary

 

 

 

 

@Windows PowerShell
sql server

4 Replies

Sort By
  • LainRobertson's avatar
    LainRobertson
    Silver Contributor
    Sep 12, 2023

    gahenry 

     

    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:

     

    1. Fetch the output from SQL;
    2. Manipulate it (in this case, remove double quotes;
    3. Output it to file.

     

    Cheers,

    Lain

    • gahenry's avatar
      gahenry
      Copper Contributor
      Sep 14, 2023
      Hi and thank you for your help and guidance. When I applied your recommendations I got this error...

      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
      • LainRobertson's avatar
        LainRobertson
        Silver Contributor
        Sep 14, 2023

        gahenry 

         

        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

Resources