Date Issues...Suggestions?

[Robert Morley]

Okay, so here we go again...the infamous issue with dates.

I have a text file FTP'd to me every day. I have very little control over
its contents, so please don't suggest that I "simply use an unambiguous
format"...I can probably request that for next year, but meanwhile, I need
to work with what I have.

In the file (tab-delimited, no quotes, field names on first row), I have a
date field. Up until now, it has been interpreted correctly as such by the
Microsoft Text Driver because there were enough dates that it could figure
out which part was the day, month, and year. Now that our year is winding
down, however, it appears it's having problems. Today, for example, I got
only two dates, in MM/DD/YY (US standard) format:

12/05/05
12/01/05

The Microsoft Text Driver, which has been quite intelligent up to now about
figuring out which part was which from the non-ambiguous dates and applying
it retroactively to all (thus leaving me completely unaware of the potential
problem) is now confused, and interpreting the above dates as YY/MM/DD
(Canadian standard).

Does anybody know how to force it to re-interpret these dates? There are
about 150 fields in the file, so I don't want to specify field information
for each one...any other suggestions? For now, I'll try changing my system
settings to US standard and see if that works, but that's hardly the ideal
solution here.



Thanks,
Rob

 

[Robert Morley]

Okay, I think I've found the way out of this, by specifying the
DateTimeFormat in the schema.ini file. Does anybody have any documentation
on this...the MSDN docs that I found were a little..."sparse".



Thanks again,
Rob

 

[Pat Hartman\(MVP\)]

I think the reference to the .ini file is a left over from A2.0. I have
never used an .ini file but I have seen this reference.

You need to create an import spec for the text file. To do this, you need
to run the import wizard ONCE. Once the wizard gets started, you'll see an
Advanced button. Press it to get to the place where you can specify the
date format. You also need to save the spec. After the spec has been
saved, you can refer to it from the TransferText Method/Action.

 

[Robert Morley]

Thanks for the suggestion, but I'm not using TransferText, I'm opening the
file directly as an ADO recordset, so as far as I know, the only way to go
is the schema.ini file. The code also needs to be completely independent of
any Access-specific features, as it will likely be going into a VB (not VBA)
module at a later date.



Thanks,
Rob

 

[Pat Hartman\(MVP\)]

The only reference I find to the schema.ini file is in the TransferText
Method/Action. I'm certain this is an old reference since the import/export
specs are stored in hidden system tables in A97 and newer. As far as I can
tell, the schema.ini has nothing to do with ADO and I have never seen it
used with versions of Access newer than 2.0.

 

[John Nurick]

PMFJI, but as far as I know ADO works with text files using the Jet text
ISAM allee samee DAO and TransferText and schema.ini applies in exactly
the same way. Certainly this article implies as much:
http://support.microsoft.com/kb/205439/EN-US/.

As for Access versions, I've used schema.ini in Access 97, 2002 and 2003
with no problems except finding and understanding the documentation<g>.

Perhaps the most convenient documentation is in the Access help topic
"Initializing the Text Data Source Driver"; you have to scroll down
quite a long way to get to the schema.ini stuff. See also "How to Use
Schema.ini for Accessing Text Data" at
http://support.microsoft.com/default.aspx?scid=kb;EN-US;149090

 

[Robert Morley]

As John Nurick points out, the Microsoft Text Driver is in fact a Jet ISAM
that ADO is simply calling on, and it is that driver that's expecting to
find schema.ini. (I'm not sure where in the process MSysIMEXSpecs is
checked, but I have the impression it's at a higher level than that
driver...and as I said, if this gets ported to VB later on, that won't be
available in any event.)

There are, in fact, several articles that tell you about schema.ini to
varying degrees, but the documentation has obviously never been reviewed for
completeness of content, because it's just a wee bit sparse/incomplete in
most cases. Even the "more complete" ones often lack details, or neglect to
mention certain keywords that you can use, etc. Among the incomplete
documentation is the specific format of the date specifier, which is what I
was after specifically in this case.

In case anybody else is looking for any schema.ini articles (besides the bit
they give you in the help file), here are some of the ones I've found:

http://support.microsoft.com/default.aspx?scid=kb;en-us;210001
http://support.microsoft.com/default.aspx?scid=kb;en-us;210073
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp

The last one is the most detailed, but all it says in terms of date/time
format is that it supports everything that Jet does (with no link to the Jet
docs, of course) except AM/PM (which then leaves me wondering just what
you're supposed to do if you've got AM/PM times in your file for some
reason).

While we're on the topic, there seems to be an undocumented bug in the text
driver as well...

While MaxScanRows=0 SHOULD scan the whole file, it doesn't seem to in many
circumstances. In most cases where I've set MaxScanRows=0, I get all text
data types.

To prove it, try MaxScanRows=0 and MaxScanRows=999999 on a text file with a
few hundred records of varying types. You will get different data types for
some fields with the two different options, where you would expect them to
be the same in both instances, since the whole file should have been scanned
either way.



Rob

 

[Pat Hartman\(MVP\)]

Here's two that have more detail on the date format.
http://msdn.microsoft.com/library/d...dbcjettextdefining_text_format.asp?frame=true
http://msdn.microsoft.com/library/d...bc/htm/odbcjettext_file_format.asp?frame=true
It doesn't look like the schema.ini supports the type of formats you get
from legacy systems but you can still try.

I still haven't been able to get these articles by searching the msdn
library for them. I only found them ONLY because I checked a few articles
above and below the one that Robert pointed to.

 

[John Nurick]

If you have file with a date format that Jet can't read, import it as
text and then write an update query to parse it and move the data into a
date/time field.

 

[John Nurick]

Afaik MSysIMEXSpecs only comes into it when the TransferText routine is
being used, i.e. in the import/export wizard, the macro, or the DoCmd
method.

 

[Robert Morley]

Thanks for all the help, everyone. I've got the import working one way or
the other for the time being. Next year, I'm going to have to get these
people working on consistent date formats...I think there's 3 different
formats in the same file! Thankfully, this isn't really an issue, since I
only use two of them, and one's in a non-ambiguous format (yyyy-mmm-dd).



Thanks again,
Rob