CODEWITHSUNDEEP
phpcsvimport
PunkIsDaFunk
PunkIsDaFunk

Reputation: 75

PHP CSV Import Problems

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.

Upvotes: 1

Views: 1987

Answers (3)

soni pushkar
soni pushkar

Reputation: 21

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
}

Upvotes: 0

111
111

Reputation: 1918

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?

Upvotes: 0

Paulo Freitas
Paulo Freitas

Reputation: 13649

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());

Upvotes: 1

Related Questions