May 10, 201214 yr Not got my brain in tonight so i thought i'd pick the wonderful minds of the folks here. I'm importing a simple .csv file (Exported from an mailing list managing website). The layout is as below... "Josh","blahblah@msn.com","Active", "john","blahblah@email.com","Active", "","email@lcc.arts.ac.uk","Bounced", "Aidan","smith@joe.uk","Active", and the MySQL table consists of the fields... ID Name Email Status The ID field is the primary key and auto increment. When trying to import this file in to my MySQL table it keeps coming up with the error ERROR Invalid field count in CSV input on line 1. The import settings i'm using within PHPMYADMIN are as follows... Partial Import: Unticked Format of imported file: CSV Fields terminated by: , Fields enclosed by: " Fields escaped by: \ Lines terminated by: auto Column names: Name,Email,Status Things i have done... Removed ID field from table Removed 'Status' field from csv and table Ideally i'd like to get the above CSV file into the database table so each entry has a unique ID. So i'm totally baffled, any help will be rewarded with plenty of man love (or rep points if preferred). p.s. there's about 2000 entries in the csv so editing that is frowned upon! Edited May 10, 201214 yr by MikeChipshop
May 10, 201214 yr This is what I have used before. Keep id in the table and set to auto-increment. The file has to be in the phpMyadmin directory. load data local infile 'mailingList.csv' into tablename fields terminated by ',' enclosed by '"' lines terminated by '\n' (Name, Email, Status)
May 10, 201214 yr If the above doesn't work: - Delete the ID column from the database - Import the CSV - Add an extra column at the start of the table called ID with auto increment and primary key It should auto-fill the entries with a unique ID. Oh, and I'll take the man-love, please!
May 11, 201214 yr Author Cheers chaps and chappettes (?) i've got to get back to this issue this afternoon so i'll let you all know how it goes and dish out loving as applicable. Although i've already tried your idea last night Spitfire and it no worky
May 11, 201214 yr Strange. It worked for me when I tested it =/ You could open the CSV in Excel, add the column, populate it with the ID's and then re-save as a CSV
May 11, 201214 yr try { $DBH = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass); } catch(PDOException $e) { echo $e->getMessage(); } // In case Bobby Tables has signed up $query = $DBH->prepare("INSERT INTO table VALUES (?, ?, ?)"); $handle = fopen('file.csv', r); while($row = fgetcsv($handle)) { $query->execute($row); } Edited May 11, 201214 yr by Renaissance-Design Half asleep
Create an account or sign in to comment