Using bulk insert to import .csv file

Question

I have a csv format file, which I want to import to sql server 2008 using bulk insert. I have 80 columns in csv file which has comma for example, column state has NY,NJ,AZ,TX,AR,VA,MA like this for few millions of rows. So I enclosed the state column in double quotes using custom format in excel, so that this column will be treated as single column and does not split at comma in between the column. But still the import is not successful; still it is splitting at comma. Can anyone please suggest successful import of the columns containing comma using bulk insert I am using this code bulk insert test from 'C:\test.csv' with ( fieldterminator=',', rowterminator='\n' ) go I saw similar question previously asked here, but I don't know visual basic to apply the code. Is there any other option to modify file in excel?

score 3 · Accepted Answer · answered Mar 14 '12 at 18:43

Is there any other option to modify file in excel?

It turns out there is, at least in Windows.

Go to Start Menu > Control Panel > Regional and Language Options.
In the Regional Options tab, click the Customize Button.
In the List Separator field, replace the , with a |. Click OK.

Saving a file as a .CSV through Excel will now create a pipe-separated value file. Be sure to undo this change to the Regional Options setting, as Excel uses the list separator for other things like functions.

Then you can do as datagod suggests and bulk upload the file using | as the column delimiter.

Thanks for your help..i appreciate it. It imported data successfully!! — alex, Mar 15 '12 at 00:20

score 2 · Answer 2 · answered Mar 14 '12 at 18:18

You should create a format file: http://msdn.microsoft.com/en-us/library/ms191516.aspx

If your data contains commas, I would choose a different delimiter. You can specify "|" as the delimiteter in the format file.

Example:

10.0
4
1       SQLCHAR       0       100     "|"     1     Col1  SQL_Latin1_General_CP1_CI_AS
2       SQLCHAR       0       100     "|"     2     Col2  SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR       0       100     "|"     3     Col3  SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR       0       7000    "\r\n"  4     Col11 SQL_Latin1_General_CP1_CI_AS

Using bulk insert to import .csv file

2 Answers2