Re: [MS_AccessPros] Re: VBA help requested with automating import of unknown files with unknown headers into temporary table

Hi, Shel,

Thank you! I think you just gave me a jumping off point to get thru my
coders block!

For some reason I was thinking I'd need to do something like read the
header row, parse it and create a make-table query. I'll go read up on the
transfer methods and see whether I can use import specs, and if I can
define one that will be loose enough to ensure it all comes in as text. :)

Hi, Lee,

Thank you for weighing in, too. While I'm not married to temp tables as the
final solution, I'm not seeing that we can get away from them because the
majority of clients send us data sets just enough different to make some
kind of human-intervention needed in defining which subset of data points
we received and where. And to make things even more complex, we can be
required to summarize multiple lines with a variety of maxing and summing
situations in addition to the record-level data point manipulations.

I'm hoping to be able to hand off to our internal development team before
it's time to start adding in automatic identification of the values we're
receiving. :)

~*~*~*~*~*~*~
Shay Bella Holmes
~*~*~*~*~*~*~

On Wed, Apr 11, 2012 at 10:57 AM, sarmbraugh <sarmbraugh@yahoo.com> wrote:

> **
>
>
> 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]
> >
>
>
>

[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/MS_Access_Professionals/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/MS_Access_Professionals/join
(Yahoo! ID required)

<*> To change settings via email:
MS_Access_Professionals-digest@yahoogroups.com
MS_Access_Professionals-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
MS_Access_Professionals-unsubscribe@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/