The only suggestion I can think of is if you have the values stored in a table, of the filenames and locations - and use that to loop through the files, linking them, and importing the data appropriately.
I'm assuming however;
- All files have same structure
- All files get imported in the same way.
If these assumptions are not correct, then you would need to have different loops (depending on how you want to code it) and different import specs (and queries = import procedure) for each of the different types of files. For example, if there are 15 different types of files, you would need to put in 15 loops, or a select case within the loop that has 15 cases.
A few other tips, as it is very difficult to interpret what it is people are trying to do without seeing it in front of you;
You could set up loops that increment by 1.
Using those increments, work out the month you want to import (dateadd("m","Start Date",intIncrementValue) will return the number of months inline with the increment.
A similar idea can be done with a year value as well.
This should enable you to piece together the name(s) of the files.
e.g. January_2004_file1.csv
dateadd("m",1/1/04,1) = 1/2/04 -> Format(1/2/04,"mmmm") = February
(not using american date format here!)
use those values in a string to workout the filepath.
Use the previous line of code for linking the table, make sure you call the table the same name, and then run your queries as part of the import procedure you have designed.
Then move onto the next increment for the loop...
If you're not too frustrated, hopefully that helps.
Regards,
Fraser.
"Brian" <ja******@bellsouth.net> wrote:
I needed to add a few things to my email.
I have an Access Database setup using a form with simple code to run the
Append and Update Queries
to move the data to the appropriate table. What I want to do is have the
code look at the table I setup and
read the location and date and then run.
What I am doing now is Using the Link Table Manager and change the location
of the Link File, then
click the button on the form, it runs the Append and Update Queries then
Opens the Link Table Manager
Window back up for me. This is where I want to be able to read from the
table of where my files locations
are and the date of the data run. Then once i press the button is will loop
through all 48 months of data at one
swoop. I just cant get my code right to get the data from the table into
the code. I have done this before, but
I am obviously missing something. Frustration has set in and now I just
tired of screwing with it.
DB Info:
DB Name: DW Import
Table Name: DISTRPT
Field Name: Location Example Data:
"Z:\2000\0100\distrpt.csv"
Field Name: Date Example Datra: 01/28/2000
Brian
"Brian" <ja******@bellsouth.net> wrote in message
news:UI**************@bignews2.bellsouth.net... I need to import data from 720 csv files into an Access database so I can
do some editing prior to loading into a SQL Server. These files came from
data output from a mainframe on a monthly basis. There are 15 files created
each month with each file containing specific data.
What I started to do is store the files by year and then by month so I new
when the data is based on. I have linked the table to the database and
them append the data into a table. Ok, this is ok for a file or two, but it is
really sucking for 720.
I tried to creating a table listing the location of each files and date of
production and then append the data into the appropriate table, however, I
could never get it to work. If anyone has any ideas or code samples, it
would be much appreciated.
Brian
