What's the easiest way to import a new table i

Question

I'm running MySQL 5 on a linux server on my local network. Running windows XP for my desktop. Had a look at the MySQL GUI Tools but I dont think they help. I cannot install apache on the remote server & use something like PHPmyAdmin.

Answer 1

From the MySQL shell or query browser...

If the CSV has no header:

LOAD DATA INFILE 'mycsvfile.csv' INTO TABLE mytable;

If the CSV has a header:

LOAD DATA INFILE 'mycsvfile.csv' INTO TABLE mytable IGNORE 1 LINES;

Answer 2

Toad for MySQL will do this nicely, with considerable control over the import (selectively matching columns for example) and most enduringly it's free.

I've also used SQLYog, but you have to have the commercial version for this as import from file isn't available in the community edition.

Toad is an excellent bit of software which comes in versions for all major databases and I've used both the MSSQL and Oracle versions in the past too. Recommended.

Answer 3

have a look @ load data infile : http://dev.mysql.com/doc/refman/5.0/en/load-data.html

Answer 4

I just did this using LOAD DATA INFILE but it's worth noting that it's not quite as simple as Gareth's example (Kai is quite right that you should look at the documentation). To correctly import comma-separated values, I used this:

LOAD DATA LOCAL INFILE 'mycsvfile.csv' INTO TABLE mytable 
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

This assumes a CSV file with quotation marks around each field and a single header row at the top. (You probably don't need the LINES TERMINATED BY since it should be the default, but it's a good practice to be explicit.)

Answer 5

If you don't mind using a bit of commercial software then Navicat (http://mysql.navicat.com/) is a very useful bit of software and is available for Mac, Windows, Linux. I use it regularly for importing a large CSV file into a database.

Answer 6

The Toad application does work wonders and is freeware. If you have a proper CSV file, it will create the table and import all data for you.