Help with mySQL import

[ozstar]

In phpmyAdmin exported using Excel with headings a dbase.

 

Chanegs were made to prices etc and it needs to be inserted into dbase.

 

In phpmyadmin I imported it but got this message..

 

What could cause this please?

 

I imported it over the current data in the dbase as this data supercedes that.

 

Thanks

 

oz

 

#1064 - 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 'products_id,products_quantity,products_model,products_image,products_price,produ' at line 1

[Guest]

David,

 

You can not import a CSV directly into your SQL database. You can EXPORT to a CSV file, but you would need something like Easy Populate or Smooth Populate to be able to use a upload a CSV into the database.

 

If you want to import directly into the MySQL database, you would have to export the database in SQL format, edit it and then import it as an SQL query.

 

 

 

Chris

[MrPhil]

If this is a one-off project, it might be easiest to manually convert the CSV file into one or more INSERT commands that could then be IMPORTed by MySQL with phpMyAdmin.

INSERT INTO tablename
(`field name 1`, `field name 2`, ....)
VALUES
('data1-1', 'data1-2', ...)
('data2-1', 'data2-2', ...)
...
;

This assumes that the table already exists, and you're just adding new records to it.

 

To avoid problems, limit it to maybe 100 to 200 sets of values per INSERT. If you run into timeout problems, you will need to split up the importation process into smaller steps. Just split up your one big .sql file into smaller ones at INSERT commands.

 

If any of the fields are "auto-increment", you need to decide whether you want to force a value, or let MySQL create the next value. If the latter, just omit the field name and value and it will automatically be incremented and filled in. If you force a value, check that the table's next autoincremented value skips over that value. Also be aware of whether that value is used as a Foreign Key anywhere else (which will need to be updated with the proper value).