Improve my code to loop through 1000 csv files and import millions of records much quicker?

Question

I have 1000 csv files, each csv file contains 5500 rows of data and 8 columns. My code below took 5 minutes to import 1 csv file into MySQL database. I know there are many questions like this out there, I've been looking into them. But, is there any suggestions you have to improve this import process, according to my code?

private void btn_fetchSharePrices_Click(object sender, EventArgs e)
{
    string[] fileCSV = Directory.GetFiles(sourceDirCSV);
    foreach (string csv in fileCSV)
    {
        try
        {
            string[] lines = File.ReadAllLines(csv);
            foreach (var line in lines)
            {
                var data = line.Split(new[] { ',' }, 8);
                DateTime prices_date = DateTime.Parse(data[0].Trim());
                DateTime prices_time = DateTime.Parse(data[1].Trim());
                string open = data[2].Trim();
                string high = data[3].Trim();
                string low = data[4].Trim();
                string close = data[5].Trim();
                int volume = int.Parse(data[6].Trim());
                int tickers_ticker_id = int.Parse(data[7].Trim());
                StoreRecord_FetchSharePrices(prices_date, prices_time, open, high, low, close, volume, tickers_ticker_id);
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }  
    }
}

private void StoreRecord_FetchSharePrices(DateTime prices_date, DateTime prices_time, string open, string high, string low, string close, int volume, int tickers_ticker_id)
{
    using (var connection = new MySqlConnection(strProvider))
    using (var command = connection.CreateCommand())
    {
        connection.Open();
        command.CommandText = @"INSERT IGNORE INTO prices (Prices_Date, Prices_Time, Prices_Open, Prices_High, Prices_Low, Prices_Close, Prices_Volume, Tickers_Ticker_ID) VALUES (@Prices_Date, @Prices_Time, @Prices_Open, @Prices_High, @Prices_Low, @Prices_Close, @Prices_Volume, @Tickers_Ticker_ID)";
        command.Parameters.AddWithValue("@Prices_Date", prices_date);
        command.Parameters.AddWithValue("@Prices_Time", prices_time);
        command.Parameters.AddWithValue("@Prices_Open", open);
        command.Parameters.AddWithValue("@Prices_High", high);
        command.Parameters.AddWithValue("@Prices_Low", low);
        command.Parameters.AddWithValue("@Prices_Close", close);
        command.Parameters.AddWithValue("@Prices_Volume", volume);
        command.Parameters.AddWithValue("@Tickers_Ticker_ID", tickers_ticker_id);
        command.ExecuteNonQuery();
    }
}

If you are connecting to the same database for every file, you need to open the connection once , create a prepared statement once,and use that prepared statement. You are currently opening the connection and a new command for every line in the spreadsheets. That is going to kill performance. — OldProgrammer, Jun 16 '14 at 18:09
Should be posted on http://codereview.stackexchange.com/ as the post is about reviewing/improving correctly working code. — Alexei Levenkov, Jun 16 '14 at 18:12

Typist · Accepted Answer · 2014-06-16T18:33:11.207

4

Quicker? Do it in MySQL

load data local infile 'file.csv' into table table_name
 fields terminated by ','
 enclosed by '"'
 lines terminated by '\n'
 (column1, column2, column3,...)

Run a foreach loop over files in a folder Directory.EnumerateFiles(Folder_path) and run above command for each file with full file path (in place of file.csv)

edited Jun 16 '14 at 18:33

answered Jun 16 '14 at 18:10

Typist

1,464
9
14

But I have 1000 files, may I know if I can loop them instead of manually loading each file? – merv Jun 16 '14 at 18:14
I'm looking into it :) – merv Jun 16 '14 at 19:42

score 2 · Answer 2 · answered Jun 16 '14 at 18:15

Use Directory.EnumerateFiles() rather than Directory.GetFiles()

See documentation:

The EnumerateFiles and GetFiles methods differ as follows: When you use EnumerateFiles, you can start enumerating the collection of names before the whole collection is returned; when you use GetFiles, you must wait for the whole array of names to be returned before you can access the array. Therefore, when you are working with many files and directories, EnumerateFiles can be more efficient.

score 2 · Answer 3 · edited May 23 '17 at 12:01

2

You can try to use LOAD DATA INFILE command, see link.

I think it is the best option.

You can write a small program to launch this command for each CSV file.

edited May 23 '17 at 12:01

Community

1
1

answered Jun 16 '14 at 18:22

Ivan Rodriguez

426
4
14

score -1 · Answer 4 · answered Jun 16 '14 at 18:13

-1

You can use Bulk Insert command:

BULK
INSERT prices 
FROM 'your file name'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

answered Jun 16 '14 at 18:13

Volma

1,305
9
17

OP is asking about MySQL, your command is MS SQL Server specific – Typist Jun 17 '14 at 01:56

Improve my code to loop through 1000 csv files and import millions of records much quicker?

4 Answers4