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  amp  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 lt br gt Urban Talent TV  amp  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 lt br gt 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  amp  Southern US  Persian  Asian  Indian   good ear for accents    Manchester School of Acting  with Mark Hudson lt br gt North Cheshire Theatre College  with David Johnson lt 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

User · Answer

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

User · Answer

In phpMyAdmin v 4 6 5 2 there s a checkbox option  The first line of the file contains the table column names

User · Answer

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

User · Answer

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

[mysql] importing a CSV into phpmyadmin

Examples related to mysql

Examples related to database

Examples related to csv

Examples related to phpmyadmin