CODEWITHSUNDEEP
javacsvload-data-infile
Manas
Manas

Reputation: 86

Handling word with comma in between in CSV file for input to LOAD DATA LOCAL INFILE

I am working on a java application which inserts data from a CSV file to mysql database table using below query

LOAD DATA LOCAL INFILE 'test.csv' INTO TABLE Test FIELDS TERMINATED BY ','    LINES TERMINATED BY ', ' IGNORE 1 LINES (id, name, address)

I have a issue when there is a comma between words for the a field like for address = pune, india. It is pushing the word after comma to next column.

I have found some workaround, such as escaping comma in Java code like 

strAddress = strAddressWithComma.replaceAll(",", "\\"+"\\,");

This works but after data is inserted in DB table it looks like “pune, india” (double quotes around the original string)

Another workaround is adding ENCLOSED BY ‘\”’ clause in LOAD DATA LOCAL INFILE and using below java code.

strAddress ="\"" + strAddressWithComma+ "\"";

But with this workaround also we have string with double quotes around the original string in DB when we use LOAD DATA LOCAL INFILE.

I do not need double quotes around original value in DB after insertion with Load Data statement . DB entry should be same as that of data in original csv file.

I know we may have a DB trigger which can strip double quotes from the new string but we want to handle this is application only.

Any solution or suggestion will be appreciated

Upvotes: 1

Views: 88

Answers (0)

Related Questions