MySQL: Load csv file to thesql

csvloadMySQL

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

Best Answer

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:

  1. 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 '<file>'

  2. 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

  3. 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

  1. 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

  1. 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

  1. 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.

    enter image description here

    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

Related Question