donpick
asked on
Import csv file to MySql table
Running MySql on a ML350 G5 HP server. The operating system is Small Business Server 2008
I am trying to import a csv file into a table. I am a MySql novice.
The table structure is attached. See file tblpivottxt structure.pdf
The contents of the csv file are below:
Ticker,Date/Time,Name,High
RUT-X,10/26/2015,Russell 2000,1165.77,1157.52,1159.
SP-500,10/26/2015,Standard
I used the commands below to try to import the data.
Mysql> LOAD DATA INFILE 'd:/investing – Programming/MySql/TxtData/
> INTO TABLE pivot.tblpivottxt
> FIELDS TERMINATED BY ','
> LINES TERMINATED BY '\n'
> IGNORE 1 ROWS;
Error 1292 <22007> Incorrect data value: '10/26/2015' for column 'DateEntered' at row 1
As you can see there is an error.
In the table tblpivottxt, the DateEntered field is defined as date
The date in the csv file is in mm/dd/yyyy format. My guess is the date format must be changed.
If I am correct, what commands would you use to change the date so it will successfully import?
Remember, I am not an expert so please provide a detailed answer.
May be you see other problems? If so, please suggest solutions.
Thank you.
tblpivottxt-structure.pdf
I am trying to import a csv file into a table. I am a MySql novice.
The table structure is attached. See file tblpivottxt structure.pdf
The contents of the csv file are below:
Ticker,Date/Time,Name,High
RUT-X,10/26/2015,Russell 2000,1165.77,1157.52,1159.
SP-500,10/26/2015,Standard
I used the commands below to try to import the data.
Mysql> LOAD DATA INFILE 'd:/investing – Programming/MySql/TxtData/
> INTO TABLE pivot.tblpivottxt
> FIELDS TERMINATED BY ','
> LINES TERMINATED BY '\n'
> IGNORE 1 ROWS;
Error 1292 <22007> Incorrect data value: '10/26/2015' for column 'DateEntered' at row 1
As you can see there is an error.
In the table tblpivottxt, the DateEntered field is defined as date
The date in the csv file is in mm/dd/yyyy format. My guess is the date format must be changed.
If I am correct, what commands would you use to change the date so it will successfully import?
Remember, I am not an expert so please provide a detailed answer.
May be you see other problems? If so, please suggest solutions.
Thank you.
tblpivottxt-structure.pdf
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you, Arnold, this is a useful link.
Thank you, theGhost_k8 Very useful information, just what I needed.
Thank you all for your prompt posts. I pay for this site and I appreciate prompt correct answers.
Thank you, theGhost_k8 Very useful information, just what I needed.
Thank you all for your prompt posts. I pay for this site and I appreciate prompt correct answers.
In your case you're right you need to correct the date format using: STR_TO_DATE.
You command should look something like:
LOAD DATA INFILE 'd:/investing – Programming/MySql/TxtData/
> INTO TABLE pivot.tblpivottxt
> FIELDS TERMINATED BY ','
> LINES TERMINATED BY '\n'
> IGNORE 1 ROWS (col1, col2,..... , @var_dateentered, ..., colN)
set dateentered=STR_TO_DATE(@v