DateTimeFormat issue in schema.ini file

[Radu Stanciu]

Hi,

I am trying to import a csv file using the jet engine and everything
works really nice except for one thing. I can't get a custom date format
to be imported as TimeDate rather than a plain string. The date looks
like this:

28/03/2005 18:19:57 Europe/London

The problem is with the "Europe/London" part, because it's not
recognized by access. I'm using DateTimeFormat=dd/mm/yyyy hh:nn:ss in
the schema.ini file, and it would work ok if not for the Europe/London,
but it doesn't. Is there a way to put some extra characters in that date
format that are supposed to be ignored? More exactly, I don't need that
Europe/London text, I just need the date. If I put that text in the date
format, I get an error from access saying that is not a valid format.

How can I get that text imported into my database as a valid DateTime entry?

Thanks,

Radu Stanciu

 

[John Nurick]

Hi Radu,

One common way of handling this sort of situation is to import or link
the csv file as a temporary table, with a text column [D] for the
awkward date field. Then use an append query to move the data into your
"real" table, with a calculated field that performs the type conversion
using an expression such as

DateSerial(Mid([D],7,4), Mid([D],3,2), Left([D],2))

Alternatively, instead of using TransferText to import or link the csv,
build and execute an append query that gets its data directly from the
csv file using syntax like this:

FROM [Text;HDR=Yes;Database=C:\MyFolder\;].[MyFile#txt]

and uses a calculated field as above to fix the date value.

 

[Radu Stanciu]

Hi,

Thanks, the first one should do the trick. I am already importing
directly from a csv file, but the thing there is that I have a lot of
columns and for future compatibility I'd like to rely on SELECT * rather
than explicitly naming the columns, and therefore be able to include
some manual changes on the date field. When already in a temp table, the
data goes into different tables, so it's easier to make the changes there.

Thanks again for your help,

Radu Stanciu

Hi Radu,

One common way of handling this sort of situation is to import or link
the csv file as a temporary table, with a text column [D] for the
awkward date field. Then use an append query to move the data into your
"real" table, with a calculated field that performs the type conversion
using an expression such as

DateSerial(Mid([D],7,4), Mid([D],3,2), Left([D],2))

Alternatively, instead of using TransferText to import or link the csv,
build and execute an append query that gets its data directly from the
csv file using syntax like this:

FROM [Text;HDR=Yes;Database=C:\MyFolder\;].[MyFile#txt]

and uses a calculated field as above to fix the date value.

Hi,

I am trying to import a csv file using the jet engine and everything
works really nice except for one thing. I can't get a custom date format
to be imported as TimeDate rather than a plain string. The date looks
like this:

28/03/2005 18:19:57 Europe/London

The problem is with the "Europe/London" part, because it's not
recognized by access. I'm using DateTimeFormat=dd/mm/yyyy hh:nn:ss in
the schema.ini file, and it would work ok if not for the Europe/London,
but it doesn't. Is there a way to put some extra characters in that date
format that are supposed to be ignored? More exactly, I don't need that
Europe/London text, I just need the date. If I put that text in the date
format, I get an error from access saying that is not a valid format.

How can I get that text imported into my database as a valid DateTime entry?

Thanks,

Radu Stanciu