I have a file in csv which the format is like following:
"**Table1"
"*Field1","*Field2","*Field3","*Field4"
"Data1","Data2", "Data3", "Data4"
"**Table2"
"*Field1","*Field2","*Field3","*Field4"
"Data1","Data2", "Data3", "Data4"
I want the program can recognise the table 1/ 2 and upload the data to the mysql predefined table. I plan to use PHP. Has anyone tried to do this? I looking for an example and method! Thanks.
You can try something like this:
$handle = fopen("./myfile.csv", "r");
if ($handle === FALSE) {
exit();
}
// ignore header
fgetcsv($handle, 1000, ",");
// generate SQL query
$sql = "INSERT INTO `mytable` (col1, col2, ...) VALUES ";
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
$sql .= '("'.$data[0].'", "'.$data[1].'", "'.$data[2].'", ...),';
}
$sql = substr($sql,0,strlen($sql)-1).';';
// then execute your query
EDITED
Table1 (Field1, Field2, Field3, Field4, Field5, Field6, Field7, Field8) VALUES "; while (($data = fgetcsv($handle, 1000, "\t")) !== FALSE) { $sql .= '("'.$data[0].'", "'.$data[1].'", "'.$data[2].'", "'.$data[3].'", "'.$data[4].'", "'.$data[5].'", "'.$data[6].'", "'.$data[7].'"),'; <----- line 26 } $sql = substr($sql,0,strlen($sql)-1).';'; // then execute your query $rs = mysql_query($sql); mysql_close();
"\t" with "," in the while statement, it should work now (code edited)This library will probably make things a lot easier for you: https://code.google.com/p/php-csv-parser/. I have used it in a recent project where I need to parse CSVs and insert into a MySQL database.
You can choose which table to insert into by using $csv->getCell($x, $y) where $x and $y point the cell with "Table1" or "Table2" inside.
So you could do:
$table_name = $csv->getCell($x, $y);
if( $table_name === "Table1" ) {
// code to insert into table 1
} else if( $table_name === "Table2" ) {
// code to insert into table 2
}
