Comma delimited import issues in SQL Server

Question

I am having a problem with SQL Server. I have a table with 42 columns to which there is frequent monthly imports. These imports are from a flat file using the import and export wizard. My problem is that my location column has information in the format:

• Sydney, Australia
• London, England
• Paris, France
• New York City, USA

What is the best way for me to display the data the same way or similiarly without the location column being split in two. For example: could i put quotes around everything? I know you can just check, but is there sometype of control that i could implelement that would prompt an error if somethig like this happens?

Thank you for your time!

Answer 1

Per rfc4180, you must use double quotes to enclose the field.

Fields containing line breaks (CRLF), double quotes, and commas should be enclosed in double-quotes. For example:

   "aaa","b CRLF
   bb","ccc" CRLF
   zzz,yyy,xxx
   "New York City, USA","next field value","Field, With, Lots, Of, Commas"