Ideaticus.com

Jun 05 2008

Importing CSV files with PHP

Published by Narcis Radu at 7:48 am under Web Development

Attention: this is a technical article.

Sometimes site owners, with almost zero technical knowledge, want a way to save their database then to import them directly from their web application. For this I usualy save every table as CSV file and for import, I parse every csv file and import in the corespondent table.

Saving a CSV is pretty simple:

 
@mkdir('/tmp/folder');
$out = fopen('/tmp/folder/tableName.csv', 'w');
 
$stmt = $dbh->query('describe tabelName');
$fields = array();
while(($data =  $stmt->fetch(PDO::FETCH_NUM))!= false)
{
    $fields[] = $data[0];
}
fputcsv($fields);
 
$stmt = $dbh->query('select * from tabelName');
while(($data = $stmt->fetch(PDO::FETCH_NUM) )!= false)
{
    fputcsv($out, $data);
}
 
fclose($out);

If you want to automatically import some CSV files and have some control over the errors, a very simple way to do it is:

$handle = fopen('tableName.csv', 'r');
$fields = fgetcsv($handle);
 
$detail = array();
while(($data = fgetcsv($handle))!== false)
{
    $detail[] = $data;
}
 
$x = 0;
$stock = array();
foreach($detail as $i)
{
    $y = 0;
    foreach($fields as $z)
    {
        $stock[$x][$z] = $i[$y++];
    }
    $x++;
}
 
foreach ($stock as $record)
{
    try
    {
        $dbh->query('
            INSERT INTO
                '.$table.'('.implode(',',array_keys($record)).')
            VALUES
                ("'.implode('","',$record).'")
        ');
    }
    catch (Exception $db_err)
    {
         echo $db_err->getMessage().'<br />\n';
    }
}

I use this to make offer some simple backup capabilities for clients and it works pretty well. If you have better ideas please let me know.

4 Responses to “Importing CSV files with PHP”

# Sorinon 08 Aug 2008 at 3:47 pm

saved as xml works pretty well :D… never got an error.
generates larger files but is more.. user friendly
# Narcis Raduon 10 Aug 2008 at 5:08 am

One of the reasons that I don’t want to use XML is the file size. Working with a large database (imagine 1Gb of data), CSV is the best option so far.
# Sorinon 11 Aug 2008 at 2:45 am

Point taken:P
I had a table of around 80000 records that generated a 12MB csv file but it was losing some columns of data and I had to save it to a xml file that was around 50MB.. but I got my data intact.
Well is a good last alternative
# Narcis Raduon 11 Aug 2008 at 2:04 pm

Sometimes the best solution is the working one. I’ll never say: “This is bad! Use this!”. But always I’m searching for the best solution.
Alternatives are good!