Been trying to import a file from excel to MySql but I keep running into an error about the date column. Some dates in the column are written; 4/12/2016 while others are written 04/13/2016.
I have tried my best to format it but it’s not working maybe I am missing something I don’t know of.
After importing and I was modifying the columns, when I got to the date column I actually put in ‘datetime’ as the data type but it keeps saying ‘error about the string data’ but when I changed it to ‘nvarchar’ it worked.
I have my doubts
>Solution :
It seems that you are having trouble importing a file from Excel to MySQL, specifically with the date column. You mentioned that some dates in the column are written in the format "4/12/2016" while others are in the format "04/13/2016". You have tried to format the dates, but you keep encountering an error.
When modifying the columns in MySQL, you tried to set the data type of the date column to "datetime", but it returned an error related to string data. However, when you changed the data type to "nvarchar", it worked.
It’s understandable that you have doubts about this solution. Changing the data type to "nvarchar" may work, but it is not the correct data type for storing dates. It’s important to use the appropriate data type to avoid issues with data integrity and accuracy.
One possible solution is to use the STR_TO_DATE function in MySQL to convert the date strings to the correct date format before inserting them into the database. For example, you could use the following query:
LOAD DATA INFILE 'file.csv'
INTO TABLE mytable
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(column1, column2, @datecolumn)
SET datecolumn = STR_TO_DATE(@datecolumn, '%m/%d/%Y')
This query assumes that the date column is the third column in the CSV file and is named "datecolumn" in the MySQL table. The STR_TO_DATE function converts the date string to the format "YYYY-MM-DD", which is the standard MySQL date format.
I hope this helps! Let me know if you have any further questions or concerns.