[AccessD] Feeding Data into Access from Hundreds of Excel Fileswith no Common Definition

We finally went to the approach of making the customer put their data in
a universal format that they can upload to a website.  For a long time,
in our application for processing participant flexible spending
accounts, we had a module that our users could define the file layout
taht the customer was sending to us and save that definition (ie column
A is participant name).  As long as the customer was consistent in the
format they used, our users just called up the saved def for that client
and use it to import their file.



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Brad Marks
Sent: Wednesday, February 26, 2014 2:07 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] Feeding Data into Access from Hundreds of Excel
Fileswith no Common Definition

All,  

I work for a 50 person firm that is in the employee recognition
business.  For the past 15 years, our customers (companies) have sent
preliminary order information to us primarily via Excel files.  The
catch is that there is currently not a "common definition" for these
Excel files.  Therefore, incorporating this data into our Order Entry
System (Access 2007 based) requires a lot of manual "cutting and
pasting".  In the past, I have proposed that we institute a common
definition, but our Marketing/Sales people are not in favor of this as
it would shift the burden to our customers.

A couple years ago, I tried to automate the processing of these Excel
input files with Access VBA code.  I gave up on this approach as things
became too complex due to the hundreds of slightly different Excel file
definitions.

Recently, I have started to work on a new "semi-automated" approach.
This approach would utilize a "Staging" Excel file which will have a
single common definition.  Once the data is in this Staging file, it
will be easy to move the data into the Access database.

To reduce the amount of manual work of getting the data from the
original Excel files (various definitions) into the Staging Excel file
(one definition), I have started to build a number of Excel Macros.
These Macros can be used for such things as (1) set names to Proper Case
(2) Move the location of columns (3) concatenate two columns to create a
new column (4) eliminate quotes, hyphens, etc. (5) rounding (6) and
several other types of data manipulation.

With all of this background info, I have a couple questions.

Has anyone else ever had to deal with a similar situation of hundreds of
Excel files with slightly different definitions?

If so, what approach was used?

Thanks,
Brad


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
**********************************************************************
WARNING: All e-mail sent to and from this address will be received,
scanned or otherwise recorded by the CPI Qualified Plan Consultants, Inc.
corporate e-mail system and is subject to archival, monitoring or review 
by, and/or disclosure to, someone other than the recipient.
**********************************************************************