What's the easiest way to import a new table into MySQL v5 from CSV?

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

Write a simple Python script that parses the CSV and inserts it into the table.

Look at the csv and mysqldb module.

Answer 2

I use SQLyog on my Windows system which has a free Community Edition and has an option to import from CSV.

I've never used this option myself so I can't tell you how good it is. However, SQLyog has been great for all the other things I've used it for.

Answer 3

I would use a spreadsheet editor to make a set of SQL statements. Put a new column at the start and add insert into tablename values(' . Add other columns to seperate the data with code like '','. Finish with ''); . Use the autofill feature to drag these cells down to as many rows as necessary. Copy the entire sheet to a plain text editor and remove the excess tabs, leaving you with a simple set of insert statements.

This is a solution that I can use for any database system and any spreadsheet file format. Plus it is easy to populate the spreadsheet from sources such as other databases, or copying and pasting from a webpage. It's also quite fast and available from any desktop machine, using Excel, OpenOffice or Google Docs.

See my example spreadsheet in Excel and OpenOffice versions.

Answer 4

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

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

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 7

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 8

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 9

Use the RazorSQL SQL GUI Tool. It has a database import tool that supports csv, excel, fixed width files, or sql script files. It works with MySQL, MS SQL Server, PostgreSQL, Oracle, SQLite, etc.

Answer 10

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.