SQL Server CSV extract of tables with newline, doublequotes and commas in columns?

I extracted some 10 tables in CSV with " as the text qualifier. Problem is my extract does not look right in Excel because of special characters in a few columns. Some columns are breaking into a new row when it should stay in the column.

1. I've been doing it manually using the management studio export feature, but what's the best extract the 10 tables to CSV with the double quote qualifier using a script?
2. Will I have to escape commas and double quotes? 开发者_JS百科Best way to do this?
3. How should I handle newline codes in my columns, we need them for migration to a new system, but the PM wants to open the files and make modifications using Excel. Can they have it both ways?

I understand that much of the problem is that Excel is interpreting the file where a load utility into another database might not do anything special with new line, but what about double quotes and commas in the data, if I don't care about excel, must I escape that?

Many Thanks.

If you are using SQL Server 2005 or later, the export wizard will export the excel file out for you. Right click the database, select Tasks-> Export Data... Set the source to be the database. Set the destination to excel. At the end of the wizard, select the option to create an SSIS package. You can then create a job to execute the package on a schedule or on demand.

I'd suggest never using commas for your delimiter - they show up too frequently in other places. Use a tab, since a tab isn't too easy to include in Excel tables.

Make sure you never start a field with a space unless you want that space in the field.

Try changing your text lf's into the literal text \n. That is:

You might have:

0,1,"Line 1 Line 2", 3

I suggest you want:

0 1 "Line 1\nLine 2" 3

(assuming the spacing between lines are tabs)

Good luck

As far as I know, you cannot have new line in csv columns. If you know a column could have comma, double quotes or new line, then you can use this SQL statement to extract the value as valid csv SELECT '"' + REPLACE(REPLACE(REPLACE(CAST([yourColumnName] AS VARCHAR(MAX)), '"', '""'), char(13), ''), char(10), '') + '"' FROM yourTable.