[AccessD] Automating Excel imports

You could always do it the "database" way.

Create a table(s) that holds the format for each of the clients and 
use that table to read out how you
need to do the import based on something in the name or the directory it is in.

You would need to code it using a xref for the column in the 
worksheet and the field you want to put
the data into. I have a friend that did this for all imports.  It is 
some work to set it up, but it runs very
smoothly.


At 10:22 AM 6/26/2011, you wrote:
>Date: Sat, 25 Jun 2011 07:32:47 -0500
>From: "Brad Marks" <BradM at blackforestltd.com>
>To: "Access Developers discussion and problem solving"
>         <accessd at databaseadvisors.com>
>Subject: Re: [AccessD] Automating Excel imports
>Message-ID:
>         <E9A6E8C998E16F4FB3D861390AFE32C50126C295 at blkltd.blkfst2003.local>
>Content-Type: text/plain;       charset="utf-8"
>
>All,
>
>This discussion about Automating Excel Imports has lead me to share 
>our situation to see if anyone else has experience in how to best 
>deal with this process.
>
>Our firm receives spreadsheets from about 100 firms 
>(customers).  The data from these 100 firms is basically the same 
>but there is variation from firm to firm.
>
>We receive one spreadsheet from each customer each month.
>
>There is a lot of variation in how the spreadsheets are 
>defined.  For example the column names vary from firm to firm.
>
>Many of these spreadsheets are automatically generated by our 
>customers.  For example, one firm may be pulling data from Oracle to 
>build their spreadsheet and another firm may use an entirely 
>different automated process.  The bottom line is that our customers 
>are not manually entering their data.
>
>In order to bend over backwards to serve the customers, we try to 
>make it as easy as possible for them to do business with us.
>
>This process has been in effect for several years.
>
>Currently the inbound spreadsheets are handled internally via a 
>manual process.  This works, but it is very labor intensive.
>
>We would like to streamline this process.  I have started a project 
>to pull the data from the customer spreadsheets and store this data 
>in Access.  Currently the customer data is simply kept by storing 
>the customer spreadsheets internally on a server.
>
>Trying to import (into Access) 100 spreadsheets that are all defined 
>differently is becoming quite challenging.
>
>We seem to be caught between a rock and a hard place.
>
>Dealing with the customer spreadsheets as they are currently defined 
>is difficult.  Asking our customers to change how they submit their 
>data is not something that our Sales Department would endorse.
>
>I am curious if anyone else has dealt with this type of 
>situation.  I realize that there is not an easy answer, but I might 
>be able to learn from others who have faced this challenge.
>
>Thanks,
>Brad
>

Robert L. Stewart
www.WeBeDb.com
www.DBGUIDesign.com
www.RLStewartPhotography.com