[AccessD] RE: AccessD Digest, Vol 14, Issue 31

Susan,

The way that I deal with this is that I COPY the file to a common name,
perhaps CountyData.XLS.  Then I link that file to the FE.  Build my queries
to suck the data out.  Run them.  Then there is just a copy of whatever the
next file name is to CountyData.XLS and run the queries over again.  You
need a means of interpreting some part of the original file name so that you
can set the "county field".  Do that 71 times and you are done.  One table
name, one set of queries, one function for pulling the county name into an
aliased query field, one routine that copies the file to a common name /
location.

Is this a manual process?  Is there a naming convention in the excel files?

If not just throw all of the excel files in a directory, copy each one out
to an archive directory, adding the yyyymmdd string to the name, either at
the beginning or the end (helps sort the files in date order).  Also do the
copy to a common location / name.  Now write an iterator that finds every
file in the directory and copies it to archive / common name - location,
deleting the file in the original directory.  When no files remain, you are
done.  In order to do this you must have some identifying string in the file
name however, perhaps the county name as the first N characters.  That
allows the code to build the "missing" field that is the county in the
destination table.


John W. Colby
www.ColbyConsulting.com

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Klos, Susan
Sent: Monday, April 19, 2004 3:40 PM
To: 'accessd at databaseadvisors.com'
Subject: [AccessD] RE: AccessD Digest, Vol 14, Issue 31


Robert, I need 71 identical tables because I am getting the same structure
Excel file from 71 districts.  The structure is the same but the data is
different.  I want to be able to loop through the tables and append their
records to one table.

Susan Klos
Senior Database Analyst
Evaluation and Reporting
Florida Department of Education