Page 1 of 2

importing from csv file to mySQL Dbase (php code)

Posted: Tue Oct 25, 2011 4:45 pm
by grabber_grabbs
I have a little problem while trying load my sql file with a csv one (import). The problem i have is that the field id (primery key) get this value 4294967295 I dont understand where this value come from... In my csv file, i have this info.

13243500,produit test,100,ceci est une longue description,12.95,ch,12
14567765,produit test 2,200,ceci est une longue description no 2,13.5,ch ,23
12312300,produit test 3,200,etgertwertwertwertwertewrr,14.57,ch,23


I have 99 records in the SQL file... so the ID of the first record imported should be 100 right ? The ID field is set with auto_increment, Not Null, and is the primary key.

initially i did this :

$sql = "LOAD DATA LOCAL INFILE '$inputfile1'
INTO TABLE $table1
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '\"'
LINES TERMINATED BY '\n' ";
mysql_query($sql) or die("ERREUR DE MISE A JOUR DU FICHER");
echo "Done!";

of course with the code above, i had 3 records added with wrong results... the ID had the product number value,
the product number field had the description value etc etc, because i did not put the (field1,field2,field3...) parameter

So i did this to correct it :

$sql = "LOAD DATA LOCAL INFILE '$inputfile1'
INTO TABLE $table1
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '\"'
LINES TERMINATED BY '\n' (prodno, descshort, dept, desclong, prix, unitevte, stock)";
mysql_query($sql) or die("ERREUR DE MISE A JOUR DU FICHER");
echo "Done!";

Now with this code above i have the following results :

added to the sql database , 1 record only, i had 3 records without the (field1, field2....) prameter.
the record added has the following (cut and paste from mysql database) look at the last record why is this. Only one record has been imported also.


4294967295 13243500 produit test 100 ceci est une longue description 12.95 ch 12

Re: importing from csv file to mySQL Dbase (php code)

Posted: Tue Oct 25, 2011 4:47 pm
by egg82
somewhere your id column is blank (whatever you're inserting into the DB there's a blank id)

Re: importing from csv file to mySQL Dbase (php code)

Posted: Tue Oct 25, 2011 4:54 pm
by grabber_grabbs
correct me if i am wrong here, but isnt the id field supposed to be incremented by the server itself... my csv file doesnt have any id field.
My csv file consist of the following field
prodno, descshort, dept, desclong, prix, unitevte, stock

so why the system is putting a weird value in the first and only record it import ?
Have you noticed that even if the ID field is wrong, all the rest of the fields are right, but with only one record imported... should have been 3.

Re: importing from csv file to mySQL Dbase (php code)

Posted: Tue Oct 25, 2011 5:11 pm
by egg82
not if you don't set it to "AUTO_INCREMENT"
What your csv did was "INSERT" the values into the table. The columns remain the same, but it inserts new rows with the existing values in your csv

Simply set the ID field to auto_increment and you should be fine

Re: importing from csv file to mySQL Dbase (php code)

Posted: Tue Oct 25, 2011 5:53 pm
by grabber_grabbs
This was posted into my first post

I have 99 records in the SQL file... so the ID of the first record imported should be 100 right ? The ID field is set with auto_increment, Not Null, and is the primary key.

Sorry i might not be clear, but, the ID field is set with auto_increment

If i try to add manually some records, i do not need to put anything in the ID field, i just fill all the other fields and when saved, the system add 1 to the ID field.

Re: importing from csv file to mySQL Dbase (php code)

Posted: Tue Oct 25, 2011 6:15 pm
by egg82
It will not start with 100. auto_increment goes from 1 to whatever the end is. So you'll have numbers 1-99 on your rows.
And that's what it should do with your .csv file...

So you're saying:
1. You have a mysql table with the columns `id` NOT NULL AUTO_INCREMENT, `prodno`, `descshort`, `dept`, `desclong`, `prix`, `unitevte`, and `stock`
2. You have a .csv file with information that fills a table with the exact same values and does not have an id
3. You used that file to import to that table
And it does not give correct IDs...

Short from manually inserting the IDs either into the .scv or the table, I honestly have absolutely no idea what's wrong with it or how to fix it.

Re: importing from csv file to mySQL Dbase (php code)

Posted: Tue Oct 25, 2011 6:43 pm
by grabber_grabbs
you see, i allready have 99 records into the MySQL file that i dont want to be overriden.
This is why i want to import records to ADD to the current mysql database.

will try to get help from ipage.com support team... ID is proprely setup with auto_increment, not null.
also, i do mention the different field name that i want to updated during the import... i mean i cannot make any mistake here because the system would crash.. and it is not, its says that 1 record has been added, with a value in the ID that i cannot understand.... Hope the ipage.com support team will be able to do something. Really dont know what i am doing wrong there. Could there be some kind of bugs on there end ? I am sure not.
Here is a cut and past of the csv file :

13243500,produit test,100,ceci est une longue description,12.95,ch,12
14567765,produit test 2,200,ceci est une longue description no 2,13.5,ch ,23
12312300,produit test 3,200,etgertwertwertwertwertewrr,14.57,ch,23

cannot be cleaner than this. only 3 records.
Prodno,-- descshort--, -dept-,- desclong--------------------, prix, unitevte,stock

Re: importing from csv file to mySQL Dbase (php code)

Posted: Tue Oct 25, 2011 6:51 pm
by egg82
good luck. I asked their support team once if I could gain access to my mysql using a CLI
they asked me what CLI meant...

Re: importing from csv file to mySQL Dbase (php code)

Posted: Tue Oct 25, 2011 6:56 pm
by Celauran
Did you try importing the CSV with phpMyAdmin?

Re: importing from csv file to mySQL Dbase (php code)

Posted: Tue Oct 25, 2011 7:02 pm
by grabber_grabbs
yes i did try with mysqladmin , and have the same results.

Re: importing from csv file to mySQL Dbase (php code)

Posted: Tue Oct 25, 2011 7:07 pm
by grabber_grabbs
i mean, using mysqladmin is not rocket science here.... you just give the path for the file, the field termination character which is "," in my case, and then you do specify all the fields name you want to be update. In my case, i do want ALL of them to be updated.
Have the same results. One record added. and when i look at it, i have a number in the ID field that i cannot undersand. It only import one record also.

Re: importing from csv file to mySQL Dbase (php code)

Posted: Tue Oct 25, 2011 7:13 pm
by Celauran
No error messages? It exits cleanly?

Re: importing from csv file to mySQL Dbase (php code)

Posted: Tue Oct 25, 2011 7:24 pm
by grabber_grabbs
yep, all is clean. no error.
you seems to be aware of mysqladmin... do you have an account with them (ipage.com) or is mysqladmin something available to all hosts ?

Re: importing from csv file to mySQL Dbase (php code)

Posted: Tue Oct 25, 2011 7:30 pm
by Celauran
I don't have an account with ipage. phpMyAdmin is pretty much universally available.

Re: importing from csv file to mySQL Dbase (php code)

Posted: Tue Oct 25, 2011 7:34 pm
by grabber_grabbs
what is funny here is if i leave the code without mentionning the fields name, it add 3 records to the file. All the info is imported, the only problem is that the info is not posted to the right field, like the prodno is in the Id, descshort is in the prodno, dept is in the descshort and so on.... so an easy fix to this was or should have been to add the (fields1, fields2,...) parmet er to the code and that should be it.
Dont have ennough experience here to know, but, with the ID set as auto_increment, do i still need to mention the fields name that i want to be updated ? I assume that by default the server should add to the first field, then go to the next one. First field here should be Prodno since ID is autoincrement. am i right here ?