I am trying to import data from .csv and missing the ability to import a field formatted as a simple date.
I was looking for Microsoft Access assistance for an issue I'm having with importing date data from a .csv file. I'd like to import as Date, Short Date. The only option I'm seeing is Date with Time, which does not work when importing my date data. Using the date with time option actually deletes all of the dates that I'm trying to import. If I select text, I need to go in and change all date fields after each import to be date, short date formatted. Help please! Am I missing something? Can this be fixed, or will I need to first change my file to Excel in order to import the date correctly?
Gerrit Viehmann commented
Perhaps an "Import/Export Specification" (which can be used with DoCmd.TransferText and with the Import Assistant) is what you need.
Import/Export Specifications are stored in the system tables MSysIMEXSpecs and MSysIMEXColumns. These tables are somewhat obscure, so maybe use the Import Assistant to create one (External Data | Import text file).
In theory all field types from DAO.DatabaseTypeEnum can be imported (dbDate (MSysIMEXColumns.DataType = 8) really means date and time).
You can easily import date/time fields like these below (mix formats with reason):
01-AUG-18 (which evaluates with MSysIMEXSpecs.DateOrder = 5 to: day 18, month 08, year 2001, time 00:00)
12h time strings are difficult (some systems won't read them at all):
10/25/18 7:00 PM
10/25/18 7:00 AM
ISO-Timestamps only work partially. You can change the date delimiter and the time delimiter but not the date/time delimiter. So:
2018-10-25 17:01:09.910 can be imported.
2018-10-25T17:01:00.000 cannot be imported (except as text).