Importing CSV into mysql

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
GeXus
Forum Regular
Posts: 631
Joined: Sat Mar 11, 2006 8:59 am

Importing CSV into mysql

Post by GeXus »

I've always had a hard time importing csv's into mysql... Is there a trick or an easy way? Do the column names in the table have to match that in the CSV? what if it uses different naming conversion or has additional columns?
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post by Christopher »

Code: Select all

LOAD DATA INFILE '/path/to/my/file.csv' INTO TABLE mytable;
There are options to specify delimeters, etc. See the manual.
(#10850)
GeXus
Forum Regular
Posts: 631
Joined: Sat Mar 11, 2006 8:59 am

Post by GeXus »

arborint wrote:

Code: Select all

LOAD DATA INFILE '/path/to/my/file.csv' INTO TABLE mytable;
There are options to specify delimeters, etc. See the manual.
I mean yeah I can see where you can specify delimiters and stuff, but like how do you map the column names in the csv to column names in a table?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

They fill in in-order.
GeXus
Forum Regular
Posts: 631
Joined: Sat Mar 11, 2006 8:59 am

Post by GeXus »

feyd wrote:They fill in in-order.
So what if I have a table as such

id | one | two | three | four

and a CSV file like

oneName | twoName | threeName | fourName


I have the id column in there that autoincrements
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

This is something you can try.
GeXus
Forum Regular
Posts: 631
Joined: Sat Mar 11, 2006 8:59 am

Post by GeXus »

feyd wrote:This is something you can try.
Gotcha! (and it doesn't work)
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post by Christopher »

Create a temporary table with the same columns as your CSV data, then INSERT ... SELECT into your target table.
(#10850)
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Post by Kieran Huggins »

a HEAP table is nice and fast for temp tables.
Post Reply