How to export csv file with a comma in the data by SQL server management studio (SSMS)

INNINNII 20 Reputation points
2023-07-07T22:19:27.13+00:00

I am trying to export csv files with headers in SSMS. I am able to do so with the code below.

EXEC master..xp_cmdshell 'sqlcmd -s "," -W -Q "set nocount on; select top(10) * from [Myserver].[dbo].[Mytable]" -o "D:\test\AAA.csv"'

However, some of the columns in the table are long text with comma, which make it separated when export to csv. I wonder how to fixed this and make the long text stay in its own columns in csv.

I tried to enclose all columns with double quotes, but it is also hard to do so.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,483 questions
0 comments No comments
{count} votes

Accepted answer
  1. Yitzhak Khabinsky 26,376 Reputation points
    2023-07-09T02:58:28.0066667+00:00

    Hi @INNINNII,

    *.csv/txt flat files always have host of problems where column delimiters, column separators, invisible characters like null terminators \0, special characters based on encoding, and line breaks are in the middle of the actual data. Or data elements are missing completely.

    The most reliable format for data feeds is XML enforced by an XSD. An XSD plays a role of a data contract between sender and receiver. It will guarantee proper data format/shape, data types, cardinality, and enforce data quality.

    For your immediate need you can try to use QUOTENAME() function.

    It will enclose columns in question with double quotes.

    -- DDL and sample data population, start
    DECLARE @tbl TABLE (id INT IDENTITY PRIMARY KEY, Product VARCHAR(256), case_date DATE);
    INSERT @tbl (Product, case_date) VALUES
    ('Bolt EV, 278 miles', '2022-10-27'),
    ('Tesla Model 2, 300 miles', '2024-05-27');
    -- DDL and sample data population, end
    
    -- or CONCAT() for strings longer than 256 chars
    SELECT ID, Product = QUOTENAME(Product, CHAR(34)), case_date
    FROM @tbl;
    
    2 people found this answer helpful.

4 additional answers

Sort by: Most helpful
  1. Viorel 119.9K Reputation points
    2023-07-08T08:41:03.1533333+00:00

    If the table is not huge, then execute select * from Table, go to the results, right-click the grid, select “Save Results As...”, etc. The quotation marks will be added if needed.

    1 person found this answer helpful.

  2. Erland Sommarskog 117.1K Reputation points MVP
    2023-07-08T08:05:06.49+00:00

    SQLCMD is not intended for data export, and it may not work out well anyway.

    I think you are better off writing a program/script in your favourite language data and that writes the file. Then you can easily deal with adding the quoting without having to list all individual columns.

    You can also try the Export wizard (right-click a database and select Tasks->Export data) or craft your own SSIS package.

    If you want to do this in an all-SQL solution it is likely to be frustrating and cumbersome. But find the table in Object Explorer, and drag the Columns node to the query window. Then use Find/Replace to change , to \n (probably need to select Regular expressions for this.) Once you have then in a vertical list, press Shift-Alt and then use the down-arrow to make a rectangular selection. If you now start typing, the text will be inserted on all lines.


  3. ZoeHui-MSFT 40,586 Reputation points
    2023-07-10T02:03:50.3933333+00:00

    Hi @INNINNII,

    You may try to use SSIS to load the data to csv file.

    It could use some component to keep the long text with comma.

    Regards,

    Zoe Hui


    If the answer is helpful, please click "Accept Answer" and upvote it.


  4. Alpesh Dhori 0 Reputation points
    2023-07-20T07:04:32.33+00:00

    As Yitzhak suggest please use QUOTENAME as "," is quite common on the content of the column. 'QUOTENAME' Very useful SQL function.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.