phpmyadmin (CSV) 中的 MySQL 导入因引号而阻塞
我正在尝试通过 phpMyAdmin 将 .csv 文件 导入到 MySQL 表中。
.csv 文件由竖线分隔，格式如下：
data|d'ata|d'a"ta|dat"a| data|"da"ta|data|da't'a| dat'a|data|da"ta"|da'ta|
数据包含引号。 我无法控制接收数据的格式——它是由第三方生成的。
当有 | 时，问题就来了。 后跟双引号。 我总是收到“第 N 行 CSV 输入中的字段计数无效”错误。
我正在从导入页面上传文件，使用 Latin1、CSV，以 | 结尾，以 " 分隔。
我只想更改“封闭的”字符，但我不断收到 “CSV 导入的参数无效：字段包含在” 中，我尝试了各种字符，但没有成功
如何告诉 MySQL 在 phpMyAdmin 中接受此格式？
设置这些表是。编写一个程序的第一步，该程序将使用上传的 gzipped .csv 文件来维护电子商务网站的目录。
I am trying to import a .csv file into a MySQL table via phpMyAdmin.
The .csv file is separated by pipes, formated like this:
data|d'ata|d'a"ta|dat"a| data|"da"ta|data|da't'a| dat'a|data|da"ta"|da'ta|
The data contains quotes. I have no control over the format in which I recieve the data -- it is generated by a third party.
The problem comes when there is a | followed by a double quote. I always get an "invalid field count in CSV input on line N" error.
I am uploading the file from the import page, using Latin1, CSV, terminated by |, separated by ".
I would like to just change the "enclosed by" character, but I keep getting "Invalid parameter for CSV import: Fields enclosed by". I have tried various characters with no success.
How can I tell MySQL to accept this format in phpMyAdmin?
Setting up these tables is the first step in writing a program that will use uploaded gzipped .csv files to maintain the catalog of an e-commerce site.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
在过去的几个小时里我一直遇到类似的问题，我终于得到了导入工作，所以我将分享我的解决方案，即使它可能对原始海报没有帮助。
简短版本：
1.) 如果是 Excel 文件，请另存为 ODS（打开文档电子表格）格式。
1a.) 如果文件是某种带有分隔符的文本格式（如原始海报所示），则打开 Excel，然后在 Excel 中使用“文件/打开”打开该文件。 您可以在此处选择适当的分隔符来查看文件。 确保文件看起来没问题，然后另存为 ODS 格式（并关闭文件）。
2.) 在 OpenOffice Calc（从 Oracle/Sun 免费下载）中打开文件。
2a.) 按 Ctrl-F 打开“查找”对话框。 单击更多选项并确保未选中“仅当前选择”。
2b.) 搜索双引号。 如果您的文件中没有任何内容，您可以跳过步骤 4 和 5。
3.) 另存为 -> 文本 CSV。 选择 UTF-8 格式选项（按“u”3 次可快速到达），选择“;” （分号）作为分隔符，并为文本选择双引号。
4.) 如果在步骤 2b 中在文件中发现任何双引号，请继续，否则只需使用 phpMyAdmin 将文件导入为 CSV（请参阅步骤 6）。 它应该有效。
5a.) 在 Word 或任何其他文本编辑器中打开，您可以在其中执行“查找”->“ 全部替换。
5b.) 通过搜索 """ 查找连续三个双引号的所有实例（如果确实找到，您甚至可能想连续搜索 4、5、6 等，直到出现空的情况）。
5c.) 将“””替换为 csv 中其他任何位置都找不到的占位符。 我将它们替换为“abcdefg”。
5d.) 查找-> 将“”（连续两个双引号）的所有实例替换为“（正斜杠和双引号）。5e
.) 查找 -> 将 abcdefg（或您在步骤 5c 中选择的占位符）的所有实例替换为“”。5c并且此步骤确保在文本分隔引号之前的字段末尾出现的任何引号都正确“转义”。5f
。）最后，保存文件，保留为 UTF-8（或导入所需的任何格式）。 6.a
) 在 phpMyAdmin 中，单击“导入”选项卡，单击“选择文件”按钮，然后选择刚刚保存的文件。
6b.) 如果列名称为 CSV，则应选择该文件。在第一行中，确保选中复选框。最重要的是，“字段终止于”应设置为 ; （分号），“字段包含于”应设置为“（双引号），并且“字段转义为”。 ' 应设置为 \（正斜杠）。 您可以按照步骤 3 在文件中进行设置，如有必要，还可以按照步骤 5a - 5f 进行设置。
7.) 单击“开始”并祈祷您没有再浪费一个小时。
既然短版本已经这么长了，我将跳过长版本。
可以这么说，通过 phpmyadmin 导入似乎有 2 个主要问题。
1.) 存在某种内存问题，无法导入大型 Excel 和 ODS 文件（多大才算大？还不确定）。
2.) OpenOffice 和 Excel 似乎都没有以与 phpmyadmin 兼容的方式保存它们的 csv 文件。 他们想用双引号转义双引号。 phpMyAdmin 希望用其他内容（例如正斜杠）转义双引号。
第一个问题有望在 phpmyadmin（和/或 Excel 导入插件“PHPExcel”）的更新中得到解决。
如果有一种简单的方法来更改保存为 CSV 的 Excel 或 ODS 文件的转义字符，或者如果 phpMyAdmin 可以与其格式兼容（这实际上应该很容易。只需让它执行相同的操作即可修复第二个问题）我们上面手动执行的查找替换操作是为了解决双引号问题）。
我希望这对某人有帮助，因为我花了 3-4 个小时发现这个解决方案，又花了一个小时在这里写下来。 我希望它不会太长，但我希望能够帮助各个专业水平的人们，从零到我所在的任何地方（可能在 0.1 左右）。
I've been having a similar problem for the last several hours and I've finally gotten an import to work so I'll share my solution, even though it may not help the original poster.
Short version:
1.) if an Excel file, save as ODS (open document spreadsheet) format.
1a.) If the file is some kind of text format with delimiters (like the original poster has), then open Excel, and once inside Excel use File/Open to open the file. There you will be able to select the appropriate delimiter to view the file. Make sure the file looks alright, THEN save as ODS format (and close the file).
2.) Open the file in OpenOffice Calc (free download from Oracle/Sun).
2a.) Press Ctrl-F to open the Find dialog box. Click More Options and make sure "Current Selection Only" is NOT checked.
2b.) Search for double quotes. If there are none in your file, you can skip steps 4 and 5.
3.) Save As -> Text CSV. Select options for UTF-8 format (press "u" 3 times to get there fast), select ";" (semi colon) as separator, and select double quotes for text.
4.) If there were any double quotes found in your file in step 2b, continue, otherwise just import the file as CSV with phpMyAdmin (see step 6). It should work.
5a.) Open in Word or any other text editor where you can do Find -> Replace All.
5b.) Find all instances of three double quotes in a row by searching for """ (if you do find any, you might even want to search for 4, 5, 6 etc. in a row until you come up empty).
5c.) Replace the """ with a placeholder that is not found anywhere else in your csv. I replaced them with 'abcdefg'.
5d.) Find -> Replace all instances of "" (two double quotes in a row) with " (forward slash and double quote).
5e.) Find -> Replace all instances of abcdefg (or your chosen placeholder from step 5c) with "". 5c and this step ensure that any quotes occuring at the end of a field just before the text-delimiting quote are properly 'escaped'.
5f.) Finally, save the file, keeping in UTF-8 (or whatever format you need for import).
6.a) In phpMyAdmin, click the "import" tab, click the "choose file" button, and select the file you just saved.
6b.) under 'Format of imported file' CSV should be selected. If column names are in the first row, make sure that checkbox is checked. Most importantly, 'Fields terminated by' should be set to ; (semi colon), 'Fields enclosed by' should be set to " (double quotes), and 'Fields escaped by' should be set to \ (forward slash). You set that up in your file by following step 3, and if necessary by following steps 5a - 5f.
7.) Click "Go" and pray you didn't just waste another hour.
Now that the short version has turned out this long, I'll skip the long version.
Suffice it to say, there seem to be 2 major problems with importing through phpmyadmin.
1.) There's some kind of memory problem that prevents large Excel and ODS files (how large is large? not sure yet) being imported.
2.) Neither OpenOffice nor Excel seem to save their csv files in a way that's compatible with phpmyadmin. They want to escape double quotes with double quotes. phpMyAdmin wants double quotes escaped with something else, like forward slash.
The first problem will hopefully be fixed in an update of phpmyadmin (and/or the Excel importing add-on 'PHPExcel').
The second one could be fixed if there was an easy way to change the escape character for Excel or ODS files saved as CSV, or if phpMyAdmin could be made compatible with their format (that should actually be pretty easy. Simply have it perform the same find-replace actions we performed manually above to skirt the double quote problem).
I hope this helps somebody, as I spent 3-4 hours discovering this solution and another hour writing it here. I hope it's not too long, but I was hoping to help people at all levels of expertise from zero to wherever I am (probably around 0.1).
我发现了一个可行的方法——我使用 $ 作为“封闭”字符，一切都很好。 由于这是针对欧洲网站的，我知道他们永远不会在表格内容中使用它。
I found a hack that works -- I use the $ as the "enclosed by" character and all is well. Since this is for a European site, I know that they'll never use it in the table content.
您可以通过在每个 ' 前面添加 \ 来修改 csv 文件，对吧？
you could modify the csv files by adding a \ in front of every ' right?
您是否尝试过清空显示“由以下内容包围的字段”和“由以下内容转义的字段”的框？ 我没有使用过 phpMyAdmin，但 Google 建议 其他人已经使用此方法取得了成功。
Have you tried blanking the boxes that read "Fields enclosed by" and "Fields escaped by"? I have not used phpMyAdmin, but Google suggests others have had success with this method.
您可能会考虑编写自己的 LOAD DATA INFILE< /a> 查询，似乎您无论如何都需要一个查询，因为此过程在某个时候将成为应用程序的一部分。
You might consider just writing your own LOAD DATA INFILE query, seems like you'll need one anyway since this process will be part of an application at some point.