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

Hi Brad,

Actually, this is what a company website - yours - would be ideal for.  But
I'm sure you've suggested this and the price to get it done is a challenge.

My suggestion is that you develop an Customer Order Entry Access database
that all your customers can use to create their order (they each get their
own copy).  This should be set up so that they only use the forms to input
their information.  Because, in the end, you collect the same information
from all your customers anyway, there must be a common definition of what
information you need.  Once they enter the information into your Customer
Order database, they can push the Send button to send you an excel file, and
you can directly import that file into your database.  They can also save
their own copy of the Excel file for their records.

Your design might need to incorporate some configurability for data
differences among your customers.

A caveat is when you send them an updated Customer Order Entry database,
that the data in the previous one will not be included.  It's just a tool
for easy and accurate data entry.

Hope this helps!
Dan


-----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 Files
with 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