How to read the data in csv and upload to Mysql

Question

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.

MySQL actually supports loading data from csv directly, check : http://stackoverflow.com/a/11449458/145587 — OneOfOne, Aug 28 '13 at 08:29
@OneOfOne - though it won't support this structure of data in the csv file — Mark Baker, Aug 28 '13 at 08:32

yafrani · Answer 1 · 2013-08-29T10:32:08.667

1

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

edited Aug 29 '13 at 10:32

answered Aug 28 '13 at 08:24

yafrani

521
4
9

It seems it doesn't work. I copied some with the code as below: PHP Notice: Undefined offset: 3 in /home/ubuntu/parse_mysql on line 26 PHP Notice: Undefined offset: 4 in /home/ubuntu/parse_mysql on line 26 – wing suet cheung Aug 29 '13 at 07:50
$sql = "INSERT INTO `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(); – wing suet cheung Aug 29 '13 at 08:00
@wingsuetcheung yes, sorry my mistake, I forgot to replace `"\t"` with `","` in the `while` statement, it should work now (code edited) – yafrani Aug 29 '13 at 10:35

score 0 · Accepted Answer · answered Aug 28 '13 at 08:19

0

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
}

answered Aug 28 '13 at 08:19

ale

11,636
27
92
149

Thanks. Is that mean it able to search the table name e.g. Table1? – wing suet cheung Aug 28 '13 at 09:12
No worries. Yeh, you need to put all the above in a loop. Then you need to look at the cells with the table name inside. – ale Aug 28 '13 at 09:19

How to read the data in csv and upload to Mysql

2 Answers2