Type conversion failure in Access 2013

Question

When importing data from a text file (csv) into MS Access, I get an error "Type conversion failure" for 1 field. The field has data with date format "yyyy-mm-dd hh:nn:ss" and Access simply refuses to recognise it and places #Num! or simply blank data. The csv file is huge with 8m rows and cannot be opened in Excel to edit the date format. Facing no problems with any other fields.Anyway to avoid this error?

Badman

You can import that column as text instead so you bypass the date validator of Access, though you will lose on that the date will not be easily be used as date since it will be considered as text now. – Abomb

I need to use the date as a date, hence the issue – Badman

Answer 1

Use the Advanced... button at the field specification step of the import and try these settings:

I don't have the exact date format in the picture above, but it is just to show how to import that specific date.

Date Order should be YMD because in your dates, you have the years coming first, followed by the month and the date.

The date delimiter for your csv will be a dash -, while the time delimiter should be the default colon :. Make sure the 4 digit years checkbox is checked, and I would also check the Leading Zeros in Dates checkbox since your month and dates are in mm and dd formats respectively (i.e. they will begin with 0 if it is a single digit).

If there are problematic dates from your csv now, then this is another problem that won't be easy to tackle. You will maybe have to correct the date manually from the csv before importing it, or import the date as text and then create a new column to manipulate the text dates to date fields (and fix any problematic dates there).

Abomb

This did it. I had done all of this but had overlooked the YMD format. It works perfectly now :) – Badman

Answer 2

Nothing wrong with the date format, but some records may be empty or have invalid entries.

Or you miss at the import to specify the separators and format for the date field.

If still no luck, link the file and specify text for the field. Then create a select query that uses the linked file as source and use CDate to convert the text date to true date values.

When done, change the query to an append or create table query to import your data.

Donndonna