I am trying to import a .csv file into a MySQL table via phpMyAdmin.
我试图通过phpMyAdmin将.csv文件导入MySQL表。
The .csv file is separated by pipes, formated like this:
.csv文件由管道分隔,格式如下:
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.
当有|时出现问题然后是双引号。我总是得到“行N上的CSV输入中的无效字段计数”错误。
I am uploading the file from the import page, using Latin1, CSV, terminated by |, separated by ".
我从导入页面上传文件,使用Latin1,CSV,以|结尾,用“。”分隔。
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.
我想只是更改“括号”字符,但我不断收到“CSV导入的无效参数:包围的字段”。我尝试过各种各样的角色但没有成功。
How can I tell MySQL to accept this format in phpMyAdmin?
如何告诉MySQL在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.
设置这些表是编写程序的第一步,该程序将使用上载的gzipped .csv文件来维护电子商务站点的目录。
5 个解决方案
#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).
简短版本:1。)如果是Excel文件,保存为ODS(打开文档电子表格)格式。 1a。)如果文件是某种带分隔符的文本格式(如原版海报所示),则打开Excel,一旦在Excel中使用File / Open打开文件。在那里,您将能够选择适当的分隔符来查看文件。确保文件看起来没问题,然后保存为ODS格式(并关闭文件)。
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.
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.) Click "Go" and pray you didn't just waste another hour.
7.)点击“开始”,祈祷你不要再浪费一个小时。
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.
可以这么说,通过phpmyadmin导入似乎有两个主要问题。 1.)存在某种内存问题,导致导入大型Excel和ODS文件(有多大?尚不确定)。 2.)OpenOffice和Excel似乎都没有以与phpmyadmin兼容的方式保存他们的csv文件。他们希望用双引号来避免双引号。 phpMyAdmin想要使用其他东西进行双引号转义,例如正斜杠。
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).
希望在phpmyadmin(和/或Excel导入附加组件'PHPExcel')的更新中修复第一个问题。如果有一种简单的方法来更改保存为CSV的Excel或ODS文件的转义字符,或者如果phpMyAdmin可以与它们的格式兼容(这实际上应该非常简单。只需让它执行相同的操作),可以修复第二个问题。我们手动执行的find-replace动作可以避免双引号问题。
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).
我希望这对某人有所帮助,因为我花了3-4个小时发现这个解决方案,另一个小时在这里写。我希望它不会太久,但我希望能帮助所有专业水平的人从零到任何地方(可能大约0.1)。
#2
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.
我找到了一个有效的黑客 - 我使用$作为“封闭”字符,一切都很好。由于这是针对欧洲网站的,我知道他们永远不会在表格内容中使用它。
#3
you could modify the csv files by adding a \ in front of every ' right?
您可以通过在每个'右前方添加\来修改csv文件吗?
#4
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.
您是否尝试将“包围的字段”和“字段转义为”的方框置空?我没有使用过phpMyAdmin,但Google建议其他人使用这种方法取得成功。
#5
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.
您可能会考虑编写自己的LOAD DATA INFILE查询,看起来您无论如何都需要一个,因为此过程在某些时候将成为应用程序的一部分。
#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).
简短版本:1。)如果是Excel文件,保存为ODS(打开文档电子表格)格式。 1a。)如果文件是某种带分隔符的文本格式(如原版海报所示),则打开Excel,一旦在Excel中使用File / Open打开文件。在那里,您将能够选择适当的分隔符来查看文件。确保文件看起来没问题,然后保存为ODS格式(并关闭文件)。
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.
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.) Click "Go" and pray you didn't just waste another hour.
7.)点击“开始”,祈祷你不要再浪费一个小时。
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.
可以这么说,通过phpmyadmin导入似乎有两个主要问题。 1.)存在某种内存问题,导致导入大型Excel和ODS文件(有多大?尚不确定)。 2.)OpenOffice和Excel似乎都没有以与phpmyadmin兼容的方式保存他们的csv文件。他们希望用双引号来避免双引号。 phpMyAdmin想要使用其他东西进行双引号转义,例如正斜杠。
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).
希望在phpmyadmin(和/或Excel导入附加组件'PHPExcel')的更新中修复第一个问题。如果有一种简单的方法来更改保存为CSV的Excel或ODS文件的转义字符,或者如果phpMyAdmin可以与它们的格式兼容(这实际上应该非常简单。只需让它执行相同的操作),可以修复第二个问题。我们手动执行的find-replace动作可以避免双引号问题。
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).
我希望这对某人有所帮助,因为我花了3-4个小时发现这个解决方案,另一个小时在这里写。我希望它不会太久,但我希望能帮助所有专业水平的人从零到任何地方(可能大约0.1)。
#2
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.
我找到了一个有效的黑客 - 我使用$作为“封闭”字符,一切都很好。由于这是针对欧洲网站的,我知道他们永远不会在表格内容中使用它。
#3
you could modify the csv files by adding a \ in front of every ' right?
您可以通过在每个'右前方添加\来修改csv文件吗?
#4
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.
您是否尝试将“包围的字段”和“字段转义为”的方框置空?我没有使用过phpMyAdmin,但Google建议其他人使用这种方法取得成功。
#5
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.
您可能会考虑编写自己的LOAD DATA INFILE查询,看起来您无论如何都需要一个,因为此过程在某些时候将成为应用程序的一部分。