0

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?

Improve this question
0

1 Answer 1

Reset to default
1

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.
Improve this answer
Sign up to request clarification or add additional context in comments.

24 Comments

If you set the format of a specific column in Excel, then save as CSV, it won't save it because as I said above, CSV doesn't store format information. You'd need to change the underlying data. Excel formatting is just for display purposes, not data storage. I would suggest you import it to MySQL just as it is. If you then want to display it in a certain format somewhere else than write some code to format it when you display it to the user. This is a good programming / data storage principle in general - store the data in its raw format, and then change the display of it to suit each use case
yeah you could probably write something in SQL to reformat it, but you shouldn't have to.
once you've imported it into mysql, what software are you using to view it? Also what is the data type of the column you imported it to? Whatever viewer you are using could be formatting the data in order to display it to you. You might have to override that default.
create your column as bigint, not int. These numbers are too large for a 32-bit integer. 2147483647 is the largest value it's possible to store in a signed int field. If you try to insert a larger value, MySQL will just use that max value instead. See dev.mysql.com/doc/refman/5.7/en/integer-types.html
also, if the varchar version is showing the E+ value, it sounds like you didn't import the correct CSV file (the one with the raw numbers in). It would only do what you describe if the value in the CSV file was actually stored as the literal text "98E+11". I tried inserting 9875461234 into a varchar field in my own MySQL database and sure enough it stored it as "9875461234"
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.