importing data into ALREADY CREATED mySQL database

Question

Hi all,

PROBLEM: importing data into ALREADY CREATED mySQL database

I've done a lot of search online and know that I can import a CSV file into phpmyadmin in order for the table to be automatically created but I have created an empty table with the exact same fields as the CSV file but would like to just import the data itself.

The reason I don't want the import function to create the table for me is because I have created my table with certain restrictions/data types that arent correctly created when I do the import.

so basically, my question is, is there a way I can import data directly into an already created table (without having to manually write the 'INSERT INTO....' statements)?

Is there a piece of software that will convert spreadsheet data into an insert statement?

Thanks very much for your help! Theresa

Answer 1

You could give either of these a whirl:

Navicat - http://www.navicat.com/en/products/navicat_mysql/mysql_overview.html

or

Heidi - http://www.heidisql.com/

Navicat is a licensed product (i.e. paid for). Heidi is open source.

EDIT: Forgot to mention that certainly within Navicat you can import from Excel to a pre-defined table.

Answer 2

You can use the LOAD DATA INFILE syntax to load data into a table from a CSV file. Or from the shell mysqlimport which essentially does the same thing.

Check out the documentation here: http://dev.mysql.com/doc/refman/5.1/en/load-data.html

Answer 3

If you're just dealing with a CSV file, the answer will be, from your PHP code,use mysql's native LOAD DATA :

LOAD DATA INFILE 'yourfile.csv' INTO TABLE tbl_name
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n';