将 CSV 导入 phpmyadmin

2021-11-20 00:00:00 csv database mysql phpmyadmin

我有一个像这样的 CSV,

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

是否可以仅将这些数据插入到我数据库中的现有列中,我认为它可以将其作为一个新表插入,然后将其作为列名称 A、B、C、D、E 等.

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.

推荐答案

在 phpMyAdmin 中,单击表格,然后单击页面顶部的导入"选项卡.

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

浏览并打开 csv 文件.保持字符集不变.除非您有一个巨大的数据集(或慢速服务器),否则取消选中部分导入.选择文件后,格式应该已经选择了CSV",如果没有,则选择它(不使用 LOAD DATA).如果要在导入前清除整个表,请选中用文件替换表数据".如果您认为 CSV 文件中有重复项,可选择勾选忽略重复行".现在是重要的部分,将接下来的四个字段设置为这些值:

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.

现在点击 Go 按钮,它应该会成功运行.

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

相关文章