This is simple php script to import csv data to mysql, script below will create sql statements and will print on browser from given csv file. By default script ignores first row but if you dont want to ignore first row
then change config option to 0.
then change config option to 0.
|
1 |
$ignoreFirstRow = 1; |
Script is not able to create table structure from given CSVÂ because it will be really difficult to assign datatype for column, so You need to manually create table structure.
complete source code is as below,
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
<?php $table = "tableName"; $fileName = "States.csv"; $ignoreFirstRow = 1; if (($handle = fopen($fileName, "r")) !== FALSE) { while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) { if($ignoreFirstRow != 1){ $sql = "insert into ".$table." values("; $sql .= '"'.implode('","',$data).'"'; echo "".$sql.');'; $sql = ""; } $ignoreFirstRow++; } fclose($handle); } ?> |
Hi Bro,
your script is really very good, but i’ve found a bug in the sql insert statement, it’s come in this way:
insert into ventes_finales values(“xxxxxx, “39.00”,”2/17/2012 12:36:04 PM”,”promo321″,”PUBLICATION”,”ope36″,””,”59112″,””,”0″,”xxxxx@xxxx.fr”,”06000000″” );error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘39.00″,”2/17/2012 12:36:04 PM”,”promo321″,”PUBLICATION_321AUTO_MAGIQUE”,”ope36″,’ at line 1
Seems like there is one quote missing in first argument, Are you sure you didnt make any changes to the script ?
I don’t think he did because I got the same error. :/
Laysa, I just again checked my code and its working fine for me. If possible, Can u please send me ur csv file so u can check if there is any issue in file ??
I’m just trying to put this in my database.
This is a single row in my sample data:
Rose,19961008,29.00000,29.33333,25.66667,26.33333,4376700,0
Thank you for your quick response.
Here’s a part of the code I’m working on:
if (($handle = fopen($fileName, “r”)) !== FALSE) {
while (($data = fgetcsv($handle, 1000, “,”)) !== FALSE) {
if($ignoreFirstRow != 1){
$sql = “INSERT INTO “.$table.” (com_sym, date, open_price, high_price, low_price, close_price, vol, val_change) VALUES(“;
$sql .= ‘\”.implode(‘\’,\”,$data).’\”;
echo “”.$sql.’);’;
// Execute query
mysql_query($sql) or die(mysql_error());;
}
$ignoreFirstRow++;
}
fclose($handle);
}
I tried running your code and can see query has no errors. This is what i get with ur data and code
“INSERT INTO tableName (com_sym, date, open_price, high_price, low_price, close_price, vol, val_change) VALUES(‘Rose’,’19961008′,’29’,’29.33333′,’25.66667′,’26.33333′,’4376700′,’0′);” What error you are getting ?? So i can help u better.
I got this error:
“You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ” at line 1 ”
SQL cannot execute the query. Could there be any missing character in the query? Esp. in this part:
$sql = “insert into “.$table.” values(“;
$sql .= ‘”‘.implode(‘”,”‘,$data).'”‘;
echo “”.$sql.’);’;
$sql = “”; <—————————— ?
That part erases the query. Are you trying to add -" (double quote) at the end? I can't make it work.
Thank you so much!!!
Yes that line is to erase the query, m not trying to add quates there.
Go to phpmyadmin (dont run using code) and run this query “INSERT INTO tableName (com_sym, date, open_price, high_price, low_price, close_price, vol, val_change) VALUES(‘Rose’,’19961008′,’29’,’29.33333′,’25.66667′,’26.33333′,’4376700′,’0′);” and see if it runs. To generate this query i have used ur code as well data. And dont forget ti change the “tableNaame” to ur real tablename.
Yes, that’s true, I tried to do that and it inserts a row but the error still appears. However, I edited the code some more and finally made it to work. Here it is:
$sql = “insert into “.$table.”(com_sym, date, open_price, high_price, low_price, close_price, vol, val_change) values(“;
$sql .= ‘”‘.implode(‘”,”‘,$data).'”)’;
echo $sql;
Again, thank you so much for your time. 🙂
Lysa, Glad to know it worked. u r welcome.
Hi….
I am trying to perform data validation with my csv data and sql table.. I have 10 columns in sql table. i defined those attributes. If i m adding a new csv data that contains some other fields which i doesn’t specified in sql table, then there ll b a mismatch in sql.. How to do this data validation??
for example, I have defined 4 columns (no, name, job, phone no) in my sql table… i have a csv file that contains, no, city, job, state). then there ll b a mismatch. I need to do this. It should b done dynamically.. Is this possible using php and phpmyadmin??? Can u help me wit this?
Thanks in advance..