将 CSV 导入 MySQL

2021-11-20 00:00:00 csv mysql load-data-infile

我已经创建了一个数据库和一个表.我还创建了我将需要的所有字段.我创建了 46 个字段，其中一个是我的行 ID.CSV 不包含 ID 字段，也不包含列的标题.我对这一切都很陌生，但一直在努力解决这个问题.我不是在这里懒惰地寻求答案，而是在寻找方向.

I have created a database and a table. I have also created all the fields I will be needing. I have created 46 fields including one that is my ID for the row. The CSV doesn't contain the ID field, nor does it contain the headers for the columns. I am new to all of this but have been trying to figure this out. I'm not on here being lazy asking for the answer, but looking for directions.

我想弄清楚如何导入 CSV，但让它从第二个字段开始导入数据，因为我希望 auto_increment 将填写 ID 字段，这是我创建的第一个字段.

I'm trying to figure out how to import the CSV but have it start importing data starting at the 2nd field, since I'm hoping the auto_increment will fill in the ID field, which is the first field I created.

我尝试了这些说明但没有成功.任何人都可以提供一些见解吗?

I tried these instructions with no luck. Can anyone offer some insight?

  1. CSV 文件的列名必须与表的列名一致
  2. 浏览到您需要的 .csv 文件
  3. 使用 LOAD DATA 选项选择 CSV
  4. 为用文件替换表数据
    5. 选中ON"复选框
  5. 在字段终止于框中，输入,
  6. 在由框包围的字段中，"
  7. 在字段转义框中，\
  8. 在行终止于框中，auto
  9. 在列名框中，输入以、分隔的列名，如column1,column2,column3
  10. 选中使用本地关键字的复选框.
  1. The column names of your CSV file must match those of your table
  2. Browse to your required .csv file
  3. Select CSV using LOAD DATA options
  4. Check box 'ON' for Replace table data with file
  5. In Fields terminated by box, type ,
  6. In Fields enclosed by box, "
  7. In Fields escaped by box, \
  8. In Lines terminated by box, auto
  9. In Column names box, type column name separated by , like column1,column2,column3
  10. Check box ON for Use LOCAL keyword.

CSV 文件为 32.4kb

The CSV file is 32.4kb

我的 CSV 的第一行是:

The first row of my CSV is:

Test Advertiser,23906032166,119938,287898,,585639051,287898 - Engager - 300x250,88793551,Running,295046551,301624551,2/1/2010,8/2/2010,Active,,Guaranteed,Publisher test,Maintainer test,example-site.com,,All,All,,Interest: Dental; custom geo zones: City,300x250,-,CPM,$37.49 ,"4,415","3,246",3,0,$165.52 ,$121.69 ,"2,895",805,0,0,$30.18 ,$37.49 ,0,$0.00 ,IMPRESSIONBASED,NA,USD

推荐答案

您可以在导入期间让 MySQL 为某些列设置值.如果您的 id 字段设置为自动递增，您可以在导入期间将其设置为 null，然后 MySQL 将为其分配递增值.尝试在 phpMyAdmin 的 SQL 选项卡中放置类似的内容:

You can have MySQL set values for certain columns during import. If your id field is set to auto increment, you can set it to null during import and MySQL will then assign incrementing values to it. Try putting something like this in the SQL tab in phpMyAdmin:

LOAD DATA INFILE 'path/to/file.csv' INTO TABLE your_table FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' SET id=null;

相关文章