I had a similar issue that I solved in several ways:
1. I made a "temporary" table for each file being imported. This works but it's a little high on the maintenance side.
2. I have access format the excel file before it brings it in so that it fits onto a standardized import table. this is the most work up front but once you get it working it's low maintenance. I have tables that store headers based on the file type, then it goes through looking for those headers on each excel file, notes the address, renames the headers to match the temp table, deletes unnecessary columns, then brings everything in. There's a lot more to it but that gives you a general idea.
I don't really have specific examples for you because it's going to be different for each project, but hopefully the line of thought might help a little.
Thanks
-Lee
--- In MS_Access_Professionals@yahoogroups.com, Shay Holmes <shaybellaholmes@...> wrote:
>
> Clarification: I know how to get the file name, but I don't know how to
> make Access create a temporary table from that file...
>
> ... either with the "field1" style field names and first row as headers, or
> with the field names matching the import headers, or something else ...
>
> ~*~*~*~*~*~*~
> Shay Bella Holmes
> ~*~*~*~*~*~*~
>
>
>
> On Wed, Apr 11, 2012 at 9:22 AM, Shay Holmes <shaybellaholmes@...>wrote:
>
> > Hi, All,
> >
> > **The question**
> >
> > How can I automate importing a previously unknown file with unknown header
> > structure into to a temporary table from which I can pull the headers?
> >
> > Assumptions
> > 1) I'm importing a single flat file of
> > a) tab-delimited text or
> > b) CSV, or
> > c) (would be great to have) Excel format (specify the single
> > worksheet to import),
> > 2) Header row is
> > a) in the first row, or
> > b) (it would be great to specify the row where headers may be
> > found, assuming data comes after headers) ...
> > 3) Without knowing what the actual headers are, BUT needing to capture them
> > 4) Assuming all imported fields will end up as Text(255)
> >
> > **The reason**
> >
> > I'm trying to normalize data that we receive in a variety of files,
> > formats, and heading structures. I don't expect that I'll get Access to do
> > even most of the work, but we have a limited number of possible data
> > point permutations, and well established logic on how those points combine
> > to what we need to report on - and if we can automate even 30% of the
> > workload, it'll be worth the time spent building this app.
> >
> > In the current process, clients send us their file(s), the receivers (who
> > are NOT data-heads) identify columns in the spreadsheets where the data
> > points fall, include "notes" from the originators, and then pass the set of
> > instructions over to data processors who go into Excel and type in a lot of
> > formulas to pull together the report-able data set.
> >
> > In the proposed process, the receivers will go into Access, and instead of
> > writing up that "names are broken into columns B and C" they will have a
> > form listing the predefined data points with drop downs to specify which of
> > the headers contain those data points.
> >
> > ~*~*~*~*~*~*~
> > Shay Bella Holmes
> > ~*~*~*~*~*~*~
> >
> >
>
>
> [Non-text portions of this message have been removed]
>
Tidak ada komentar:
Posting Komentar