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

LOL, Hire Arthur to do this on his Windows phone.  Should be instructive.  And amusing.

John W. Colby

Reality is what refuses to go away
when you do not believe in it

On 2/26/2014 3:07 PM, Brad Marks wrote:
> 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
>
>


---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com