1

I have a table that I am exporting via an SQL server table into a CSV file, via the flat file connection manager.

What I have done is in the Flat File Connection Manager I have used " (double-quote) as the Text Qualifier, which keeps the original data in the same column.

However for a couple of cells I run into the problem where the data originally had double-quotation marks and commas within them.

For instance,

Shaquille "Shaq" O'Neal, LLC

I don't want to change the data within the database. I also don't want to do it the quick and dirty way, ie. "save as xlsx through a query and then save as CSV".

Thanks

dave
  • 11,641
  • 5
  • 47
  • 65
ipandabear
  • 11
  • 1
  • 4
  • You should be able to do this by escaping the `"`, i.e. `\"`. If there's a comma in the field, the text needs to be in quotes. Your example would be `"Shaquille \"Shaq\" O'Neal, LLC"` – Robbert Apr 23 '15 at 21:15
  • possible duplicate of [Properly escape a double quote in CSV](http://stackoverflow.com/questions/17808511/properly-escape-a-double-quote-in-csv) – Robbert Apr 23 '15 at 21:18
  • Use tabs as your column delimiter and ponder whether you actually need a text delimiter. – Gordon Linoff Apr 24 '15 at 02:38
  • Why do you use double-quote for the text qualifier? Is it important? – dyatchenko Apr 24 '15 at 04:36

2 Answers2

0

Because of your data, you will have to use something else besides " or ' as your column delimiters and field separators. CSV does not mean Comma Delimited Values...it means Character Delimited Values...tho many get it mixed up. Be creative, use slashes, # sign, $ sign, ^, &....just be sure the receiving system can match what you are using when you export to CSV.

Juan-Carlos
  • 377
  • 2
  • 8
0

I would use a query in your SSIS package OLE DB Source or ODBC source something like this: 

select 
    id,
    '"' + name + '"',
    '"' + address + '"',
    city,
    zipcode
    FROM yourtable

Hope this helps!

FutbolFan
  • 13,235
  • 3
  • 23
  • 35