loading .csv file with multiple commas in the field using MySQL

Question

I have a table named 'table1' with the columns:

CSMembers,BCID,Total,Email.

The excel sheet contains the data for this table in this format:

     CSMembers       BCID                 Total           Email
        abc    2,5,7,9,12,17,22,32       10,000      [email protected]
        xyz    1,3,5,7,9,12,17,20,22,33  12,500      [email protected]
        pqr    2,5,7,9,12,17,22,32       11,000      [email protected]
        ttt    2,5,7,9,12,17,22          9,800       [email protected]

the .csv file of this is :

CSMembers,BCID,Total,Email
abc,"2,5,7,9,12,17,22,32","10,000",[email protected]
xyz,"1,3,5,7,9,12,17,20,22,33","12,500",[email protected]
pqr,"2,5,7,9,12,17,22,32","11,000",[email protected]
ttt,"2,5,7,9,12,17,22","9,800",[email protected]

I have used the following code:

 load data local infile 'H:/abc.csv' into table table1
 fields terminated by ',' 
 optionally enclosed by '"'
 lines terminated by '\n' ignore 1 lines
 (CSMembers,BCID,Total,Email);

I am getting the following output:

CSMember    BCID    Total       Email
abc          2       10      [email protected]

xyz          1       12      [email protected]

pqr          2       11      [email protected]

ttt          2       9       [email protected]

But i need this output:

CSMembers   BCID                    Total         Email
    abc  2,5,7,9,12,17,22,32       10,000      [email protected]
    xyz  1,3,5,7,9,12,17,20,22,33  12,500      [email protected]
    pqr  2,5,7,9,12,17,22,32       11,000      [email protected]
    ttt  2,5,7,9,12,17,22          9,800       [email protected]

Can anyone please tell me what is wrong? if I should change the code or the csv file content or both? plese help.

vogomatix · Accepted Answer · 2013-11-21 08:03:16Z

1

Your schema for the table is possibly wrong bcid and total is probably defined as an int of some kind instead of a string. Numeric fields won't know whether a comma is a separator to another field or simply dividing the number up so it is easily readable. Also input to numeric fields typically accepts the value up to the first non-numeric character i.e. the comma

edited Nov 21, 2013 at 8:03

answered Nov 21, 2013 at 7:58

vogomatix

5,1332 gold badges27 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

vogomatix Over a year ago

For the total field, it should be possible to export a CSV file that does not put commas in numbers.

peterm Over a year ago

Another alternative for fixing Total column is to remove commas while loading data by using SET clause of LOAD DATA INFILE

Collectives™ on Stack Overflow

loading .csv file with multiple commas in the field using MySQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related