Dumping SQL Query Result to Text (quoting issue)

PowerShell Help
1

Hi, forgive me, I’m a PowerShell newbie, the cloud app we just migrated to has a CLI that integrates with PowerShell, so I’m trying to learn as fast as I can.

I’m trying to use the code below to export SQL Server data to a text file, which is later uploaded to the cloud…

Everything looks great, except that last field (amount) cannot be wrapped in quotes.

Import-Module sqlps

$startDateString = Get-Date -format "yyyy_MM_dd_HH_mm_ss"

Invoke-Sqlcmd -Query "select * from vw_MyView" `
    -serverinstance localhost `
    -database my_database | `
   Export-Csv `
   -Path "E:\MyPath\$startDateString MyTextFile.txt" `
   -delimiter "`t" `
   -notypeinformation

This is the actual output to MyTextFile.txt:

“Product X” “Customer 33” “45.11”
“Product Y” “Customer 14” “215.75”
“Product Z” “Customer 91” “16.32”

And this is the desired output to MyTextFile.txt (notice no quotes around last field):

“Product X” “Customer 33” 45.11
“Product Y” “Customer 14” 215.75
“Product Z” “Customer 91” 16.32

So I think my options are:

  1. Have the SQL view export just one column, and do all the quoting and concatenating in that one field.
  2. Change the ‘write to file’ approach in PowerShell from Write-CSV to some other method.
  3. Keep the SQL and PowerShell Write-CSV the same, but parse/rewrite the output in a later step to remove the double quotes around the last field (complicated?)

I sort of like option 3 if it’s easy to do because I’ve gotten this far, and I just like this approach, but I’m happy to hear suggestions from the experts!

Thanks!

2

dwooderson36,
Welcome back to the forum. :wave:t4: Long time no see. :wink:

There’s nothing needed to be forgiven. :wink:

That shouldn’t be an issue for all tools able to work with standard compliant CSV data. It is plain text actually anyway.

PowerShell is actually able to work with CSV data out of the box. You can use Export-Csv to export data to a CSV file. By default are all fields enclosed in double quotes - just as specified in the standard. :wink:

Please always read the help for the cmdlets you’re about to use completely including the examples to learn how to use them. At the bottom of each help topic you can find even more related help topics.

If you insist to have a non standard CSV file you can use Export-Csv from PowerShell version 7. There you can specify which columns you want to have with quotes.

And BTW: Backticks as line continuation characters are considered as very bad style. You should use splatting instead.

Edit 1:

  • Before I forget … when you post sample data or console output you should format it as code as well. This would make it easier for us to copy and to use it to play around with it if needed.

Thanks in advance.

Edit 2:

  • Ooops I actually missed that you’re already using Export-Csv because you use *.txt as extension. Sorry … :flushed: But anyway - my suggestion stays the same.