CODEWITHSUNDEEP
mysqlcsvdate-formatdate-formatting
Rajeev R
Rajeev R

Reputation: 123

Format a csv date column using mysql load data in file

I am using Load data in file query to insert csv into table. I have to format a date column inside the csv, 

LOAD DATA INFILE '/invoices/invoice1381301986.csv' INTO TABLE invoice_table FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' (`code`,@var1)  set datefield=STR_TO_DATE(@var1, '%m/%d/%Y');

I can format the date using the above query.

But the problem is, I have different formats for the csv date column. Possible formats are, "m/d/Y","m-d-Y", "m/d/y", "m-d-y", "Y-m-d", "Y/m/d".

so my query should be according to date format from the csv, so that I can modify my queries like,

datefield=STR_TO_DATE(@var1, '%m/%d/%Y')

OR

datefield=STR_TO_DATE(@var1, '%m-%d-%Y')..

How can I read the in which format the csv date field is?

Upvotes: 2

Views: 3325

Answers (1)

Stephan
Stephan

Reputation: 8090

you need to use a CASE : 

LOAD DATA INFILE '/invoices/invoice1381301986.csv' 
    INTO TABLE invoice_table 
    FIELDS TERMINATED BY ',' 
    ENCLOSED BY '"' 
    LINES TERMINATED BY '\n' 
    (`code`,@var1)  
    SET datefield= (
        CASE
            WHEN @var1 REGEXP '[0-9]{2}/[0-9]{2}/[0-9]{4}' THEN STR_TO_DATE(@var1,'%m/%d/%Y')
            ...
        END
    )

Upvotes: 3

Related Questions