Tricky file import problem using vba

[Matilda]

Hi All,

I have a very tricky import problem.

My directory contains files named according to the following convention:

7 digits, 1 alpha

where the first 4 digits represent an ID
5th digit is a filler,
last two digits represent a month of the year
alpha character identifies a file belonging to the group for that month.

The list then looks something like this:

1234901a.txt
1234901b.txt
1234901c.txt
1234902a.txt
1234902b.txt
1234903a.txt
1234903b.txt
1234903c.txt
1234903d.txt
2234901a.txt
2234901b.txt
2234902a.txt
2234902b.txt
2234902c.txt
2234902d.txt
2234902e.txt
2234903a.txt
2234903b.txt

What i need to do is
1) import each file belonging to an ID for each month.
2) after each ***month*** is complete, I need to perform some code
3) after every file is processed for each ID, I need to perform some more
code.
4) repeat from step 1 to end of directory.


I have tried importing the file list into a table, (called FileList) and
breaking down the filename into components ID, Month and assigning an index
to give the number of files for the month, but I simply cannot construct the
code that will read down the list of records in the table, putting the
elements into variables that I can using to pass an sql string.

Am I even on the right track?

 

[Jeanette Cunningham]

Matilda,
here is a suggestion with a slightly different method which I think will be
easier for you to write the code for.
I suggest you create a table which you use to import all the txt files into,
one after the other, including a field where you add the file name for each
record.
Finish all the imports in the one code routine so that you end up with all
the data from all the files in the one table, with a single field that
identifies which file each record came from.
Use queries to extract the info you need from this table and append to the
table/s that will store the real data.


Jeanette Cunningham -- Melbourne Victoria Australia

 

[Matilda]

Hi Jeannette,

Y'know I'm beginning to think this problem is bigger than Access - your
suggestion would lead to a table of a couple of mill records.

However I think it has merit - I should tackle this on a smaller scale in
that much simplified way - or lookat using another tool of analysis.

Much obliged

 

[Steve Sanford]

Hi Matilda,

I was also thinking along the lines of Jeannette. I was wondering about the
year....is there a column in the text file for the year? Or is the data only
for one year?

The number of records really doesn't matter as long as the mdb is not
greater the 2GB. That is for *each* MDB. So you could have one or more tables
from 5 (BE) MDBs , each under 2 GB, linked to a FE. Or your FE could open an
additional MDB (from code), open a recordset from that unlinked MDB, do
calculations, store the results in the linked BE and then close the unlinked
MDB.

If you don't think that 2GB is enough, you could use SQL Express. The file
size limit is 4GB. You could link the SQL table to the Access FE just like
linking an Access BE.

Then there is SQL Server.....

Not knowing the table structure, how many text files, how long does the data
need to be kept, etc., it doesn't seem that it would be too difficult
accomplish your task.

HTH

 

[Matilda]

Hi Steve,

I'm glad y'all agree I wasn't getting anywhere. I've been pondering
Jeanette's suggestion but have to say I didn't think of a back end / front
end configuration, which would be very useful. I am reluctant to use SQL
Server because I need a simple gui and Access allows for that with ease.
It's one year's records per ID. Am leaning to combining all records for one
month and processing form there, then dropping the table once relevant info
extracted and importing next batch.
I was half way through my first solution, but having totally abandonded that
i now see other possibilities. And resigned myself to another few blisters on
my b**m :-((

Many thanks for your input

 

[Nomen]

A thought I had might be to use the FileSystemObject in a loop to scan thru
the files in the folder and match them to the IDs and/or Months that you want
to import. The ID and Month would need to be pulled from a table or entered
by a user. The individual file names could be parsed to determine a match
before the actual import.