import xls or csv into mysql db

mikawhat

First of all, which one is better for import format... xls or csv?

I tried both ways and i think csv is better... but there's an issue when exporting csv from the xls file... when there are numbers like 03, in the csv file the will be converted as 3 without the 0 in front...is there a way to fix this?

As about migrating data, is it possible to import a file into the mysql db with a php script or it has to be done from phpmyadmin?

Crowly

when there are numbers like 03, in the csv file the will be converted as 3 without the 0 in front...is there a way to fix this?

Format the cells as text.

As about migrating data, is it possible to import a file into the mysql db with a php script or it has to be done from phpmyadmin?

Yes, something like this (untested)

//open file read only
$file=fopen("test.csv","r");
while (!feof($file))
{
  $row=fgetcsv("test.csv",0); //explode can be used instead
  $sql=sprintf("INSERT INTO table (field1, field2, field3) VALUES ('%s','%s','%s')",$row[0], $row[1], $row[2]); //use %d if the values are numbers instead of a string/text
  mysql_query($sql)||die("Insert error: ".mysql_error());
}
//close file
fclose($file);

mikawhat

ok i'll try it... thank you!

mikawhat

i'm having a problem when saving the xls as csv... i have about 20 records but the csv file includes empty lines after those records... is there a way to configure it in order to exclude those empty records?

rulian

yes, count the number in the array, if it doesnt match your required value skip the insert

Crowly

You should be able to test against the empty records with field=='' or using the empty function, so putting the insert function in an if should avoid empty records being inserted into the DB.

Untested

//open file read only 
$file=fopen("test.csv","r"); 
while (!feof($file)) 
{ 
  $row=fgetcsv("test.csv",0); //explode can be used instead 
  if (!empty($row[0])) // checks only the first field, add OR !emptry($row[1]) to include the other fields
  {
    $sql=sprintf("INSERT INTO table (field1, field2, field3) VALUES ('%s','%s','%s')",$row[0], $row[1], $row[2]); //use %d if the values are numbers instead of a string/text 
    mysql_query($sql)||die("Insert error: ".mysql_error()); 
  }
} 
//close file 
fclose($file);

mikawhat

Yes i thought of that, but wouldn't that cause much delay for a file with thousands of records?

Crowly

Dont know. Another way can to be read everything into a "temporary" table, then do a new insert into the table you want to store the data, where you filter out the empty records

something like this

INSERT INTO table2 (field1,field2,field3,....,fieldN)
(SELECT field1,field2,field3,...,fieldN FROM table1 WHERE field1 IS NOT NULL)

mikawhat

ok... i think the first way is better...
thanks for your help.