Import from csv to db
Moderator: General Moderators
Import from csv to db
Hi am a CS student from Greece and i need some help, if you pleased. In my application(not for commercial use), the tables and their fields will be created dynamically. I used csv's headers for that purpose an i added another field "id". After the creation of the table i need to import the data to table. I use the following script:
$k=0;
$j=1;
//Insert Data to table
foreach ($csv->data as $key => $row){
$k=0;
foreach ($row as $value) {
echo "
$value
";
$sql= "INSERT INTO inouts ('id','$cols[$k]') VALUES ('$j','$value')";
// Execute query
$result=mysql_query($sql,$link);
if(mysql_error()){ echo mysql_error(); }
$k++;
}
$j++;
}
But, i get the following 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 ''id','DMUS') VALUES ( '1', 'Brighton' )' at line 1
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 ''id','INPUT1') VALUES ( '1', '4453' )' at line 1
etc..
Thank you in advance!
$k=0;
$j=1;
//Insert Data to table
foreach ($csv->data as $key => $row){
$k=0;
foreach ($row as $value) {
echo "
$value
";
$sql= "INSERT INTO inouts ('id','$cols[$k]') VALUES ('$j','$value')";
// Execute query
$result=mysql_query($sql,$link);
if(mysql_error()){ echo mysql_error(); }
$k++;
}
$j++;
}
But, i get the following 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 ''id','DMUS') VALUES ( '1', 'Brighton' )' at line 1
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 ''id','INPUT1') VALUES ( '1', '4453' )' at line 1
etc..
Thank you in advance!
Re: Import from csv to db
Please use [syntax=php]yourcode[/syntax] tags when posting code.
Normally the field names don't require quotes, are you using MySQL or something else?
Have you double checked to see that you've created the table properly for those fields? Can you post your create table statement too?
Normally the field names don't require quotes, are you using MySQL or something else?
Have you double checked to see that you've created the table properly for those fields? Can you post your create table statement too?
Re: Import from csv to db
Hi thanks for the reply, i use wamp server, i didn't mentioned before.
I did check the table and the fields, with some changes (that i cant remember now) into the 'insert into' query, the 'id' filed is written in the table once, that leads me to use mysql_real_escape_string() for the rest of the fields. Now trying the above script, i get the previous error and none of the fields are written, even the 'id'.
I use the follow script to create the table,in $cols i have the headers of th csv file
I did check the table and the fields, with some changes (that i cant remember now) into the 'insert into' query, the 'id' filed is written in the table once, that leads me to use mysql_real_escape_string() for the rest of the fields. Now trying the above script, i get the previous error and none of the fields are written, even the 'id'.
I use the follow script to create the table,in $cols i have the headers of th csv file
Code: Select all
// Create table
$sql= "CREATE TABLE inouts
(
id int not null AUTO_INCREMENT,
";
foreach ($cols as $col) {
$sql.=" $col varchar(255),"; }
$sql.="primary key (id) )";
// Execute query
$result=mysql_query($sql,$link);
Re: Import from csv to db
can you do an SQL print along with your mysql_error()?
It's sometimes easier to identify an SQL problem if you have the full query.
I chucked the mysql_error() into my text editor and i noticed something when it gets to
If you look, just before `id` there is a double '
Try updating your query to:
It is a habit thing for me, i never use ' or " when encasing field names or table names and have fixed a couple of SQL problems just by replacing ' " with `. I also always encase my variables using {} to make sure they cant escape the query.
EDIT!!:
Sorry typo in the sql
It's sometimes easier to identify an SQL problem if you have the full query.
I chucked the mysql_error() into my text editor and i noticed something when it gets to
Code: Select all
''id','DMUS') VALUES ( '1',Try updating your query to:
Code: Select all
"INSERT INTO `inouts` (`id`,`{$cols[$k]}`) VALUES ('{$j}','{$value}')"EDIT!!:
Sorry typo in the sql
Re: Import from csv to db
Thank you very much, i think that we are so close, i use your 'insert into' statement and i get a Duplicate entry '1' for key 1 error.
I leave blank the field value for the id
and i get Incorrect integer value: '' for column 'id' at row 1
In the case that i dont use id at all, at insert into statement, i get the data from cell 1x3 to 2x3 etc..
I leave blank the field value for the id
Code: Select all
$sql="INSERT INTO `inouts` (`id`,`{$cols[$k]}`) VALUES ('','{$value}')";In the case that i dont use id at all, at insert into statement, i get the data from cell 1x3 to 2x3 etc..
Re: Import from csv to db
The reason you get a duplicate entry is because your trying to insert 2 sets of data with the same ID number (which im assuming in your database is set to the PRIMARY KEY.
If all data is individual from each other, and you dont need to insert data with same same ID numbers into the table, then your SQL would look like:
And just ditch the `id` all together if it isnt required.
But if you DO have to store data with the same ID number multiple times, you will need to remove the ID as the PRIMARY KEY from that table.
If all data is individual from each other, and you dont need to insert data with same same ID numbers into the table, then your SQL would look like:
Code: Select all
"INSERT INTO `inouts` (`{$cols[$k]}`) VALUES ('{$value}')"But if you DO have to store data with the same ID number multiple times, you will need to remove the ID as the PRIMARY KEY from that table.
Re: Import from csv to db
I am sorry for asking all the time, I do not use id as primary key, but now i cant create the table, i get the following error:
Error creating table: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 2
I check some things tha you mentioned before, but nothing, coul you find the error?
Error creating table: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 2
I check some things tha you mentioned before, but nothing, coul you find the error?
Code: Select all
// Create table
$sql= "CREATE TABLE inouts
(`id` integer,";
foreach ($cols as $col) {
$sql.=" `{$col}` varchar(255) ,";}
$sql.=")";Re: Import from csv to db
Your problem lies in how your creating your SQL, a full print of your current setup reveals your problem.
The , at the end is causing your problem 
that should fix your problem.
I removed the comma after 'integer' and moved the comma after varchar(255) to before `{$col}`
Code: Select all
CREATE TABLE inouts (`id` integer, `col1` varchar(255) , `col2` varchar(255) , `col3` varchar(255) ,)Code: Select all
$sql= "CREATE TABLE inouts (`id` integer";
foreach ($cols as $col) {
$sql.=", `{$col}` varchar(255)";
}
$sql.=")";I removed the comma after 'integer' and moved the comma after varchar(255) to before `{$col}`
Re: Import from csv to db
Thank you very much for your help, i cant fill the table as i like, i think i do the job with update statement
because with insert into i cant manage the rows, i need to fill the table line by line as that is the format i got from csv. Consequently id is needed.
because with insert into i cant manage the rows, i need to fill the table line by line as that is the format i got from csv. Consequently id is needed.
Re: Import from csv to db
Without seeing what sort of data your getting from your CSV, im finding it difficult to understand how you need the data stored.
Would your data in your table would look something like:
NO PRIMARY KEY
The above SQL statement refers to the above table.
Or more like:
WITH PRIMARY KEY
The above SQL statement refers to the above table.
Im afraid thats all i can help you with for now... 2:30am and i need sleep ^^
Would your data in your table would look something like:
NO PRIMARY KEY
Code: Select all
id | col1 | col2
------------------------
1 | blah | 1
1 | 1 | etc
2 | doh | 2
2 | 2 | etc
3 | yahoo | 3
3 | 3 | etc
4 | meh | 4
4 | 4 | etc
Code: Select all
"INSERT INTO `inouts` (`id`,`{$cols[$k]}`) VALUES ('{$j}','{$value}')"Or more like:
WITH PRIMARY KEY
Code: Select all
id | col1 | col2
------------------------
1 | blah | 1
2 | doh | 2
3 | yahoo | 3
4 | meh | 4
Code: Select all
"INSERT INTO `inouts` (`{$cols[$k]}`) VALUES ('{$value}')"Im afraid thats all i can help you with for now... 2:30am and i need sleep ^^
Re: Import from csv to db
Code: Select all
1 Brighton NULL NULL NULL NULL NULL
2 NULL 4453 NULL NULL NULL NULL
3 NULL NULL 5456 NULL NULL NULL
4 NULL NULL NULL 52165 NULL NULL
5 NULL NULL NULL NULL 516 NULL
6 NULL NULL NULL NULL NULL 516
7 Manchester NULL NULL NULL NULL NULL
8 NULL 1 NULL NULL NULL NULL
9 NULL NULL 8 NULL NULL NULL
10 NULL NULL NULL 9 NULL NULL
11 NULL NULL NULL NULL 56 NULL
12 NULL NULL NULL NULL NULL 12
13 Leeds NULL NULL NULL NULL NULL
14 NULL 21 NULL NULL NULL NULL
15 NULL NULL 78 NULL NULL NULL
16 NULL NULL NULL 546 NULL NULL
17 NULL NULL NULL NULL 556 NULL
18 NULL NULL NULL NULL NULL 1
19 Leicester NULL NULL NULL NULL NULL
20 NULL 2 NULL NULL NULL NULL
21 NULL NULL 798 NULL NULL NULL
22 NULL NULL NULL 61 NULL NULL
23 NULL NULL NULL NULL 547 NULL
24 NULL NULL NULL NULL NULL 2
25 Cardiff NULL NULL NULL NULL NULLAs an example (id=2) Manchester 1 8 9 56 12
That is what i get using
Code: Select all
$sql="INSERT INTO `inouts` (`{$cols[$k]}`) VALUES ('{$value}')";[/php ]
WITH a Primary Key
I tried
[php] $sql="INSERT INTO `inouts` (`id`,`{$cols[$k]}`) VALUES ('{$j}','{$value}')";Re: Import from csv to db
Thats a large amount of data...
Your problem is in your insert script.
your inserting each column data your retrieving as a new row.
You need to modify your INSERT script to combine all the elements you need into possibly an array. then once you have ALL data you need, run the INSERT SQL.
Then clear your cache, and start for the next item.
Your problem is in your insert script.
your inserting each column data your retrieving as a new row.
You need to modify your INSERT script to combine all the elements you need into possibly an array. then once you have ALL data you need, run the INSERT SQL.
Then clear your cache, and start for the next item.
Re: Import from csv to db
Thank you very much, i undestand that in australia is very late, i am from greece, i ll try to fix this and i post the solution for future need. If i wont i would appreciate your help in the future, goodnight!
Re: Import from csv to db
This may not exactly work, but this is an example of what i mean.
Anyways im heading to bed!
Good luck!!
Code: Select all
foreach ($csv->data as $row){
$fields = "";
$values = "";
foreach($row as $value){
$fields .= ", '{cols[$k]}'";
$values .= ", '$value'";
}
$sql = "INSERT INTO inouts (`id`, ".$fields.") VALUES ('{$j}', ".$values.")";
mysql_query($sql,$conn);
$j++;
}
Good luck!!
Re: Import from csv to db
Many thanks!