PHP CSV Import Problems

Question

So I'm trying to make it so that I can update a MySQL database by importing a CSV file, only problem is I am seeing some of my data has commas, which is causing the data to be imported into the wrong tables. Here's my existing import code.

if ($_FILES[csv][size] > 0) { 

  //get the csv file 
  $file = $_FILES[csv][tmp_name]; 
  $handle = fopen($file,"r"); 

  //loop through the csv file and insert into database 
  do { 
      if ($data[0]) { 
         mysql_query("INSERT INTO songdb (artist, title) VALUES 
             ( 
                 '".addslashes($data[0])."', 
                 '".addslashes($data[1])."' 
             ) 
         ") or die (mysql_error()); 
     } 
 } while ($data = fgetcsv($handle,1000,",","'")); 
 // 

 //redirect 
 header('Location: import.php?success=1'); die; 

}

Is there a way I can set it to ignore the commas, quotes and apostrophes in the CSV file?

I would also let to set it to ignore the first line in the csv, seeing as how it's just column information. If that is at all possible.

** EDIT ** For example if the CSV contains data such as "last name, first name", or "User's Data", these are literally just examples of the data that's actually in there. The data is imported each month and we've just noticed this issue.

Sample Data:

Column 1, Column 2
Item 1, Description
Item 2, Description
Item, 3, Description
Item, 4, Description
"Item 5", Description
"Item, 6", Description

Above is the sample data that was requested.

Can you please give example data that poses problem ? It would help us to help you. — Lorenz Meyer
– Lorenz Meyer, Commented Nov 17, 2013 at 20:21
For example if the CSV contains data such as "last name, first name", or "User's Data", these are literally just examples of the data that's actually in there. The data is imported each month and we've just noticed this issue. — PunkIsDaFunk
– PunkIsDaFunk, Commented Nov 17, 2013 at 20:24
No they vary, some are, some are not. I'm not sure why, it's a CSV provided by a distributor. — PunkIsDaFunk
– PunkIsDaFunk, Commented Nov 17, 2013 at 20:30
Can you edit your question and post some representative sample data from your data (a few rows) that cause problems? Instead of explaining just post the data. Obviously it should not be real. You can alter it if you wish. — peterm
– peterm, Commented Nov 17, 2013 at 20:39

Paulo Freitas · Accepted Answer · 2013-11-17 21:03:59Z

1

You might want to use MySQL's built-in LOAD DATA INFILE statement which not only will work faster, but will let you use the clause FIELDS OPTIONALLY ENCLOSED BY '"' to work with that kind of files.

So your query will be something like that:

mysql_query(<<<SQL
    LOAD DATA LOCAL INFILE '{$_FILES['csv']['tmp_name']}'
    INTO TABLE songdb
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    LINES TERMINATED BY '\\n'
    IGNORE LINES 1 (artist, title)
SQL
) or die(mysql_error());

edited Nov 17, 2013 at 21:03

answered Nov 17, 2013 at 20:46

Paulo Freitas

13.7k15 gold badges78 silver badges98 bronze badges

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

9 Comments

PunkIsDaFunk Over a year ago

i'm giving your code a try. Just wanted to post something, because the intial file is a 5GB csv file, and it's taking a few minutes to upload

Paulo Freitas Over a year ago

@BradJTrammell It should work thoroughly! Upload time will depend on network conditions, but you'll notice that the processing import time will be a lot better! :)

PunkIsDaFunk Over a year ago

Will this function actually import the values as well. i'm not as fluent in MySQL Statements as I am in PHP :-)

Paulo Freitas Over a year ago

@BradJTrammell If your CSV file is well formed, yes, it'll import all the records once. But per your question update seems like your CSV file violated any parsing methods, Item, 3, Description shouldn't be a valid record for 2 columns of CSV data. It's weird! :|

PunkIsDaFunk Over a year ago

so there is no way to avoid, or ignore a comma within a columns value?

|

111 · Accepted Answer · 2013-11-17 21:12:08Z

If your data is dirty, the easiest way to handle this will be to clean it up manually, and either use data entry forms that strip out bad characters and/or escape the input data, or tell the users who are generating this data to stop putting commas in fields.

Your example has inconsistent column count and inconsistent fields due to lack of escaping input in whatever they used to generate this data.

That said, you could do some advanced logic to igore any comma after Item but before a space or digit, using regular expressions, but that is getting kind of ridiculous and depending on the number of rows, it may be easier to clean it up manually before importing.

In terms of skipping the header row, you can do this:

if ($_FILES[csv][size] > 0) { 

  //get the csv file 
  $file = $_FILES[csv][tmp_name]; 
  $handle = fopen($file,"r"); 
  $firstRow = false;
  //loop through the csv file and insert into database 
  do { 
      if ($data[0]) { 

         // skip header row
         if($firstRow) { 
             $firstRow=false;
             continue;
         }

         mysql_query("INSERT INTO songdb (artist, title) VALUES 
             ( 
                 '".addslashes($data[0])."', 
                 '".addslashes($data[1])."' 
             ) 
         ") or die (mysql_error()); 
     } 
 } while ($data = fgetcsv($handle,1000,",","'")); 
 // 

 //redirect 
 header('Location: import.php?success=1'); die; 

}

Oh I just read your comment, 5gb. Wow. Manual cleanup is not an option. You need to look at the range of possible ways the data is screwed up and really assess what logic you need to use to capture the right columns.

Is your example above a representative sample or could other fields without enclosures have commas?

Class · Accepted Answer · 2013-12-31 07:43:09Z

0

Try this, this is working fine for me.

ini_set('auto_detect_line_endings',TRUE);

$csv_data=array();

$file_handle = fopen($_FILES['file_name']['tmp_name'], 'r');

while(($data = fgetcsv($file_handle) ) !== FALSE){

    $update_data= array('first'=>$data['0'],
                        'second'=>$data['1'],
                        'third'=>$data['2'],
                        'fourth'=>$data['34']);
    // save this array in your database
}

edited Dec 31, 2013 at 7:43

Class

3,1503 gold badges25 silver badges31 bronze badges

answered Dec 31, 2013 at 7:25

soni pushkar

212 bronze badges

1 Comment

nbrooks Over a year ago

Welcome to Stack Overflow! It is often useful to provide some explanation for what's happening in code samples, rather than simply posting code for the asker to try and understand.

Collectives™ on Stack Overflow

PHP CSV Import Problems

3 Answers 3

9 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

9 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related