将 CSV 导入 MySQL
我已经创建了一个数据库和一个表。我还创建了我需要的所有字段。我创建了 46 个字段，其中一个字段是我的行 ID。 CSV 不包含 ID 字段，也不包含列的标题。我对这一切都很陌生，但一直在努力解决这个问题。我不是在这里懒惰地寻求答案，而是在寻找方向。
我正在尝试弄清楚如何导入 CSV，但让它从第二个字段开始导入数据，因为我希望 auto_increment 能够填充 ID 字段，这是我创建的第一个字段。
我尝试了这些说明，但没有成功。谁能提供一些见解？
- CSV 文件的列名称必须与表格的列名称相匹配
- 浏览到所需的 .csv 文件
- 使用“加载数据”选项选择 CSV 将
- 用文件替换表格数据复选框选中为“打开”
- 在字段终止中by 框中，键入
, - 在 由 框包围的字段中，
" - 在由 框转义的字段中，
\ - 在 行终止符 框中，
auto - 在 列名称 框中，键入以
分隔的列名称，就像column1,column2,column3 - 选中使用本地关键字复选框
编辑：
CSV 文件是 32.4kb
我的 CSV 的第一行是：
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
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.
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?
- 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.
Edit:
The CSV file is 32.4kb
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
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您可以在导入期间为某些列设置 MySQL 值。如果您的 id 字段设置为自动递增，则可以在导入过程中将其设置为 null，然后 MySQL 将为其分配递增值。尝试在 phpMyAdmin 的 SQL 选项卡中添加如下内容：
You can have MySQL set values for certain columns during import. If your
idfield 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:请查看此页面，看看其中是否有您想要的内容。应该是您所需要的，因为您只处理一张桌子。 MYSQL LOAD DATA INFILE
例如，您可能会这样做像这样的东西：
这应该会给你一个想法。当然，还可以添加更多选项，如上面的链接所示。
Please look at this page and see if it has what you are looking for. Should be all you need since you are dealing with just one table. MYSQL LOAD DATA INFILE
So for example you might do something like this:
That should give you an idea. There are of course more options that can be added as seen in the above link.
如果导入文件是本地的，请务必使用 LOAD DATA LOCAL INFILE。 :)
be sure to use LOAD DATA LOCAL INFILE if the import file is local. :)