There are about 7 different departments (sales/marketting/qa/payro
ll/etc..) each of which record information on employees - storing the data in spreadsheets. The spreadsheets can contain multiple sheets, graphics on some pages/filters. I am sure a lot of people are familiar with this scenario.
What I want to get across is the sheer randomness of where the data I need is, so I cant exactly post examples. Just know that at least one of the sheets in a departments spreadsheet will have the data in a column/row format that I can import (after removing the company logo and blank lines at the top of the spreadsheet). I wont need all the columns in some cases - there may be a spreadsheet with 56 columns but I only 5 for my Access reports.
So my current process for each department is to...
1. Open the spreadsheet and go to the sheet that has the data I need
2. Remove the first "x" number of rows and any logos/headers which are not needed.
3. Save it as CSV
4. Using DTS, import the CSV into a SQL staging table - mapping the columns that I want
5. Tidy up the data in the SQL table (eg where certain columns are NULL)
6. Insert the data from the SQL staging table to the SQL table used in the Access Reports
7. Run a stored procedure to update supporting lookup tables and validation checks
I am using, and only have access to, SQL 2000 and Access 2000.
Some assumptions...
- Department spreadsheets are located on the network and will not move/change their filename. Sheetnames will also remain static.
- There has to be the option to do the imports through an app and/or have it run automatically (perhaps through Scheduler)
- Not all spreadsheets are done at the same time eg Payroll may have their spreadsheet updated weekly, the marketting spreadsheet monthly, sales daily
I need/want to remove the manual process, replacing the steps programmatically using modules in Access. Also ANYONE who is non-SQL literate should also be able to import just by using Access forms and clicking a button.
So my plan is to create an app in Access which will have a user interface allowing the user to select which department spreadsheet to import - thinking about it, maybe even a checkbox list so they can import in multiples.
Anyone have any ideas how to approach this? For example, should I perhaps (through code) open the spreadsheet, save it as csv and then import using BULK INSERT? Or perhaps open the spreadsheet and build loops to go through it cell by cell and build an INSERT statement. I already tried to create some DTS packages but a couple of the spreadsheets had duplicate column names so it bailed.
Looking for some inspiration. Like I said, I have about 7 spreadsheets for one line of the business, there is another line which I havent started yet plus another 2 locations so it could end up being 28 manual imports of spreadsheets.
I would say my Access is beginner, VB is good/vgood and SQL vgood.
Start Free Trial
