importing a CSV into phpmyadmin

Question

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,Strawberry,Shortcake,Female,11 Jan 1942,FALSE,Manchester,5'2,Brown,Black,Mid-length,Native Lancashire,Equity,Urban Talent TV & Drama Workshops,Strawberry-Shortcake---5.4.06.jpg,Yes,Yes
,29,urban talent,Rainbow,Brite,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",Rainbow Brite 1_1.jpg,Yes,Yes
,31,urban talent,Webbigail,Vanderquack,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,Webbigail Vanderquack web 1.jpg,Yes,Yes
,33,urban talent,Smurfette,Smurf,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",Smurfette Smurf 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 1

This is happen due to the id(auto increment filed missing). If you edit it in a text editor by adding a comma for the ID field this will be solved.

Answer 2

In phpMyAdmin, click the table, and then click the Import tab at the top of the page.

Browse and open the csv file. Leave the charset as-is. Uncheck partial import unless you have a HUGE dataset (or slow server). The format should already have selected “CSV” after selecting your file, if not then select it (not using LOAD DATA). If you want to clear the whole table before importing, check “Replace table data with file”. Optionally check “Ignore duplicate rows” if you think you have duplicates in the CSV file. Now the important part, set the next four fields to these values:

Fields terminated by: ,
Fields enclosed by: “
Fields escaped by: \
Lines terminated by: auto

Currently these match the defaults except for “Fields terminated by”, which defaults to a semicolon.

Now click the Go button, and it should run successfully.

Answer 3

In phpMyAdmin v.4.6.5.2 there's a checkbox option "The first line of the file contains the table column names...." :

Answer 4

Using the LOAD DATA INFILE SQL statement you can import the CSV file, but you can't update data. 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, field2, field3); 
  

• UPDATE the real table joining the table

  UPDATE maintable
  INNER JOIN temp_table A USING (field1)
  SET maintable.field1 = temp_table.field1