Mysql – Date conversion and integer truncation during import

importMySQL

I am doing some data file imports using:

LOAD DATA
INFILE 'apa.txt'
INTO TABLE Test_Stock_Daily
FIELDS TERMINATED BY ','
(symbol, date, open, high, low, close, volume);

The date in the file is formatted mm/dd/yyyy. I need it to be yyyymmdd in the database.

The volume field needs to be truncated by two places so 123456 becomes 1234.

What are the most SQL-centric ways of doing this?

Table definition

my schema

Best Answer

Here is what I settled on:

DELETE FROM Stock_Daily where symbol = 'APA';
LOAD DATA
    INFILE 'APA.TXT'
    INTO TABLE Stock_Daily
    FIELDS TERMINATED BY ','
    (@date, open, high, low, close, @volume)
    SET date = STR_TO_DATE(@date, '%m/%d/%Y'), symbol = 'APA', volume = @volume/100;

SELECT * FROM Stock_Daily WHERE symbol = 'APA' and date >= "2016-02-26";

Some sample data:

02/26/2016,39.0900,40.4800,38.4000,39.4700,5545700
02/29/2016,39.4900,39.5000,37.6900,38.2800,5330100
03/01/2016,38.4700,38.6800,36.2600,38.6200,5884600
03/02/2016,38.3300,42.0100,38.1600,41.9700,6606500
03/03/2016,41.7500,45.6000,41.7500,45.0900,10047600
03/04/2016,45.6300,48.9400,45.1600,48.0400,8779000

Some sample output:

APA 2016-02-26  39.0900 40.4800 38.4000 39.4700 55457
APA 2016-02-29  39.4900 39.5000 37.6900 38.2800 53301
APA 2016-03-01  38.4700 38.6800 36.2600 38.6200 58846
APA 2016-03-02  38.3300 42.0100 38.1600 41.9700 66065
APA 2016-03-03  41.7500 45.6000 41.7500 45.0900 100476
APA 2016-03-04  45.6300 48.9400 45.1600 48.0400 87790

It would be really nice if I could replace all those 'APA's with a variable, but I gather that I can't do that without using a stored procedure so I think that I'll wrap in up in a Python wrapper like so:

 python reloadData.py APA

Thanks to all!

Related Question