Sorting and Inserting a CSV

[Cristiano]

Now - I am completely new to this, bear in mind. Not to PHP - but to this. Well, an up and coming project will require me to download a pretty messy CSV file, sort it into a local format and upload and insert it into a database.

Could someone outline the pre-requisite knowledge I would require to go about something like this, any good resources/tutorials that delve into this sort of thing.

Thanks kindly.

Cristiano

[Gaheris]

I don't exactly see what your problem is? You get the file into an array with file, then go trough it (foreach) and explode every value into a second array with all values. Then you jungle with your new, multi dimensional array and generate your queries out of it.

/** 

  * Example data

  * a,b,c

  * x,y,z

  */

$d = ',';

$f = 'example.dat';

$a = file($f);



/**

  * Generate multi dimensional array

  * The example data would generate this array

  * array(0 => array('a', 'b', 'c'), 1 => array(x', 'y', 'z'));

  */

$r = array();

foreach ($a as $k => $v) {

    $t = explode($d, $v);

    $r[$k] = $t;

}



/** 

  * Insert data in database

  */

$r = array();

foreach ($a as $k => $v) {

    $t = explode($d, $v);

    $sql = 'INSERT INTO 

                my_table 

                (column1, column2, column3) 

            VALUES 

                (\''.$t[0].'\', \''.$t[1].'\', \''.$t[2].'\')';

    $res = mysql_query($sql) or die(mysql_error());

    echo 'Inserted row: '.implode(', ', $t).' into database <br />'";

} 

[Cristiano]

OK thanks - I hadn't had any experience with this before, and the explode function either. What does this do exactly?

[Gaheris]

I didn't place a link on it's name for no reason, check it out.

Returns an array of strings, each of which is a substring of string formed by splitting it on boundaries formed by the string separator.

[veverkap]

You say that you already have it in CSV format? Why not use fgetscv?

http://us4.php.net/manual/en/function.fgetcsv.php

The example there is pretty good.

<?php
$row = 1;
$handle = fopen ("test.csv","r");
while ($data = fgetcsv ($handle, 1000, ",")) {
    $num = count ($data);
    print "<p> $num fields in line $row: <br>\n";
    $row++;
    for ($c=0; $c < $num; $c++) {
        print $data[$c] . "<br>\n";
    }
}
fclose ($handle);
?>

[Gaheris]

I would only use fgetcsv if the CSV file is quite big, then only storing part of it in memory would be a lot better then having it all. But if you want speed and you've got enough memory and/or you will not run this a lot (or at peak times) then the file, explode way should work good enough.