create primary key from filename of .txt file

[lahmcc]

Hi,

I’m a novice with Access (2003), and right now I need some help.

I receive a fixed-length text file from the state on a daily basis that
needs to be imported into an access table. This table needs to have the date
it was received from the state as the primary key. However, the state, being
the state (Alabama), does not include this as part of the record of the file.
However, the needed date is part of the file name, as follows:

4194400808040300.txt

Or

NSA8227051080804.txt

The 080804 being YYMMDD format.

I have created a crude macro that will import what I need to build my table,
with the exception being that primary key, whether it is the entire filename
or just the YYMMDD portion of the file name.

Is there a way I populate the primary key with as user prompt incorporated
in my macro?

At this point, any help would be appreciated.

Thanks!

 

[Klatuu]

You will not be able to do what you want completely in a macro. Decodeing
the date part of the file name you are importing.

But, there are some issues here.
First, you are using the term primary key incorrectly. A primary key is a
table's main index. It can be one or more fields and each field or
combination of fields must be unique within the table.

Also, it appears the filenames may not be consistently formatted, so knowing
where in the string the date is may be just about impossible.

Can you give some more details on what you are doing currently and what it
is you want to do.

 

[pietlinden]

You will not be able to do what you want completely in a macro.  Decodeing
the date part of the file name you are importing.

But, there are some issues here.
First, you are using the term primary key incorrectly.  A primary key is a
table's main index.  It can be one or more fields and each field or
combination of fields must be unique within the table.

Also, it appears the filenames may not be consistently formatted, so knowing
where in the string the date is may be just about impossible.

Can you give some more details on what you are doing currently and what it
is you want to do.

As Dave pointed out, this might be tricky. What are the rules for the
filename. You have two patterns here, so far. the file name can begin
with a letter or a number. Is there any rhyme or reason for that?
Does it mean anything? If so, you could use
IsNumeric(Left(strFieldName,1)) and use two different import specs or
whatever to import the file... kind of depends on what your rules are,
though.

 

[lahmcc]

First of all, thanks for the info.

To make things a bit clearer, here goes.

The Sales Tax dept receives two text files daily from the state, a Payment
file, and a Returns file, of customers who have paid their sales taxes via
the internet.

The files are formatted as follows (only the date portion of the filename
will be different each day):

4194400808040300.txt (Payment file)

or

NSA8227051080804.txt (Returns file)

So, actually I have a macro that populates the ‘Payment’ table, and another
that populates the ‘Returns’ table.

The way the data is laid out, there is no way for the Sales Tax people to
determine when a record was received, since the date is only shown in the TXT
filename of each file itself.

I hope this better explain the problem, and my needs. Is there a way to
capture that filename, and use it as either an alternate key (sorry about the
‘primary’ thing), or just a regular field.

If I could bring in the entire filename, it would be great.

Thanks again.

 

[Larry Linson]

Could you clarify "Table needs to have the date it was received from the
state as the Primary Key"?

A Primary Key is a Field (or combination of Fields) in each Record, the
contents of which must be indexed, and which must be unique for each Record.
Thus, unless you mean you need to rename a Field to be named with the
date -- which seems ludicrous -- "the date it was received" cannot be the
Primary Key of "the Table".

This "requirement" sounds suspiciously to me to be your approach to
implementing a solution you have thought about, not a "business" or "legal"
requirement. If you would explain, in simple business terms what you have
and what you are trying to accomplish, it's possible that someone here could
offer you a useful suggestion on how to accomplish it.

Larry Linson
Microsoft Office Access MVP

 

[lahmcc]

First of all, thanks for the info.

To make things a bit clearer, here goes.

The Sales Tax dept receives two text files daily from the state, a Payment
file, and a Returns file, of customers who have paid their sales taxes via
the internet.

The files are formatted as follows (only the date portion of the filename
will be different each day):

4194400808040300.txt (Payment file)

or

NSA8227051080804.txt (Returns file)

So, actually I have a macro that populates the ‘Payment’ table, and another
that populates the ‘Returns’ table.

The way the data is laid out, there is no way for the Sales Tax people to
determine when a record was received, since the date is only shown in the TXT
filename of each file itself.

I hope this better explain the problem, and my needs. Is there a way to
capture that filename, and use it as either an alternate key (sorry about the
‘primary’ thing), or just a regular field.

If I could bring in the entire filename, it would be great.

Thanks again.

 

[lahmcc]

First of all, thanks for the info.

To make things a bit clearer, here goes.

The Sales Tax dept receives two text files daily from the state, a Payment
file, and a Returns file, of customers who have paid their sales taxes via
the internet.

The files are formatted as follows (only the date portion of the filename
will be different each day):

4194400808040300.txt (Payment file)

or

NSA8227051080804.txt (Returns file)

So, actually I have a macro that populates the ‘Payment’ table, and another
that populates the ‘Returns’ table.

The way the data is laid out, there is no way for the Sales Tax people to
determine when a record was received, since the date is only shown in the TXT
filename of each file itself.

I hope this better explain the problem, and my needs. Is there a way to
capture that filename, and use it as either an alternate key (sorry about the
‘primary’ thing), or just a regular field.

If I could bring in the entire filename, it would be great.

Thanks again.

 

[Larry Linson]

First of all, thanks for the info.

To make things a bit clearer, here goes.

The Sales Tax dept receives two text files daily from the state, a Payment
file, and a Returns file, of customers who have paid their sales taxes via
the internet.

The files are formatted as follows (only the date portion of the filename
will be different each day):

4194400808040300.txt (Payment file)

or

NSA8227051080804.txt (Returns file)

So, actually I have a macro that populates the 'Payment' table, and another
that populates the 'Returns' table.

The way the data is laid out, there is no way for the Sales Tax people to
determine when a record was received, since the date is only shown in the TXT
filename of each file itself.

I hope this better explain the problem, and my needs. Is there a way to
capture that filename, and use it as either an alternate key (sorry about the
'primary' thing), or just a regular field.

If I could bring in the entire filename, it would be great.

You may be able to do what you want with macros if you are _expert_ in their
use, but most accomplished developers do not use macros because up until
Access 2007, they provided no error-handling capabilityf.
Thus, interestingly, experienced developers are often not a good source of
information about macros.

Klaatu may well be correct, that you just can't do what you want with
macros, but, in any case, I would do it with VBA code. There are some
alternate ways, but one that is simple and that I would consider first is:

1. Use the Windows Common Dialog, see
http://www.mvps.org/access/api/api0001.htm, to retrieve the file name.
2. Use the InStr function to extract the date information from the file
name.
3. Import the file into a temporary Table.
4. Add a new Field with the DateReceived information (either in text or
converted to date type) to each Record in the temporary Table.
5. Execute an Append Query to add the data (including the dates) to the
actual Table.
6. Delete the temporary table.

Someone who is very well-versed in macros might be able to accomplish this
with macros, but a competent VBA programmer can do so in VBA code. Someone
may post such code, but it seems to me that it's more than the few lines for
which newsgroup responses are suitable.

If I were going to do this, I'd use MVP Tony Toews' technique to create a
temporary database, in which I'd create the temporary table, then when I was
done, I'd delete the link, delete the temporary datbase, and avoid the
problems of bloating that can attend using temporary tables in your database
over and over.

Larry Linson
Microsoft Office Access MVP