date format in schema.ini

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Chutney - 27 Apr 2006 22:41 GMT

I am importing a text file that has dates in the format 15MAR06. I have the
following in my schema.ini file but the dates are not imported into the date
field in the table:
DateTimeFormat = "ddmmmyy"
I have used this successfully to import other date formats (e.g. "dd/mm/yyyy")
. Does anyone know if/how I can import this format? I am importing to a
staging table so I can import the date to a text field and then use a formula
to move the date to the master table. However, I am interested to know if it
is possible to get the date to import correctly in one move.

Thanks and regards.

Reply to this Message

'69 Camaro - 28 Apr 2006 00:50 GMT

Hi, Chutney.

> Does anyone know if/how I can import this format?

Yes.

> However, I am interested to know if it
> is possible to get the date to import correctly in one move.

Format the date string so that Jet can recognize it as a date. The
following example works without a schema.ini file to guide the formatting,
so YMMV if you have a schema.ini file in the same directory:

INSERT INTO tblTextImport ( FName, LName, Address, DOB )
SELECT FName, LName, Address,
(MID(DOB, 1, 2) & " " & MID(DOB, 3, 3) & " " & MID(DOB,6, 2)) AS Temp
FROM [TEXT;DATABASE=C:\Test\].TextImport.txt;

. . . where DOB in the table is a Date/Time data type, DOB in the text file
is a text field of format ddmmmyy, C:\Test\TextImport.txt is the path and
file name of the text file, and tblTextImport is the name of the table to
import the data into.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

>I am importing a text file that has dates in the format 15MAR06. I have the
> following in my schema.ini file but the dates are not imported into the
[quoted text clipped - 11 lines]
>
> Thanks and regards.

Reply to this Message

Chutney - 28 Apr 2006 21:23 GMT

Gunny,

Thanks very much for your suggestion. I have 49 columns including numerous
date fields in the import file so I was hoping to be able to add a single
format statement at the head of the existing schema.ini.

I have, however, attempted to use your suggestion in my code and am getting a
"missing operator" error for the concatenation. Any thoughts as to why?

Regards,
Rick

>Hi, Chutney.
>
[quoted text clipped - 32 lines]
>>
>> Thanks and regards.

Reply to this Message

'69 Camaro - 29 Apr 2006 09:00 GMT

Hi, Chutney.

> I have 49 columns including numerous
> date fields in the import file so I was hoping to be able to add a single
> format statement at the head of the existing schema.ini.

Sorry. That won't work. The string formatting needs to be done in the
query.

> I have, however, attempted to use your suggestion in my code and am
> getting a
> "missing operator" error for the concatenation. Any thoughts as to why?

I pasted the SQL from a working query into the post, so I know it works.
Check to ensure that the ampersands, commas, spaces and parentheses are in
the right places in your query. If that doesn't help, then copy the
concatenation string from the post into your SQL View pane and replace DOB
with the name of one of your date fields, then test it. And watch out for
word wrap in the post.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

> Gunny,
>
[quoted text clipped - 47 lines]
>>>
>>> Thanks and regards.

Reply to this Message