CODEWITHSUNDEEP
mysqlsqlcsv
Will Beauchamp
Will Beauchamp

Reputation: 579

Load CSV into MySQL Workbench table

Here is a snippet of the raw csv (from Quandl):

Date,Open,High,Low,Close,Volume_BTC,Volume_Dollar,Weighted_Price
13/02/2014,650.04,660,645.07,645.24,4027.229102,2628148.177,652.5946528
12/02/2014,677,685.19,631.58,651.99,15511.78726,10224606.46,659.1507667

And here is the SQL code I am trying, of which I have tried many permutation :

load data local infile '/../BITSTAMPUSD.csv' 
into table test.BTC  
CHARACTER SET utf8
FIELDS TERMINATED BY ','
enclosed by ""
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

Here is the error I have been getting:

1 row(s) affected, 8 warning(s): 1265 Data truncated for column 'Date' at row 1 1265 Data truncated for column 'Open' at row 1 1265 Data truncated for column 'High' at row 1 1265 Data truncated for column 'Low' at row 1 1265 Data truncated for column 'Close' at row 1 1265 Data truncated for column 'Volume_BTC' at row 1 1265 Data truncated for column 'Volume_Dollar' at row 1 1265 Data truncated for column 'Weighted_Price' at row 1 Records: 1  Deleted: 0  Skipped: 0  Warnings: 8

And here is the code I used to generate the table to being with:

CREATE TABLE BTC(
    Date DATE,  
    Open FLOAT,
    High FLOAT,
    Low FLOAT,
    Close FLOAT,    
    Volume_BTC FLOAT,
    Volume_Dollar FLOAT,
    Weighted_Price FLOAT
)

I have been hitting my head against this problem for a while now so any help is much appreciated.

UPDATE: I have tried some of the suggestions out and still no fix. I have a suspicion it is related to lines terminated. I am using Mac is that makes a difference. Is '/n' correct?

Upvotes: 1

Views: 1505

Answers (2)

Will Beauchamp
Will Beauchamp

Reputation: 579

Changing LINES TERMINATED BY '\n' to LINES TERMINATED BY '\r' fixed my problem!

Use '\n' for windows and '\r' for mac.

Upvotes: 1

Ohlin
Ohlin

Reputation: 4178

You can use str_to_date to convert a field when importing it, as in this example where the two last lines are added to your code:

load data local infile '/../BITSTAMPUSD.csv' 
into table BTC  
CHARACTER SET utf8
FIELDS TERMINATED BY ','
enclosed by ""
LINES TERMINATED BY '\r'
IGNORE 1 LINES
(@date,Open,High,Low,Close,Volume_BTC,Volume_Dollar,Weighted_Price)
SET `Date` = DATE_FORMAT(STR_TO_DATE(@date, '%d/%m/%Y'), '%Y/%m/%d');

For Mac I also think you need to use '\r' to terminate lines.

I did try to create your table and to import a file holding your data and it works just fine with the "load data" query above. So the line termination might be the only thing missing...

Upvotes: 1

Related Questions