Hi Shay,
I would be interested in other people's answers to this question too.
Not the most elegant solution, but if you want to create a new table each time you import, you could import into a table that doesn't exist, and a table fitting the file structure will be created. So for a spreadsheet you could do something like this:
db.TableDefs.Delete "TempTbl" 'get rid of previous import table
DoCmd.TransferSpreadsheet acImport, , "TempTbl", "FileName", True
Or maybe it would make sense for your needs to import into a temp table with column names "Field1", "Field2", etc., and then get the real column names from the first row and put those in a sort of lookup table where you map, for example, "Field1" to "Employee Name", etc.
Shel
--- 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