尝试读取结果集时遇到MySQL致命错误加载CSV文件

Trying to load a CSV file into a MySQL Database and I get a run time

尝试将CS​​V文件加载到MySQL数据库中,我得到一个运行时

Fatal error encountered attempting to read the resultset. 

A similar question was asked here in April. I didn't see any replies but hoping someone else has seen this. here is the command from the debugger:

4月份在这里提出了类似的问题。我没有看到任何回复,但希望别人看到这个。这是来自调试器的命令:

"LOAD DATA LOCAL INFILE 'D:\\SANCentral\\Customer Files\\ibm\\70738\\0918\\Switch 
Port.csv' INTO TABLE By_Switch FIELDS TERMINATED BY ',' LINES TERMINATED BY '\\\r\\n' IGNORE 1 LINES"

Here is the C# code:

这是C#代码:

string ConnectionString =  String.Format(@"server={0};userid={1};
        password={2};database={3}", server, user, passwd, database);
        MySqlConnection sqlconnect = new MySqlConnection(ConnectionString);
        sqlconnect.Open();
        IEnumerable<FileInfo> files = this.InputFileListView.Items.Cast<FileInfo>();
        string commandstring = String.Format(@"LOAD DATA LOCAL INFILE '{0}' INTO TABLE {1} FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' IGNORE 1 LINES", 
                                                files.FirstOrDefault().ToString(), "By_Switch");
        MySqlCommand cmd = new MySqlCommand(commandstring,sqlconnect);
        cmd.ExecuteNonQuery();

I have verified that the column names in the database match the column names in the incoming CSV file.

我已经验证数据库中的列名与传入CSV文件中的列名相匹配。

1 个解决方案

#1

So I figured it out. I used MySQL Workbench to run the same load command. MySQL was more descriptive of the issues. First, I had an incorrect data type on a column in the table. Next, apparently if you say a line ends in \n but it ends in \r\n that is an issue. In this file it appears there is a mix of \r\n and just \n. So I guess I will have to normalize the line endings before loading files. Finally, some of the actual data values are missing, so you get rows with ,, in them. I am not sure what to do about that.

所以我明白了。我使用MySQL Workbench来运行相同的加载命令。 MySQL更能描述问题。首先,我在表中的列上有不正确的数据类型。接下来,显然如果你说一行以\ n结尾,但它以\ r \ n结尾,这是一个问题。在这个文件中,它出现了\ r \ n和\ n的混合。所以我想我必须在加载文件之前规范化行结尾。最后,缺少一些实际数据值,因此您可以获得包含其中的行。我不知道该怎么做。