2

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? 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.

3Dave
  • 28,657
  • 18
  • 88
  • 151
Hell.Bent
  • 1,667
  • 9
  • 38
  • 73

3 Answers3

1

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

3Dave
  • 28,657
  • 18
  • 88
  • 151
btx
  • 266
  • 1
  • 4
  • Oh and just a note, you need to escape your double quotes with another double quote, so if you had the string: I say "hello" there. you would want to escape to I say ""hello"" there. – btx Dec 16 '10 at 23:03
1

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.

brian
  • 3,635
  • 15
  • 17
  • 1
    I saw that, but the file will be used primarilly for a import into some unknown solution so we needed CSV. but now they want to muck with it in Excel. Any way to automate creation of the files in csv with the options I have for text qualifier and delimiter. Thanks. – Hell.Bent Dec 17 '10 at 19:52
0

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.

Jeba Ranganathan
  • 532
  • 1
  • 8
  • 5