I'm having problems when loading a csv file into MySQL 5.7 with MySQL Workbench.
Example Data
ESH13 1/2/2013 10:04:35 1451 1
ESH13 1/2/2013 10:04:35 1451.25 2
ESH13 1/2/2013 10:04:35 1451 1
ESH13 1/2/2013 10:04:35 1451 1
ESH13 1/2/2013 10:04:36 1451 1
ESH13 1/2/2013 10:04:36 1451 2
Originally I tried using this [below] method to start:
SHOW VARIABLES LIKE "secure_file_priv";
CREATE TABLE es (
id INT NOT NULL AUTO_INCREMENT,
ticker VARCHAR(255) NOT NULL,
date DATE NOT NULL,
time DECIMAL(10 , 2 ) NULL,
price DECIMAL(10 , 2 ) NULL,
vol DECIMAL(10 , 2 ) NULL,
PRIMARY KEY (id)
);
LOAD DATA INFILE 'C:\Users\YCM LLC\Desktop\HFT\ES_test.csv'
INTO TABLE es_test1
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';
This error occurs when the above is run:
Error Code: 1290. The MySQL server is running with the --secure-file-priv option so it cannot execute this statement.
I tried to modify C:\ProgramData\MySQL\MySQL Server 5.7\my.ini
by blocking the statement as shown below, but I get an "Access is denied" when I try to save the changes.
# Secure File Priv.
# secure-file-priv="C:/ProgramData/MySQL/MySQL Server 5.7/Uploads"
I then tried to use of "local" keyword in the LOAD DATA INFILE statement:
LOAD DATA local INFILE 'C:\Users\YCM LLC\Desktop\HFT\ES_test.csv'
INTO TABLE es_test1
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';
This error occurs when the above is run:
Error Code: 2. File 'C:UsersYCM LLCDesktopHFTES_test.csv' not found (Errcode: 2 - No such file or directory)
I was able to get data to load after getting adjusting per the advice from [a] the person that answered my question in a comment, but I started having trouble loading time string data to a table
CREATE TABLE es (
ticker VARCHAR(255) NOT NULL,
xdate DATE NOT NULL,
time Time NULL,
price DECIMAL(10 , 2 ) NULL,
vol DECIMAL(10 , 2 ) NULL
);
LOAD DATA local INFILE 'C:\\Users\\YCM LLC\\Desktop\\HFT\\ES_test.csv'
INTO TABLE es
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(ticker, @var1, @var2, price, vol)
SET xdate = STR_TO_DATE(@var1, '%m/%d/%Y'),
time = STR_TO_DATE(@var2, '%hh:%mm:%ss');
This error occurs when the above is run:
6 row(s) affected, 6 warning(s): 1411 Incorrect datetime value: '10:04:35' for function str_to_date 1411 Incorrect datetime value: '10:04:35' for function str_to_date 1411 Incorrect datetime value: '10:04:35' for function str_to_date 1411 Incorrect datetime value: '10:04:35' for function