Import csv file to database - values with comma

Question

I'm trying to import a csv file to my database. It's working but the problem is I have a column, namely "address1" and "address2" which may have commas. Example is:

194 Chavez compound, Something City

What's happening is194 Chavez compound is in the "address1" and Something City is in "address2". The whole address should be in "address1" only.

This is my code on importing in my database:

    do { 
        if ($data[0]) { 
            mysql_query("INSERT INTO awb (recNAME, company, address1, address2, city, province, postalCODE, contactNO, email, commodity, type, weight, length, width, height, decVAL, specINSTRUC, shipREF, payMETHOD, packNO, trackNO) VALUES 
                ( 
                    '".addslashes($data[0])."', 
                    '".addslashes($data[1])."',
                    '".addslashes($data[2])."', 
                    '".addslashes($data[3])."',
                    '".addslashes($data[4])."', 
                    '".addslashes($data[5])."',
                    '".addslashes($data[6])."', 
                    '".addslashes($data[7])."',
                    '".addslashes($data[8])."', 
                    '".addslashes($data[9])."',
                    '".addslashes($data[10])."', 
                    '".addslashes($data[11])."',
                    '".addslashes($data[12])."', 
                    '".addslashes($data[13])."',
                    '".addslashes($data[14])."', 
                    '".addslashes($data[15])."',
                    '".addslashes($data[16])."', 
                    '".addslashes($data[17])."',
                    '".addslashes($data[18])."',    
                    '".addslashes($data[19])."',                    
                    '".addslashes($data[20])."' 
                ) 
            "); 
        } 
    } while ($data = fgetcsv($handle,1000,",","'"));

In any event, if fields containing the field delimiter are not enclosed (e.g. within quote " characters) or such delimiters within fields are not escaped in some way (e.g. with backslash \ characters), how can any parser possibly know whether a given delimiter is part of a field or not? — eggyal
– eggyal, Commented Jan 2, 2014 at 9:08
If it's a proper csv file, then any value containing a comma should be enclosed in quotes.... you're saying a single quote in your fgetcsv() call; make sure it is a single quote and not a double quote — Mark Baker
– Mark Baker, Commented Jan 2, 2014 at 9:16
The "field delimiter" is the character or characters which separate the "fields" (or "columns" or "values") within your file: in your case, the "field delimiter" is comma ,. A parser is a program which reads a file and understands how the data within it should be divided up. — eggyal
– eggyal, Commented Jan 2, 2014 at 9:19
@nicole101: In this case, you should use mysql_real_escape_string() instead of addslashes(). However, I would recommend that you instead focus on getting LOAD DATA or mysqlimport to work for you. — eggyal
– eggyal, Commented Jan 2, 2014 at 9:57

Tim Burch · Accepted Answer · 2014-01-02 11:14:30Z

1

As one of the commentors suggested, try using LOAD DATA INFILE instead:

LOAD DATA INFILE '[file name]' INTO TABLE '[table name]'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'

If your CSV file has a header row, you'll also want to add this clause: IGNORE 1 LINES.

answered Jan 2, 2014 at 11:14

Tim Burch

1,0937 silver badges9 bronze badges

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

Collectives™ on Stack Overflow

Import csv file to database - values with comma

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related