Import from csv to db

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

greg7
Forum Commoner
Posts: 32
Joined: Tue Oct 13, 2009 7:38 am

Import from csv to db

Post by greg7 »

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!
Eric!
DevNet Resident
Posts: 1146
Joined: Sun Jun 14, 2009 3:13 pm

Re: Import from csv to db

Post by Eric! »

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?
greg7
Forum Commoner
Posts: 32
Joined: Tue Oct 13, 2009 7:38 am

Re: Import from csv to db

Post by greg7 »

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

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);
 
User avatar
Weiry
Forum Contributor
Posts: 323
Joined: Wed Sep 09, 2009 5:55 am
Location: Australia

Re: Import from csv to db

Post by Weiry »

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

Code: Select all

''id','DMUS') VALUES ( '1',
If you look, just before `id` there is a double '

Try updating your query to:

Code: Select all

"INSERT INTO `inouts` (`id`,`{$cols[$k]}`) VALUES ('{$j}','{$value}')"
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
greg7
Forum Commoner
Posts: 32
Joined: Tue Oct 13, 2009 7:38 am

Re: Import from csv to db

Post by greg7 »

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

Code: Select all

$sql="INSERT INTO `inouts` (`id`,`{$cols[$k]}`) VALUES ('','{$value}')";
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..
User avatar
Weiry
Forum Contributor
Posts: 323
Joined: Wed Sep 09, 2009 5:55 am
Location: Australia

Re: Import from csv to db

Post by Weiry »

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:

Code: Select all

"INSERT INTO `inouts` (`{$cols[$k]}`) VALUES ('{$value}')"
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.
greg7
Forum Commoner
Posts: 32
Joined: Tue Oct 13, 2009 7:38 am

Re: Import from csv to db

Post by greg7 »

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?

Code: Select all

// Create table
$sql= "CREATE TABLE inouts
(`id` integer,";
      foreach ($cols as $col) {
         $sql.=" `{$col}` varchar(255) ,";} 
         $sql.=")";
User avatar
Weiry
Forum Contributor
Posts: 323
Joined: Wed Sep 09, 2009 5:55 am
Location: Australia

Re: Import from csv to db

Post by Weiry »

Your problem lies in how your creating your SQL, a full print of your current setup reveals your problem.

Code: Select all

CREATE TABLE inouts (`id` integer, `col1` varchar(255) , `col2` varchar(255) , `col3` varchar(255) ,)
The , at the end is causing your problem :)

Code: Select all

$sql= "CREATE TABLE inouts (`id` integer";
foreach ($cols as $col) {
    $sql.=", `{$col}` varchar(255)";
}
$sql.=")";
that should fix your problem.
I removed the comma after 'integer' and moved the comma after varchar(255) to before `{$col}`
greg7
Forum Commoner
Posts: 32
Joined: Tue Oct 13, 2009 7:38 am

Re: Import from csv to db

Post by greg7 »

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.
User avatar
Weiry
Forum Contributor
Posts: 323
Joined: Wed Sep 09, 2009 5:55 am
Location: Australia

Re: Import from csv to db

Post by Weiry »

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

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}')"
The above SQL statement refers to the above table.

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}')"
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 ^^
greg7
Forum Commoner
Posts: 32
Joined: Tue Oct 13, 2009 7:38 am

Re: Import from csv to db

Post by greg7 »

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    NULL
Sorry for the huge post, i have 13 cities that i must assign an id and fill 5 more fields in every row.
As 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}')";
without primary key and i got exactly the same as above.
User avatar
Weiry
Forum Contributor
Posts: 323
Joined: Wed Sep 09, 2009 5:55 am
Location: Australia

Re: Import from csv to db

Post by Weiry »

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.
greg7
Forum Commoner
Posts: 32
Joined: Tue Oct 13, 2009 7:38 am

Re: Import from csv to db

Post by greg7 »

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!
User avatar
Weiry
Forum Contributor
Posts: 323
Joined: Wed Sep 09, 2009 5:55 am
Location: Australia

Re: Import from csv to db

Post by Weiry »

This may not exactly work, but this is an example of what i mean.

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++;
}
 
Anyways im heading to bed!
Good luck!!
greg7
Forum Commoner
Posts: 32
Joined: Tue Oct 13, 2009 7:38 am

Re: Import from csv to db

Post by greg7 »

Many thanks!
Post Reply