Search code examples
mysqlsqlcsvload-data-infile

date field values are wrong while importing csv into mysql

I am importing csv file into mysql using load data. My load data command is as mentioned below.

load data local infile 'D:/mydata.csv' into table mydb.mydata 
fields terminated by ','
enclosed by '"'
lines terminated by '\r\n'
ignore 1 lines
(SrNo,SourceFrom,@var_updated,Title,First_Name,Middle_Name,Last_Name,Designation,Company_Name,@var_dob,Office_Mobile_No)  

set updated = str_to_date(@var_updated,'%Y-%m-%d'), dob = str_to_date(@var_dob, '%Y-%m-%d');

I am getting different values in my "Updated" and "DOB" columns. Such values are different in my .csv file.

enter image description here enter image description here

First image is from mysql workbench while another is of csv.

Also, I sat "office_mobile_no" column's format to 'number' in csv. But its showing number like this. enter image description here

When I double click on it, then only it shows the real number like 9875461234. It imports the same in mysql too. How do I get original number in a specific column? Also why my imported date values are differ from csv's date columns?

Solution

  • A couple of points that I can see:

    1. It looks from your screenshot like the data in your CSV file for "updated" is in d-m-Y format, but you're telling the import to look for Y-m-d. I think you need to change

    set updated = str_to_date(@var_updated,'%Y-%m-%d')

    to

    set updated = str_to_date(@var_updated,'%d-%m-%Y')

    And the same for DOB field as well, assuming your CSV has that in the same format.

    1. You said I sat "office_mobile_no" column's format to 'number' in csv. CSV is a text file format, it doesn't store any information about how to display data. What you're seeing is just how Excel decides to display large numbers by default. You can change that, but your changes won't be saved when you save it to CSV, because the CSV file format doesn't include that sort of information. Try opening the file in Notepad++ and seeing the real format of the file.