[AccessD] Automating Excel imports

Doug Murphy dw-murphy at cox.net
Mon Jun 27 14:32:20 CDT 2011


Hi Brad,

We have done something similar a few times. In Access we have an application that allows event planners to load spread sheets holding lists of vendors with phone, address, etc. None of these sheets is ever quite the same but the data must go into an Access table. What we ended up doing was creating a wizard type import form that leads the user through a series of steps to associate the appropriate Excel column with the correct Access field. The wizard works by having the user select the Excel file from a file dialog. The wizard then builds a querydef to connect to the first work sheet in the file. It then reads out the Excel column names (query field names) into at temp table which is used as the rowsource for a series of comboboxes. Each combobox is associated with one of the fields in the table the data is to be imported into. The wizard then has the user select the Excel column name that should be associated with the Access table field. After this process is completed the Excel data is appended to a temp table that is displayed to the user in datasheet view. If the data looks correct the user then completes the process and the temp table data is appended to the associated working table. The wizard is really an Access form with subforms on a tab control. As each step is completed the user is taken to the next tab page. The tab control does not have the tabs displayed.

I have thought about how to do this for a while and this is the way that I found where any user can bring in data from an excel sheet that is supposed to have data similar to what Access expects. No two people ever come up with the same spreadsheet, even if you tell them what is required. We have a web page that works in a similar fashion where a user can upload an excel file and import guest lists into a table for use in tracking event attendees. Users seem to be able to complete the process without too much trouble. You might set up a similar web system where your users would send you there data.

Doug

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Brad Marks
Sent: Saturday, June 25, 2011 5:33 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Automating Excel imports

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 
  
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   
-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of 'Steve Goodhall'
Sent: Friday, June 24, 2011 10:52 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Automating Excel imports

I give spreadsheets to clients for data entry, but I protect everything that I don't want them to mess up.  That said, it generally takes me several iterations before they stop finding ways to break it anyway. 

	Regards,  

	Steve Goodhall, MSCS, PMP
248-505-5204

----- Original Message -----
 From:Access Developers discussion and problem solving To:"Access Developers discussion and problem solving" 
Cc:
Sent:Fri, 24 Jun 2011 08:42:05 -0700
Subject:Re: [AccessD] Automating Excel imports

Duly noted!

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [1] [mailto:accessd-bounces at databaseadvisors.com [2]] On Behalf Of Stuart McLachlan
Sent: Thursday, June 23, 2011 1:20 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Automating Excel imports

Finally, you've identified the real problem.

Don't give clients spreadsheets for data entry. They *will* screw them up.
Give them a some other tool such as a simple Access database with a single continuous form bound to a single table.

--
Stuart

On 23 Jun 2011 at 7:48, Darrell Burns wrote:

> That's the problem...not all of the columns are formatted as text.
I
> created the template that way, but sometimes the client does a
paste
> and changes the format.
> 

--
AccessD mailing list
AccessD at databaseadvisors.com [3]
http://databaseadvisors.com/mailman/listinfo/accessd [4]
Website: http://www.databaseadvisors.com [5]

--
AccessD mailing list
AccessD at databaseadvisors.com [6]
http://databaseadvisors.com/mailman/listinfo/accessd [7]
Website: http://www.databaseadvisors.com [8]


Links:
------
[1] mailto:accessd-bounces at databaseadvisors.com
[2] mailto:accessd-bounces at databaseadvisors.com
[3] mailto:AccessD at databaseadvisors.com
[4] http://databaseadvisors.com/mailman/listinfo/accessd
[5] http://www.databaseadvisors.com/
[6] mailto:AccessD at databaseadvisors.com
[7] http://databaseadvisors.com/mailman/listinfo/accessd
[8] http://www.databaseadvisors.com/

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com





More information about the AccessD mailing list