2

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

0

3 Answers 3

2

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!

0
2

I suggest that this is a bit cleaner than Vérace's answer:

LOAD DATA
    INFILE 'apa.txt'
    INTO TABLE Test_Stock_Daily
    FIELDS TERMINATED BY ','
    (symbol, @date, open, high, low, close, volume)
    SET date = STR_TO_DATE(@date, '%m/%d/%y'); 

(And yes, date is a poor choice for a column name.)

1

1st an important point:

Do not have a column called "date" - it's an SQL keyword, and it makes debugging confusing - call it Stock_date or similar.

From here (it's been a while since I've used MySQL's INFILE), I came up with the code below. If you'd care to post a few lines of real data, I can check against that. Note: the code is untested. It may require some (ahem...) massaging before functioning properly, but it should give you a good start.

LOAD DATA
INFILE 'apa.txt'
INTO TABLE Test_Stock_Daily
FIELDS TERMINATED BY ','
(symbol, date, open, high, low, close, volume)
SET Stock_date = SELECT CONCAT(SUBSTRING('12/17/2015', 7, 4), '-', SUBSTRING('12/17/2015', 1, 2), '-', SUBSTRING('12/17/2015', 4,2)), SET Volume = SELECT SUBSTRING('12345566', 1, length('12345566') - 2)

For '12/17/2015' substitute the string Stock_date (without quotes). This SQL converts the string from mm/dd/yyyy to yyyy-mm-dd

For '12345566' substitute Volume (again, without quotes)

2
  • I HATE the fact that column is named date. I wish there were a way to change it. The table was designed long ago when that did not appear to be a problem and many programs use it now. We have another table created long ago with a column named signal which is preventing up from upgrading to MySQL 5.5. (SIGNAL became a key word in 5.5.)
    – John
    Feb 25, 2016 at 22:42
  • Ah, if we could only rewrite the whole thing from scratch. We know so much more now. As for the code, very helpful, thanks. I was going to try another approach to the truncation 'INT(volume/100)' but I like your LENGTH() idea.
    – John
    Feb 26, 2016 at 17:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.