1

Upon using a text editor to review exported results from SSMS to CSV I'm witnessing extra double quotes around the result values - not field names. I've used the concat function in my script to manually add a single pair of double quotes around each value and field name. So where I would expect "012345678" I'm actually seeing """012345678""".

It may be that my code is a bit too rudimentary

ex.:

SELECT CONCAT('"',ISNULL('012345678',''),'"') AS '"employee_id"'
FROM employees

More fields are selected I just included one as an example.

Any direction is greatly appreciated.

Thom A
  • 88,727
  • 11
  • 45
  • 75
Corey
  • 11
  • 1
  • 3
  • 1
    Don't quote the values in your SQL, let the export process do that. Effectively you're saying the value you want to export is `"012345678"` (***with*** the double quotes), however, as your CSV is quote identified, the double quotes in the string need to be escaped (to `""`), resulting in `"""012345678"""`. The first and last double quotes (`"`) are the quote identifiers, and then the others are the escaped double quotes from your value. – Thom A Sep 09 '20 at 15:02
  • Don't do that single quote + double quote thing in the column alias either. You don't even need to quote that identifier anyway. – shawnt00 Sep 09 '20 at 15:06
  • Larnu, I'm needing double quotes to appear when reviewing the export in a text editor or Excel. When I remove the quotes from script, I don't visibly see the needed double quotes in the export. – Corey Sep 09 '20 at 15:16
  • When you say "in the export" do you mean, for example, in Excel? If so, then no, you won't. If you open the file in a text editor, like notepad, you will. – Thom A Sep 09 '20 at 15:20

1 Answers1

0

You shouldn't manually add in the " around your data fields. If you want a character around the fields use the Text qualifier option. In SSMS when using Task > Export to open the SQL Server Import and Export Wizard, set the Text qualifier as shown below and whatever character you set for the Text qualifier will be automatically put around each field.

enter image description here

You can find some documentation about text qualfiier here: https://learn.microsoft.com/en-us/sql/integration-services/import-export-data/connect-to-a-flat-file-data-source-sql-server-import-and-export-wizard?view=sql-server-ver15#options-to-specify-general-page

vvvv4d
  • 3,881
  • 1
  • 14
  • 18