Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.
Moderator: General Moderators
GeXus
Forum Regular
Posts: 631 Joined: Sat Mar 11, 2006 8:59 am
Post
by GeXus » Wed Dec 05, 2007 1:47 pm
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?
Christopher
Site Administrator
Posts: 13596 Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US
Post
by Christopher » Wed Dec 05, 2007 1:49 pm
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 » Wed Dec 05, 2007 2:20 pm
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?
feyd
Neighborhood Spidermoddy
Posts: 31559 Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA
Post
by feyd » Wed Dec 05, 2007 2:29 pm
They fill in in-order.
GeXus
Forum Regular
Posts: 631 Joined: Sat Mar 11, 2006 8:59 am
Post
by GeXus » Wed Dec 05, 2007 2:30 pm
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
feyd
Neighborhood Spidermoddy
Posts: 31559 Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA
Post
by feyd » Wed Dec 05, 2007 2:33 pm
This is something you can try .
GeXus
Forum Regular
Posts: 631 Joined: Sat Mar 11, 2006 8:59 am
Post
by GeXus » Wed Dec 05, 2007 2:33 pm
feyd wrote: This is something you can try .
Gotcha! (and it doesn't work)
Christopher
Site Administrator
Posts: 13596 Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US
Post
by Christopher » Wed Dec 05, 2007 4:29 pm
Create a temporary table with the same columns as your CSV data, then INSERT ... SELECT into your target table.
(#10850)
Kieran Huggins
DevNet Master
Posts: 3635 Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:
Post
by Kieran Huggins » Fri Dec 07, 2007 3:47 am
a HEAP table is nice and fast for temp tables.