0

I have a need to download a CSV file from a network location and import it into a MySQL table. The absolute easiest way is to copy it to the MySQL location and use LOAD DATA. I did it manually using this command:

LOAD DATA INFILE 'Transactions.CSV' 
INTO TABLE tmpImport 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\r\n' 
IGNORE 1 ROWS 
(AccountDesignator,@PostedDate,SerialNum,Description,Amount,CrDr) 
SET PostedDate = STR_TO_DATE(@PostedDate, '%m/%d/%Y');

I had to add the @PostedDate because, without it, MySQL complained that the date was not in the correct format.

So since this is going to be a regular thing, I tried to put it into a stored procedure that could be called by my process, but using LOAD DATA in a stored procedure is not allowed.

So I figured I'd just send the raw command from my process using the MySQL Net/Connector:

        using (MySqlCommand cmd = new MySqlCommand(sql, new MySqlConnection("server=theServer; uid=theUid; pwd=thePwd; database=theDatabase; SslMode=none;")))
        {
            cmd.Connection.Open();
            rows = cmd.ExecuteNonQuery();
            cmd.Connection.Close();
        }

But when I try to run that, I get this error:

MySqlException: Parameter '@PostedDate' must be defined.

Is there any way around this? Or am I going to have to resort to reading in the CSV file and inserting the records individually?

tolsen64
  • 881
  • 1
  • 9
  • 22

1 Answers1

1

You have two options.

1) format the date in the .csv before loading your file.

2) change that column to a varchar. Load the file and then use STR_TO_DATE to change to your desired date format. Then change the column type back to DATE if it must be defined as one. I usually have no issues working with date fields as VARCHAR.

Both options are better than what you thought you may have to do at the end

Bleach
  • 561
  • 4
  • 11