将CSV导入MySQL [英] Import CSV to MySQL
问题描述
我创建了一个数据库和一个表。我也创造了我需要的所有领域。我创建了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?
- CSV档案的栏名称必须与表格的栏名称相同
- .csv文件
- 使用LOAD DATA选项选择CSV
- 将 / li>
- 在
框中终止的字段中,键入,
- 框中括起的字段
-
\
- 在方块终止的行中,
$ c>
- 在列名称框中,键入列名称,由
,
c> column1,column2,column3 - 使用LOCAL关键字复选框ON。
- The column names of your CSV file must match those of your table
- Browse to your required .csv file
- Select CSV using LOAD DATA options
- Check box 'ON' for Replace table data with file
- In Fields terminated by box, type
,
- In Fields enclosed by box,
"
- In Fields escaped by box,
\
- In Lines terminated by box,
auto
- In Column names box, type column name separated by
,
likecolumn1,column2,column3
- 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
推荐答案
列。如果您的 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;
这篇关于将CSV导入MySQL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!