Commas within CSV Data

I have a CSV file which I am directly importing to a SQL server table. In the CSV file each column is separated by a comma. But my problem is that I have a column "address", and the data in this column contains commas. So what is happening is that some of the data of the address column is going to the other columns will import开发者_高级运维ing to SQL server.

What should I do?

For this problem the solution is very simple. first select => flat file source => browse your file => then go to the "Text qualifier" by default its none write here double quote like (") and follow the instruction of wizard.

Steps are - first select => flat file source => browse your file => Text qualifier (write only ") and follow the instruction of wizard.

Good Luck

If there is a comma in a column then that column should be surrounded by a single quote or double quote. Then if inside that column there is a single or double quote it should have an escape charter before it, usually a \

Example format of CSV

ID - address - name
1, "Some Address, Some Street, 10452", 'David O\'Brian'

New version supports the CSV format fully, including mixed use of " and , .

BULK INSERT Sales.Orders
FROM '\\SystemX\DiskZ\Sales\data\orders.csv'
WITH ( FORMAT='CSV');

I'd suggest to either use another format than CSV or try using other characters as field separator and/or text delimiter. Try looking for a character that isn't used in your data, e.g. |, #, ^ or @. The format of a single row would become

|foo|,|bar|,|baz, qux|

A well behave parser must not interpret 'baz' and 'qux' as two columns.

Alternatively, you could write your own import voodoo that fixes any problems. For the later, you might find this Groovy skeleton useful (not sure what languages you're fluent in though)

Most systems, including Excel, will allow for the column data to be enclosed in single quotes...

col1,col2,col3 'test1','my test2, with comma',test3

Another alternative is to use the Macintosh version of CSV, which uses TAB's as delimiters.

The best, quickest and easiest way to resolve the comma in data issue is to use Excel to save a comma separated file after having set Windows' list separator setting to something other than a comma (such as a pipe). This will then generate a pipe (or whatever) separated file for you that you can then import. This is described here.

I don't think adding quote could help.The best way I suggest is replacing the comma in the content with other marks like space or something.

replace(COLUMN,',',' ') as COLUMN

Appending a speech mark into the select column on both side works. You must also cast the column as a NVARCVHAR(MAX) to turn this into a string if the column is a TEXT.

SQLCMD -S DB-SERVER -E -Q "set nocount on; set ansi_warnings off; SELECT '""' + cast ([Column1] as nvarchar(max)) + '""' As TextHere, [Column2] As NormalColumn FROM [Database].[dbo].[Table]" /o output.tmp /s "," -W