Extra double quotes on export from SSMS to CSV

Question

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.

Answer 1

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.

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