CODEWITHSUNDEEP
c#sql
Baub
Baub

Reputation: 751

using SQLBulkCopy with CSV and quoted identifier

I have some code that reads in a CSV file, loads that information to a data table, and then uses SQLBulkCopy to send it to SQL server. This is working fine except when my data has a column with a quoted identifier.

Here is the record that is causing the problem:

17,1,1,2,B219483,1,Martinez,Don,,Female,1965-01-30 00:00:00.000,915 Main St,Apt 7,Anywhere,CO,8000-1111,0,717-241-7766,1,,2,1,,0,,Reason for opting out per Health Co. - "214453, dr c johnson is not a part of the ACC"

,NULL,ENG,NULL,NULL,2011-07-18 15:50:48.000,1,2011-08-26 10:26:35.077,7

Notice there is a column of data that has a comma in the data so the entire column has double quotes. The problem is that my program will fail when I get to that line of data with this message:
System.ArgumentException: Input array is longer than the number of columns in this table.

What options do I have to get around this problem?

Upvotes: 0

Views: 231

Answers (1)

Barry
Barry

Reputation: 99

17,1,1,2,B219483,1,Martinez,Don,,Female,1965-01-30 00:00:00.000,915 Main St,Apt 7,Anywhere,CO,8000-1111,0,717-241-7766,1,,2,1,,0,,Reason for opting out per Health Co. - "214453, dr c johnson is not a part of the ACC",NULL,ENG,NULL,NULL,2011-07-18 15:50:48.000,1,2011-08-26 10:26:35.077,7

From looking at your data, it seems the double quotes are only around part of the data for that column. The double quotes should begin before Reason rather than 214453.

Upvotes: 1

Related Questions