Rocky Smolin
rockysmolin at bchacc.com
Sat Jun 25 09:56:25 CDT 2011
I had this situation with an union related business that collects money from contractors for up to 8 or 10 different fund. A few of the contractors are su7bmitting the timesheets via spreadsheet. They were not amenable to change to a standard form. So I found it easiest to write a different routine for each contractor that was submitting their data through a spreadsheet. The data from each is pretty much the sdame - just appears in different places. So I import their sheet into a temp table and sort out the fields from there. Turned out to be pretty easy, and accomodated contractors who decided to switch from manual to spreadsheet submission. TO make a new spreadsheet import is a lot of copy and paste but you end up with a nice library of input routines. R -----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