CODEWITHSUNDEEP
t-sqlsql-server-2012
sukesh
sukesh

Reputation: 2523

Bulk insert csv - column value itself has commas

The Activity column has values that have commas in the text

Some records in the csv :

Name,Project,Activity,Hrs
John,,,7.1
,Junkie,,7.1
,,Reviewing the,file,7.1 //This is under 'Activity' column and it has a comma in the text

When I use the bulk insert, i get 'bulk load data conversion for this line. If this line is removed or the comma in that sentence is removed, it works all good.

Please let me know what are the options in this case. I have many csv files, and each might have many such values.

Upvotes: 0

Views: 1173

Answers (2)

CygnusX1
CygnusX1

Reputation: 1

I had a similar issue where a text string had a comma in the string. I used the following field terminator to resolve.

FIELDTERMINATOR = '\t'

This does not work on CSV and I had to save my files as .txt.

Upvotes: -1

abiessu
abiessu

Reputation: 1927

If I had this particular issue and the creation of the CSV files was not under my control, I would resort to a Perl script like this:

open(my $fhin, "<", "MyFile.csv");
open(my $fhout, ">", "MyQFile.csv");

while (my $line = <$fh>) {
  chomp($line);
  $line =~ s/^([^,]*),([^,]*),(.*),([^,]*)$/\"$1\",\"$2\",\"$3\",\"$4\"/;

  print $fhout $line . "\n";
}

Note that the above regular expression can handle only one "problem" column of this kind. If there are any others, there is no possibility of programmatically assigning correct quotation to such columns (without more information...).

Upvotes: 3

Related Questions