Parse hard csv file

Question

I need to parse hard csv file and put it to MySQL.

1.csv:

"col1,""col21, col22, col23"",""col3"",""col41, col42""

My test php script file:

<?php
$connection = mysql_connect('127.0.0.1:3307','a','') or die ("Unable to connect!"); 
mysql_select_db('test') or die ("Unable to select database!");
mysql_query('SET NAMES utf8');
$fp = fopen("1.csv", "r") or die("Couldn't open filename");
while (($data = fgetcsv($fp, 0, ",", "'")) !== FALSE)
{
    $import="INSERT INTO iptable(col1,col2,col3,col4) values('$data[0]','$data[1]','$data[2]','$data[3]')";
    mysql_query($import) or die(mysql_error());
}
fclose($fp);
echo '<b>Successfully added to DB</b>';
?>

I need to INSERT INTO iptable VALUES('col1', 'col21, col22, col23', 'col3', 'col41, col42'). How i can to do that? (how to correctly separate them?).

My csv file is not correctly, but i need to parse data from it. To iptable(col1,col2,col3,col4). (4 columns)

For example, Excel is correctly convert this csv file to columns. (http://goo.gl/xm0bly)

That csv seems to be invalid, are you sure it's what you have? — Maerlyn
The double quotes in your csv file looks really strange? Also, if the file contains headers for each column you should skip the first row before your loop. — Cyclonecode
If you fix the CSV format, you should be able to use LOAD DATA LOCAL INFILE to load it into MySQL. — Barmar
I think that you need to do the following before adventuring in the CSV parsing world: 1. You need a better database schema: col21, col22, col23 smells of bad design from a mile, same goes for col41, col 42 2. you need to generate a better CSV, because this one is beyond messy 3. if you can't do (2) you must define a better separator, because the comma is ambiguous as the double quotes. — STT LCU

Fluffeh Fluffeh · Accepted Answer · 2014-06-03T12:54:22

This is a slight mix of a few functions (explode, str_replace) and some logic in the foreach statement, but given your input will put the data in as expected:

while (($data = fgetcsv($fp, 0, ",", "'")) !== FALSE)
{

    $array=explode('""', $data);
    $line=array();
    foreach($array as $val)
    {
        if(trim($val)!="," && trim($val))
        {
            $line[]=str_replace('"', '', $val);
        }
    }

    $import="INSERT INTO iptable(col1,col2,col3,col4) values('$line[0]','$line[1]','$line[2]','$line[3]')";
    mysql_query($import) or die(mysql_error());
}

Based on your input as shown in the question, the $line array is shown as:

Array
(
    [0] => col1,
    [1] => col21, col22, col23
    [2] => col3
    [3] => col41, col42
)

Edit: Based on your input string, the following code outputs the text below:

<?php
    $string='"col1,""col21, col22, col23"",""col3"",""col41, col42"" ';

    echo "String input:\r\n".$string."\r\n";

    $array=explode('""', $string);
    $line=array();
    foreach($array as $val)
    {
        if(trim($val)!="," && trim($val))
        {
            $line[]=str_replace('"', '', $val);
        }
    }

    echo '$line variable is currently:'."\r\n";
    print_r($line);

?>

Output:

String input:
"col1,""col21, col22, col23"",""col3"",""col41, col42"" 
$line variable is currently:
Array
(
    [0] => col1,
    [1] => col21, col22, col23
    [2] => col3
    [3] => col41, col42
)

Parse hard csv file

2 Answers