处理 csv 的最佳方法，为 mySql 做准备
我们有一个 CSV 文件，我们对其进行了仔细检查和剥离，以按照我们想要的格式显示数据。
因此，这个 csv 文件的大小不到 500kb。我已转换为 sql（另存为 txt）希望没问题。
原始 csv 数据条目有 3 个字段，如下所示：
'STANLEY','7331','TAS'
'GORMANSTON','7466','TAS'
转换后如下所示：
INSERT INTO suburbs ('Locality','Pcode','州') VALUES ('\'斯坦利\'','\'7331\'','\'TAS\ '');
INSERT INTO 郊区 ('Locality','Pcode','State') VALUES ('\'GORMANSTON\'','\'第7466章
好吧，现在，我不是数据库官员，我想知道。我是否正确转换了它？ 我是否应该考虑使此代码更清晰以导入数据库。
该文件的 sql 超过 1.6 mb，有超过 16,000 个条目，所以想确保我已经正确完成了操作。
干杯
We have a CSV file, which we have meticulously checked and stripped to show the data in the format we want.
As such this csv file is, just under 500kb in size. I have converted to sql ( saved as txt ) hope thats ok.
The original csv data entry is 3 fields, as thus:
'STANLEY','7331','TAS'
'GORMANSTON','7466','TAS'
After conversion its like so:
INSERT INTO suburbs ('Locality','Pcode','State') VALUES ('\'STANLEY\'','\'7331\'','\'TAS\'');
INSERT INTO suburbs ('Locality','Pcode','State') VALUES ('\'GORMANSTON\'','\'7466\'','\'TAS\'');
Ok now, not being a db officianado, I would like to know. Have I converted it correctly ?
Should I be looking at making this code cleaner for import to the db.
The sql is over 1.6 mb for this file, with over 16,000 entries, so want to make sure I have done things correctly.
Cheers
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
正如亚当的评论所说，您很可能不想插入引号，而您正在使用“斯坦利”等进行操作。
此外，在“现场”方面（地点等），请确保这些是反引号（非移位波形符）和数据方（STANLEY）是单引号。
改为：
除此之外，我看不出有什么问题。
As with what Adam's comment said, you're most likely not going to want to insert the quotes, which you are doing with \'STANLEY\' etc.
Also, on the 'field' side (locality etc), make sure those are back ticks (non-shift tilde), and the data side (STANLEY) are single quotes.
change to:
Other than that, I don't see anything wrong with it.
看起来不错，除了转义的引号之外。我通常将这种技术用于 Excel 文件。我有我的列，然后创建一个公式来生成适当的插入语句。或者，您可以使用 SSIS 之类的工具将数据导入数据库。
Looks fine, other than the escaped quotes. I use this technique usually with an excel file. Where I have my columns and then I create a formula to generate the appropriate insert statements. Alternatively you can use something like SSIS to get your data into your db.
您的 SQL 看起来不错，但额外的转义单引号不会最终出现在您的记录中吗？我不确定您的记录中是否需要“STANLEY”或仅 STANLEY，所以我将由您决定。
你已经完成一半的工作了。您有插入策略，也有回滚策略吗？看来这对您来说是一次大数据迁移，如果我可以谦虚地建议您尝试插入垃圾表中的几行，而您不介意先删除这些行。如果必须撤消更改并且没有任何到位或准备好撤消任何错误，那么这总是很痛苦。
Your SQL looks good, although won't the extra escaped single quotes end up in your records? I'm not sure if you want 'STANLEY' or just STANLEY in your records, so I'll leave it up to you.
You have half of your work done. You have an insert strategy, do you have a rollback strategy as well? It seems as if this is a big data migration for you, if I might so humbly suggest that you try the insert with just a few rows in a junk table that you don't mind getting rid of first. It's always a pain if the changes have to be undone and there is nothing in place or ready to go to undo any errors.