Rusty Hammond
Rusty.Hammond at cpiqpc.com
Wed Feb 26 14:53:12 CST 2014
We finally went to the approach of making the customer put their data in a universal format that they can upload to a website. For a long time, in our application for processing participant flexible spending accounts, we had a module that our users could define the file layout taht the customer was sending to us and save that definition (ie column A is participant name). As long as the customer was consistent in the format they used, our users just called up the saved def for that client and use it to import their file. -----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 Fileswith 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 ********************************************************************** WARNING: All e-mail sent to and from this address will be received, scanned or otherwise recorded by the CPI Qualified Plan Consultants, Inc. corporate e-mail system and is subject to archival, monitoring or review by, and/or disclosure to, someone other than the recipient. **********************************************************************