Import a csv file to mysql..

[rapidz]

Hi There, im not a novice, but i'm not a php/sql expert either.

Basically, I've managed to convert an html table to a csv file.

I need to convert this csv file to sql, or better yet an sql statement that I can just enter through phpmyadmin.

Can anyone help, or create a script that would generate the csv text to a sql query?


The CSV looks like this...


LRS01923,AAKERMANS,EC 300,9T 4.0KW M10 Y,
LRS01812,AAKERMANS,EC 300,9T 5.4KW M10 Y 2P,
LRS00861,ABG,Jumbo 140 Roller,11T 5.5KW M10 Y,75-83
LRS02151,ABG,Jumbo 140 Roller,11T 5.4KW M10 Y,83-90
LRS01042,ALBIN,AD-21 Marine Engine,9T 1.0KW M8 Y,69-77
LRS01999,ALFA ROMEO,Alfa 145 1.4,9T 0.7-0.8KW M8 Y,94-96
LRS02071,ALFA ROMEO,Alfa 145 1.4 16V TS,9T 1.1KW GR M8 Y,97-00

Id like them to go in these fields, respectively;
part_no,Make,Model,Motor,year

if anyone can help me out, I'd be extremely grateful.

Many thanks.

Rapidz

[chazzy]

You want to use SQL to parse the CSV?

[TyeM]

You are going to have to process the CSV using something like the fgetcsv function and then execute an SQL INSERT for every line you are processing.

This is coming from the closest code I had at hand so might not be suitable without modification as it uses tabs instead of commas.


$fs = $file; //CSV file
while ( ($data = fgetcsv($fs, 10000, "\t") ) !== FALSE ){
//perform string escapes so data is entered into database correctly
$number_of_fields = count($data);

for ($c=0; $c < $number_of_fields; $c++){
$data_array[$c] = mysql_real_escape_string ($data[$c]);
}

$query = "INSERT INTO parts_table
VALUES(
'',
'$data_array[0]',
'$data_array[1]',
'$data_array[2]',
'$data_array[3]'
);";

$result = mysql_query($query);
}

[Technoroj]

$fs = $file; //CSV file
while ( ($data = fgetcsv($fs, 10000, "\t") ) !== FALSE ){
//perform string escapes so data is entered into database correctly
$number_of_fields = count($data);

for ($c=0; $c < $number_of_fields; $c++){
$data_array[$c] = mysql_real_escape_string ($data[$c]);
}

$query = "INSERT INTO parts_table
VALUES(
'',
'$data_array[0]',
'$data_array[1]',
'$data_array[2]',
'$data_array[3]'
);";

$result = mysql_query($query);
}