I have an website C# ASP.NET, for create for the *.csv file from convert *.xls file in a chosen folder.

The app responsible for making that file takes following steps:

1. Make a *.csv file
2. Import the *.csv file in an dedicate table using LOAD DATA from MySQL

But there is a problem.

The LOAD DATA not find the *.csv file on the folder

ERROR [HY000] [MySQL][ODBC 5.1 Driver][mysqld-5.1.51-community]File 'D:\inetpub\wwwroot\public\target_D8BD592B2F99_08-02-2022.csv' not found (Errcode: 2)

But the *.csv file exists on the folder

'D:\inetpub\wwwroot\public\target_D8BD592B2F99_08-02-2022.csv'

I have tried without success waiting until File.Exists

Maybe the call of DATA LOAD is too fast?

Any suggestion?

var timeout = DateTime.Now.Add(TimeSpan.FromMinutes(1));

while (!File.Exists(output))
{
    if (DateTime.Now > timeout)
    {
        Response.Write("Application timeout; app_boxed could not be created; try again");
        Environment.Exit(0);
    }
    Thread.Sleep(TimeSpan.FromSeconds(1));
    
    //LOAD DATA
}

CodePudding user response：

It looks like the real problem is how to import Excel files into MySQL. This can be done without converting the files to CSV or calling LOAD DATA explicitly.

Bulk loading data into MySql can be done using the MySqlBulkCopy class from the open source MySqlConnector.NET library.

MySqlConnector can send any data stored in a DataTable or available through an IDbDataReader interface to MySQL, using LOAD DATA underneath. This means all we need is a way to get an IDbDataReader from an Excel file.

using var excelReader = GetReaderFromExcel(somePath);

// open the connection
using var connection = new MySqlConnection("...;AllowLoadLocalInfile=True");
await connection.OpenAsync();

// bulk copy the data
var bulkCopy = new MySqlBulkCopy(connection);
bulkCopy.DestinationTableName = "some_table_name";
var result = await bulkCopy.WriteToServerAsync(dataTable);

// check for problems
if (result.Warnings.Count != 0) { /* handle potential data loss warnings */ }

The popular ExcelDataReader can read data from xlsx or the obsolete xls format and return an IDbDataReader, without having to install OLEDB drivers. Reading data from an Excel file can be done with 2 lines.

using var reader = ExcelReaderFactory.CreateReader(stream);
...

Putting it all together :

using var stream = File.Open(filePath, FileMode.Open, FileAccess.Read);
using var excelReader = ExcelReaderFactory.CreateReader(stream);

using var connection = new MySqlConnection("...;AllowLoadLocalInfile=True");
await connection.OpenAsync();

var bulkCopy = new MySqlBulkCopy(connection);
bulkCopy.DestinationTableName = "some_table_name";
var result = await bulkCopy.WriteToServerAsync(dataTable);

if (result.Warnings.Count != 0) { /* handle potential data loss warnings */ }