MySQL: Load csv file to mysql 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
Issues when Loading Data with MySQL LOAD DATA INFILE

You appear to have a few issues going on here and to resolve you can make a few adjustments to get your data to load without error. 

I've source referenced and quoted the items in more detail below so you can read up on each for a more thorough explanation.

In short though essentially you can:


Use the LOCAL keyword with the LOAD DATA INFILE statement to get the data to load with MySQL without needing to adjust the my.ini file parameters


LOAD DATA LOCAL INFILE '&lt;file&gt;'

Use double backslashes [\\] in the folder path to separate folders rather than just one to escape the first backslash since by default a single backslash is used as a special escape character and ignored when used alone


C:\\Users\\YCM LLC\\Desktop\\HFT\\ES_test.csv

With the STR_TO_DATE function for the second argument you pass it telling it the format for the time value in the first argument, you should use the %h:%i:%s or %H:%i:%s format 


time = STR_TO_DATE(@var2, '%h:%i:%s');





 Further References



  The LOCAL keyword affects expected location of the file and error
  handling, as described later. LOCAL works only if your server and
  your client both have been configured to permit it. For example, if
  mysqld was
  started with the local_infile system variable disabled, LOCAL
  does not work. See Section 6.1.6, “Security Issues with LOAD DATA
  LOCAL”.
  
  The LOCAL keyword affects where the file is expected to be found:
  
  
  If LOCAL is specified, the file is read by the client program on the client host and sent to the server. The file can be given as a
  full path name to specify its exact location. If given as a relative
  path name, the name is interpreted relative to the directory in which
  the client program was started.
  
  When using LOCAL with LOAD DATA, a copy of the file is
  created in the server's temporary directory. This is not the directory
  determined by the value of tmpdir or slave_load_tmpdir,
  but rather the operating system's temporary directory, and is not
  configurable in the MySQL Server. (Typically the system temporary
  directory is /tmp on Linux systems and C:\WINDOWS\TEMP on
  Windows.) Lack of sufficient space for the copy in this directory can
  cause the LOAD DATA LOCAL statement to fail.
  
  
  source







  Within a string, certain sequences have special meaning unless the
  NO_BACKSLASH_ESCAPES SQL mode is enabled. Each of these sequences begins with a backslash (\), known as the escape
  character. MySQL recognizes the escape sequences shown in Table 9.1,
  “Special Character Escape
  Sequences”.
  For all other escape sequences, backslash is ignored. That is, the
  escaped character is interpreted as if it was not escaped. 
  
  
    Table 9.1 Special Character Escape Sequences
    
    Escape Sequence: \\
    
    Character Represented by Sequence: A backslash (\) character
  
  
  source







  Description
  
  The MySQL STR_TO_DATE function takes a string and returns a date
  specified by a format mask.
  
  Syntax
  
  The syntax for the STR_TO_DATE function in MySQL is:
  
  
    STR_TO_DATE( string, format_mask )
  
  
  Parameters or Arguments
  
  string
  
  The string value to format as a date.
  
  format_mask
  
  The format to apply to string. The following is a list of options for
  the format_mask parameter. These parameters can be used in many
  combinations.
  
  
  
  Short List
  
  
    Value: %H
    
    Description: Hour (00 to 23)
    
    Value: %h
    
    Description: Hour (00 to 12)
    
    Value: %i
    
    Description: Minutes (00 to 59)
    
    Value: %s
    
    Description: Seconds (00 to 59)
  
  
  source