I receive several excel spreadsheets (xls extension, which I believe is Excel 2003) that contains several lines of header data that I need to strip and then save as a csv file (This was the easiest way I could think of to convert the multiple text columns that are really numeric columns to actual numeric columns) and then append this csv file (Call it say "Upload.csv") into an existing Access 2007 database and then delete csv file. I would like to do this from an Access 2007 form.
I would like the form to prompt the user for the location of the excel speadsheet and then hit the OK button to execute the above processes which are then are run in the background.
I would like to automate this entire process from an Access form interface. Can this even be done? I am a newbie and don't want to head down the road of trying to figure out how to do this unless I am sure it can be done.
Below is a dummy sample of the data JohnsData.xls
John's Home Delivery Service Service you can count on Customer Name: Frank Johnson ID: FJ1023 OrderID
Item ProductID Price
Quan
Discount
FJ1023-10042008 Carrots 11 14 12 0 FJ1023-10042008 Apples 42 6.4 10 0 FJ1023-10042008 Lettuce 15 42.75 5 0 FJ1023-10042008 Celery 1078 64.3 9 0.15
Thank for any an all help.