在 MySQL Workbench 中，使用“表数据导入向导”导入 CSV 会创建空表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32898767/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
In MySQL Workbench, using "Table Data Import Wizard" to import CSV creates empty table
提问by Erik Hanson
I am attempting to import a csv file into a MySQL table using the Table Data Import Wizard. The sample section at the bottom of the Configure Import Settings screen looks fine and when I run the import, it says all of my entries were loaded successfully. However, when I go to view the contents of the table, only the columns are there and none of my actual data loaded. Does anyone know why this might be happening and how to correct it?
我正在尝试使用表数据导入向导将 csv 文件导入 MySQL 表。配置导入设置屏幕底部的示例部分看起来不错，当我运行导入时，它说我的所有条目都已成功加载。但是，当我查看表的内容时，只有列在那里，而没有加载任何实际数据。有谁知道为什么会发生这种情况以及如何纠正它？
EDIT:
编辑：
These are a few lines from my CSV file:
这些是我的 CSV 文件中的几行：
STATION,STATION_NAME,ELEVATION,LATITUDE,LONGITUDE,DATE,MLY-TAVG-NORMAL,MLY-TMAX-NORMAL,MLY-TMIN-NORMAL,Average Temp,Max Temp,Min Temp GHCND:USW00094085,PIERRE 24 S SD US,647.4,44.0194,-100.353,201001,218,322,113,21.8,32.2,11.3 GHCND:USW00094085,PIERRE 24 S SD US,647.4,44.0194,-100.353,201002,246,354,137,24.6,35.4,13.7 GHCND:USW00094085,PIERRE 24 S SD US,647.4,44.0194,-100.353,201003,339,451,226,33.9,45.1,22.6 GHCND:USW00094085,PIERRE 24 S SD US,647.4,44.0194,-100.353,201004,463,588,337,46.3,58.8,33.7 GHCND:USW00094085,PIERRE 24 S SD US,647.4,44.0194,-100.353,201005,573,696,450,57.3,69.6,45
车站，车站名称，海拔，纬度，经度，日期，MLY-TAVG-NORMAL，MLY-TMAX-NORMAL，MLY-TMIN-NORMAL，平均温度，最高温度，最低温度 GHCND：USW00094085，PIERRE 24 S SD US，647。 44.0194，-100.353,201001,218,322,113,21.8,32.2,11.3 GHCND：USW00094085，PIERRE第24号SD美国，647.4,44.0194，-100.353,201002,246,354,137,24.6,35.4,13.7 GHCND：USW00094085，PIERRE第24号SD美国， 647.4,44.0194，-100.353,201003,339,451,226,33.9,45.1,22.6 GHCND：USW00094085，PIERRE第24号SD美国，647.4,44.0194，-100.353,201004,463,588,337,46.3,58.8,33.7 GHCND：USW00094085，PIERRE第24号SD US,647.4,44.0194,-100.353,201005,573,696,450,57.3,69.6,45
Here are some images of the import process:
以下是导入过程的一些图像：
And here is what I see when I attempt to view the newly created table:
这是我尝试查看新创建的表时看到的内容：
Second Edit:
第二次编辑：
Here is an image of my settings:
这是我的设置的图像：
And here is an image of my CSV file showing line endings:
这是我的 CSV 文件的图像，显示了行尾：
回答by Taurai Benhura
Verify the encoding of your file. When parsing a source file as UTF-8, the workbench silently bombs out when it encounters non UTF-8 characters. I used cp1250 (windows 1250) for a file exported from Excel and everything worked properly.
验证文件的编码。将源文件解析为 UTF-8 时，工作台在遇到非 UTF-8 字符时会默默地轰炸。我将 cp1250 (windows 1250) 用于从 Excel 导出的文件，并且一切正常。
回答by BK435
You need to ensure that your csv options are set correctly when importing the table.
您需要确保在导入表时正确设置了 csv 选项。
When you get to the below screen notice how red arrow points to the wrench. By clicking that an option drop down to set your line separator, enclosing strings, and field separator will appear. If these are not set right then the data will load unsuccessfully.
当您进入以下屏幕时，请注意红色箭头如何指向扳手。通过单击该选项下拉以设置行分隔符，将出现包含字符串和字段分隔符。如果这些设置不正确，则数据将无法成功加载。
Below is a sample CSV file. Notice how in Notepad++the wrapper button is pressed so you can see the end of line. In this case the end of line is CR LF. The default option for Workbench Import wizard is just LF. Additionally, default field separator appears to be a ;and not a ,.
下面是一个示例 CSV 文件。请注意在Notepad++ 中如何按下包装按钮，以便您可以看到行尾。在这种情况下，行尾是CR LF。Workbench 导入向导的默认选项就是LF。此外，默认字段分隔符似乎是 a;而不是,。
You can read more here at dev.mysql
您可以在dev.mysql 中阅读更多信息
回答by Jennie
I ran into the same problem and tried to solve it with changing settings, but it didn't work for me. I just import all data as txt datatype and
我遇到了同样的问题并试图通过更改设置来解决它，但它对我不起作用。我只是将所有数据导入为 txt 数据类型和
(ALTER TABLE tablename MODIFY COLUMN datatype)
later to correct the data type.
稍后更正数据类型。
回答by Mike Lischke
Tried with your sample data and it imported fine. There must be something wrong with further data I guess. Maybe one (or more) of the records are not as they should be. The preview is good for the first number of records, so your import definition seems fine. Try importing smaller chunks of records. Start with 1, extend to ten etc. Until you see the import fail. You can also try with only one column and if that succeeds do a binary search for the column that fails.
尝试使用您的示例数据并导入正常。我猜进一步的数据肯定有问题。也许一个（或多个）记录不应该是。预览适用于第一条记录，因此您的导入定义看起来不错。尝试导入较小的记录块。从 1 开始，扩展到 10 等。直到您看到导入失败。您也可以尝试只使用一列，如果成功，则对失败的列进行二分搜索。
Note: there is bug when displaying the imported rows (and has been fixed for the next version). That always shows the total count of records, not those actually imported.
注意：显示导入的行时存在错误（已在下一个版本中修复）。这总是显示记录的总数，而不是实际导入的记录。
