March 16, 2012
importing a CSV into phpmyadmin
Question by sico87
I have a CSV that looks like this,
candidate_id,show_on_site,first_name,surname,gender,DOB,showdob,Location,height,eyes,hair_colour,hair_length,accents,unions,training,url,visible,availability
,26,urban talent,Alice,Barry,Female,11 Jan 1942,FALSE,Manchester,5'2,Brown,Black,Mid-length,Native Lancashire,Equity,Urban Talent TV & Drama Workshops,Alice-Barry---5.4.06.jpg,Yes,Yes
,29,urban talent,Angela,Murray,Female,12 Oct 1970,FALSE,Manchester,5'7,Brown,Dark Brown,Long,"Native Manchester, others include - Cheshire, RP, Patois, Standard USA",Equity Member,"BA Acting Studies, Arden School of Theatre<br>Urban Talent TV & Drama Workshops",Angela Murray 1_1.jpg,Yes,Yes
,31,urban talent,Christine,Barton-Brown,Female,4 Jun 1947,FALSE,Manchester,5'0,Hazel,Blonde,Mid-length,"Native Manchester, others include - Liverpool, Cockney, Birmingham, West Country, Standard Scottish, Standard Welch, S Irish",,Manchester School of Acting<br>3 Years at David Johnson Acting Workshops,Christine Barton-Brown web 1.jpg,Yes,Yes
,33,urban talent,Davinia,Jokhi,Female,1 Jul 1979,FALSE,Manchester,5'2,Dark Brown,Dark Brown,Long,"Native Manchester, others include - Liverpool, RP, Lancashire, Birmingham, Cockney, Devon, Geordie, West Country, Glasgow, Edinburgh, South African, Standard & Southern US, Persian, Asian, Indian ~ good ear for accents",,"Manchester School of Acting, with Mark Hudson<br>North Cheshire Theatre College, with David Johnson<Oldham Theatre Workshop",Davinia Jokhi web 4.jpg,Yes,Yes
Is it possible to just insert this data into the existing columns in my database, all I can seem to it insert it as a new table which then has columns name A, B, C , D, E etc.
Answer by Starx
Using the LOAD DATA INFILE SQL statement you can import the CSV file, but to update you can’t. However, there is a trick you can use.
- Create another temporary table to use for the import
-
Load onto this table from the CSC
LOAD DATA LOCAL INFILE '/file.csv' INTO TABLE temp_table FIELDS TERMINATED BY ',' LINES TERMINATED BY 'n' (field1, filed2, field3); -
UPDATE the real table joining the table
UPDATE maintable INNER JOIN temp_table A USING (field1) SET maintable.field1 = temp_table.fiedl1
