importing from csv file to mySQL Dbase (php code)

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

grabber_grabbs
Forum Commoner
Posts: 60
Joined: Mon Oct 10, 2011 6:13 pm

importing from csv file to mySQL Dbase (php code)

Post 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
Last edited by grabber_grabbs on Tue Oct 25, 2011 5:04 pm, edited 2 times in total.
User avatar
egg82
Forum Contributor
Posts: 156
Joined: Sat Oct 01, 2011 9:29 pm
Location: Colorado, USA

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

Post by egg82 »

somewhere your id column is blank (whatever you're inserting into the DB there's a blank id)
grabber_grabbs
Forum Commoner
Posts: 60
Joined: Mon Oct 10, 2011 6:13 pm

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

Post 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.
User avatar
egg82
Forum Contributor
Posts: 156
Joined: Sat Oct 01, 2011 9:29 pm
Location: Colorado, USA

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

Post 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
grabber_grabbs
Forum Commoner
Posts: 60
Joined: Mon Oct 10, 2011 6:13 pm

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

Post 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.
User avatar
egg82
Forum Contributor
Posts: 156
Joined: Sat Oct 01, 2011 9:29 pm
Location: Colorado, USA

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

Post 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.
grabber_grabbs
Forum Commoner
Posts: 60
Joined: Mon Oct 10, 2011 6:13 pm

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

Post 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
User avatar
egg82
Forum Contributor
Posts: 156
Joined: Sat Oct 01, 2011 9:29 pm
Location: Colorado, USA

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

Post 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...
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

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

Post by Celauran »

Did you try importing the CSV with phpMyAdmin?
grabber_grabbs
Forum Commoner
Posts: 60
Joined: Mon Oct 10, 2011 6:13 pm

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

Post by grabber_grabbs »

yes i did try with mysqladmin , and have the same results.
grabber_grabbs
Forum Commoner
Posts: 60
Joined: Mon Oct 10, 2011 6:13 pm

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

Post 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.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

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

Post by Celauran »

No error messages? It exits cleanly?
grabber_grabbs
Forum Commoner
Posts: 60
Joined: Mon Oct 10, 2011 6:13 pm

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

Post 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 ?
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

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

Post by Celauran »

I don't have an account with ipage. phpMyAdmin is pretty much universally available.
grabber_grabbs
Forum Commoner
Posts: 60
Joined: Mon Oct 10, 2011 6:13 pm

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

Post 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 ?
Post Reply