将 CSV 加载到 MySQL 这是一个错误吗?- 文件未找到异常 [英] Loading CSV into MySQL Is This a Bug? - File Not Found Exception

问题描述

我正在尝试将 CSV 文件加载到 C# 中的 MySQL 数据库中，但我不断收到无法从结果集中读取的异常.我在 MySQL Workbench 中清理了语法，以至于我可以将 CSV 文件加载到 MySQL workbenc 中的数据库中而没有错误.在我的程序中尝试它，我仍然遇到异常.我正在处理的文件具有以下路径和文件名:

I am attempting to load a CSV file into a MySQL database in C# and I keep getting an exception could not read from the result set. I got the syntax cleaned up in MySQL workbench to the point that I can load a CSV file into the database in MySQL workbenc with no errors. Trying it in my program, I still get the exception. The file I am working with has this path and filename:

D:\SANCentral\Customer Files\ibm\70738\0918\switch port.csv

我得到的异常是无法从结果集中读取".查看内部异常，我找不到文件.我不知道这是从哪里来的.D:\Projects\CSVParser 是我的项目的位置.我假设 MySQL.Data.Client 类正在使用我给它打开文件的位置做一些事情.这是 SQL 命令试图从显然加载文件的路径:

The exception I get is a 'could not read from resultset'. Looking at the inner exception I get file not found with this. I have no idea where that is coming from. D:\Projects\CSVParser is the location for my project. I am assuming the MySQL.Data.Client class is doing something with the location I give it to open a file. This is the path that the SQL command is trying to load the file from apparently:

 {"Could not find file  'D:\\Projects\\CSVParser\\CSVParser\\bin\\Debug\\SANCentralCustomer Filesibm70738'
.":"D:\\Projects\\CSVParser\\CSVParser\\bin\\Debug\\SANCentralCustomer Filesibm70738"}

我该如何解决这个问题?我检查了我想在调试器中解析的文件的路径，它是正确的.我尝试将 .CSV 文件移动到我从中运行项目的项目目录中，但仍然出现异常，但它是路径中的非法字符，而不是找不到文件.

How can I fix this? I checked the path on the file I want to parse in the debugger and it is correct. I tried moving the .CSV file into the project directory where I am running the project from and I still get an exception, but it is illegal characters in path, instead of file not found.

这是执行加载的 C# 代码和 MySQL 语句:

Here's the C# code to do the load, and the MySQL statement:

string ConnectionString =  String.Format(@"server=localhost;userid={0};
        password={1};database=PerformanceMonitors;
Allow User Variables=True", user,password);
        MySqlConnection sqlconnect = new MySqlConnection(ConnectionString);
        sqlconnect.Open();

这是 MySQL 语句并执行查询.抱歉，字符串有点长:

Here's the MySQL statement and exccuting the query. Sorry the string is kinda long:

string working = String.Format(@"LOAD DATA LOCAL INFILE '{0}' IGNORE
INTO TABLE {1} COLUMNS TERMINATED BY ',' LINES TERMINATED BY '\n'
  IGNORE 1 LINES (`Switch`,`Port`,`WWPN`,@the_slot,`Port Index`,@the_time,
`Interval`,`Port Send Packet Rate`,  
`Port Receive Packet Rate`,`Total Port Packet Rate`,`Port Send Data Rate`, 
`Port Receive Data Rate`,`Total Port Data Rate`, 
Peak Send Data Rate`,`Port Peak Receive Data Rate`,

端口发送包大小,端口接收包大小,端口总包大小,
错误帧率,转储帧率,
链路故障率,同步丢失率,信号丢失率,CRC错误率,
短帧率、长帧率、编码视差错误率、
Discarded Class3 Frame Rate,F-BSY Frame Rate,F-RJT Frame Rate,
端口发送带宽百分比,端口接收带宽百分比,总端口带宽百分比,原始序列协议错误率,
无效传输字率,链路重置传输率,链路重置接收率)

Port Send Packet Size,Port Receive Packet Size,Overall Port Packet Size,
Error Frame Rate,Dumped Frame Rate,
Link Failure Rate,Loss of Sync Rate,Loss of Signal Rate,CRC Error Rate,
Short Frame Rate,Long Frame Rate,Encoding Disparity Error Rate,
Discarded Class3 Frame Rate,F-BSY Frame Rate,F-RJT Frame Rate,
Port Send Bandwidth Percentage,Port Receive Bandwidth Percentage, Overall Port Bandwidth Percentage,Primitive Sequence Protocol Error Rate,
Invalid Transmission Word Rate,Link Reset Transmitted Rate,Link Reset Received Rate)

    SET Slot = nullif(@the_slot,''), Time= str_to_date(@the_time,'%m/%d/%y %h:%i %p')", files.FirstOrDefault().ToString(), "by_switch");


       string commandreplaced= working.Replace("\n", "");

        MySqlCommand cmd = new MySqlCommand(commandreplaced,sqlconnect);
        cmd.ExecuteNonQuery();

推荐答案

好吧，我终于找到了答案.LOAD DATA FILE 语句要求文件名是一个字符串值，它不能被参数化.不幸的是，MySQLCommand 类不喜欢 C# 字符串对象的文件路径中的转义 \，因此它找不到该文件.我通过这样逃避转义来让它工作:

Well I finally figured out the answer. the LOAD DATA FILE statement requires the filename be a string value, it cannot be parameterized. Unfortunately the MySQLCommand class doesn't like the escaped \ in the file path of a C# string object so it can't find the file. I got it to work by escaping the escapes thusly:

//build the LOAD DATA File command
string working = String.Format("LOAD DATA LOCAL INFILE '{0}' IGNORE ", files.FirstOrDefault().ToString()) +
                         String.Format("INTO TABLE {0} COLUMNS TERMINATED BY ',' LINES TERMINATED BY '\n'", "by_switch")+
                         String.Format(" IGNORE 1 LINES (`Switch`,`Port`,`WWPN`,@the_slot,`Port Index`,@the_time,`Interval`,`Port Send Packet Rate`,")+
                         String.Format("`Port Receive Packet Rate`,`Total Port Packet Rate`,`Port Send Data Rate`,")+        
                         String.Format("`Port Receive Data Rate`,`Total Port Data Rate`,`Port Peak Send Data Rate`,`Port Peak Receive Data Rate`,")+ 
                         String.Format("`Port Send Packet Size`,`Port Receive Packet Size`,`Overall Port Packet Size`,`Error Frame Rate`,")+
                         String.Format("`Dumped Frame Rate`,`Link Failure Rate`,`Loss of Sync Rate`,`Loss of Signal Rate`,`CRC Error Rate`,")+
                         String.Format(" `Short Frame Rate`,`Long Frame Rate`,`Encoding Disparity Error Rate`,")+         
                         String.Format("`Discarded Class3 Frame Rate`,`F-BSY Frame Rate`,`F-RJT Frame Rate`, `Port Send Bandwidth Percentage`,")+
                         String.Format("`Port Receive Bandwidth Percentage`, `Overall Port Bandwidth Percentage`,`Primitive Sequence Protocol Error Rate`,")+
                         String.Format("`Invalid Transmission Word Rate`,`Link Reset Transmitted Rate`,`Link Reset Received Rate`)")+ 
                          String.Format("SET Slot = nullif(@the_slot,''),")+ 
                          String.Format(@"Time= str_to_date(@the_time,'%m/%d/%y %h:%i %p')");


// now escape the escape character
       commandreplaced = commandreplaced.Replace(@"\", @"\\");

   // now execute the command
        MySqlCommand cmd = new MySqlCommand(commandreplaced,sqlconnect);

这篇关于将 CSV 加载到 MySQL 这是一个错误吗?- 文件未找到异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！