Import/Modify CSV

PHP
1

Howdy, I have a bit of a question for you all. I’m wanting to import a .csv file into a MySQL database, but I’m not sure how I’d go about formatting it to match the table structure.

A few sample lines from the .CSV (almost 5k lines in total):

LABNAME,COMPUTERNAME,USERNAME,TIME,DATE
"firstfloor", D37A5GG1, hatleyad, 13:40:53.98, 07/09/2010 
"lab118A", LKLCKB8, curries, 13:42:22.15, 07/09/2010 
"firstfloor", D96A5GG1, johnso62, 13:44:06.95, 07/09/2010 
"firstfloor", D96A5GG1, biggers, 14:04:43.83, 07/09/2010 
"secondfloor", D5I92VFI, lockhabe, 14:06:16.85, 07/09/2010 
"firstfloor", DDM0VLJ1, james11, 14:24:51.42, 07/09/2010 
"firstfloor", D67A5GG1, holdridg, 14:35:50.57, 07/09/2010 
"secondfloor", DHI92VF1, martinn, 14:38:34.41, 07/09/2010 
"firstfloor", DB7A5GG1, girtyl, 14:42:00.95, 07/09/2010 
"firstfloor", D4M0VLJ1, xiong03, 14:51:07.98, 07/09/2010 
"lab118A", LKLOAG1, winlock, 14:57:13.70, 07/09/2010

My two issues:

  1. I need to strip the LABNAME column of quotes
  2. I need to format the date and time columns to match the standard syntax for MySQL (I’m just using the data types DATE and TIME for each column at the moment).

Is there a simple way to do this, or am I going to have to format the .CSV before importing? If I do have to format it beforehand, what would be the quickest way to do so? This will just be a one-time conversion, just getting everything online. :slight_smile:

2

the simplest way is to load the csv into a “landing” table in mysql, using a single LOAD DATA INFILE command

the landing table would be designed to receive the data as strings, so you would have 5 VARCHARs

then you would use a simple mysql INSERT statement (with the SELECT FROM option) to reformat the date and time into the final target table

3

hi man,

here is your solution:

<?php
$d = file('data.txt', FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
$data = array();
for($i = 0; $i < count($d); $i++)
{
    $data[$i] = explode(',', $d[$i]);
    if($i > 0)
    {
        $data[$i][0] = str_replace('"', '', $data[$i][0]);
        $data[$i][3] = substr($data[$i][3], 0, 9);
        $data[$i][4] = preg_replace('/([0-9]{2,2})\\/([0-9]{2,2})\\/([0-9]{4,4})/', '$3-$2-$1', $data[$i][4]);
    }
}
?>

result is array with the formatted data.

4

Is there a simple way to do this, or am I going to have to format the .CSV before importing?
You will have to format before importing.

If I do have to format it beforehand, what would be the quickest way to do so?
See this:http://www.php.net/manual/en/function.fgetcsv.php.

The most efficient way would be to go through the csv file line by line, apply the formatting you need, then insert into the database (use a transaction).

Hope this helps.

5

Wow that was weird…I had been trying to access the array through a little snippet of code like this:

for($i = 0; $i < $count; $i++) {
for ($loop = 0; $loop < 5; $loop++) {
echo “$data[$i][$loop],”;
}
echo “<br />”;
}

That didn’t work, but if I took out the quotation marks (and the comma) it started working…don’t have a clue why but it works. :stuck_out_tongue: Thanks everyone for your help!

6

Doing a var_dump, this seems to be working great! Now I just have to figure out how to access the data. For some reason, doing an echo of $data[$i][$num] where $num = 0-4 simply results in the output of this:

Array[0],Array[1],Array[2],Array[3],Array[4],Array[0],Array[1],Array[2],Array[3],Array[4]

To be quite honest, outside of interacting with MySQL databases I haven’t had much experience with arrays, hopefully it’s something easy to fix. :stuck_out_tongue: Here’s a portion of the var dump if anyone wants to take a look:

array(12) {
    [0]=>  array(5) {
        [0]=>  string(7) "LABNAME" 
		[1]=>  string(12) "COMPUTERNAME" 
		[2]=>  string(8) "USERNAME" 
		[3]=>  string(4) "TIME" 
		[4]=>  string(4) "DATE"
    }
    
    [1]=>  array(5) {
        [0]=>  string(10) "firstfloor" 
		[1]=>  string(9) " D37A5GG1" 
		[2]=>  string(9) " hatleyad" 
		[3]=>  string(9) " 13:40:53" 
		[4]=>  string(12) " 2010-09-07"
    }
}