Importing dates from CSV problem

[Bech100]

i have a CSV file that has some dates in one of the columns

Here is a snippet of the CSV

Code:

Product Name,Generic Name,Country (s),Pack Size,Date of notification
Aerius 5mg Tablets,Desloratadine,Belgium/France,30,15/01/03
Aprovel 150mg Tablets,Irbesartan,Belgium/France/Germany/Greece/Italy/Netherlands/Portugal/Spain,28,26/02/01
Aprovel 300mg Tablets,Irbesartan,Belgium/France/Germany/Greece/Italy/Netherlands/Portugal/Spain,28,26/02/01
Aprovel 75mg Tablets,Irbesartan,France/Greece/Italy/Spain,28,26/02/01
Arava 100mg Tablets,Leflunomide,Austria/Greece,3,05/12/01
Arava 10mg Tablets,Leflunomide,Austria/Germany/Greece/Spain,30,05/12/01
Arava 20mg Tablets,Leflunomide,Austria/Greece/Spain,30,05/12/01
Avandia 2mg Tablets,Rosiglitazone,Austria/France,56,16/04/03

As you can see, the date is in the format of DD/MM/YY, but when i import the CSV file, the date has now been converted to this format YYYY-MM-DD HH:MM:SS.

The data type for the field has been set to varchar(8) before the import.

I am using SQLyog v.3.52.

Here is my column information to to confirm

Code:

Field                 Type          Null    Key     Default  Extra
——————–  ————  ——  ——  ——-  ——
product_name          varchar(50)   YES             (NULL)        
generic_name          varchar(50)   YES             (NULL)        
countrys              varchar(50)   YES             (NULL)        
pack_size             varchar(50)   YES             (NULL)        
date_of_notification  varchar(8)    YES             (NULL)        
comments              varchar(255)  YES             (NULL)        

Is it a bug, or am i doing something wrong?

Thanks

Mark

[Shadow]

Based on the file exerpt you had provided, I performed the file import, and I must say, MySql got it in the right way – the date field was preserved exactly as it was… What import did you use, ODBC or CSV and with what options?

[Bech100]

I imported using ODBC.

Where is the CSV option?

Mark

[Bech100]

Ah, just found it, it works that way, but not through ODBC!??!

Mark

[Shadow]

The problem with ODBC is that it tries to figure out the field types and change the values accordingly. Apparently ODBC recognized your “notification date” field – correctly – as date. BTW, you should consider storing that column in a date field because date type corresponds best to that field. In your app you can render the date values as you wish.

[Shadow]

Just one more thought: in ODBC text driver you may determine type of the fields. You may want to set “notofication date” to text, but I still suggest you to use date as field type!

[Bech100]

Thanks for the info.

Im not fussy that the date is seen as a date. it is not essential to my application.

Thanks again

Mark

[Author]

Posts