Thread: Best approach
11-07-06, 16:31 #1Registered User
- Join Date
- Apr 2006
Unanswered: Best approach
I need advice on the best approach for my project. I will have two different spreadsheets (.xls or .csv) that I will need to dump in an Access database bi-weekly. Once I dump all the data I want to run several reports and one of them I want to have couple of basic formulas, mostly "how much are we over/under", etc. What is the best way of importing these spreadsheets, I have been playing with it and cant get it to import them all, just the first record.
11-08-06, 03:31 #2Cavalier King Charles
- Join Date
- Dec 2002
- Préverenges, Switzerland
i prefer .csv
do one import manually with the file/getexternaldata/import
do the delimited and first-row-contains-headings steps in the 'wizard'
then hit the 'advanced' button to define an import specification
SAVE the import specification e.g. 'myImportSpec01'
most likely thing to go wrong is A making stupid decisions about datatypes: if you have a field with values 21, 44, AA, 2G, 3G then A will usually try to use integer (which will fail on the third value). if in doubt, make it text.
once you have it working 'manually' and you have saved the import spec, you should be able to import all similar structure .CSV from code with.
docmd.transfertext acimportdelim, "myImportSpec01", "myTargetTable", "C:\temp\myFile.CSV", TrueFalse
izycurrently using SS 2008R2
11-08-06, 07:58 #3Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
Izyriders solution is (as you would expect) fine... and probably tthe best solution
However if you are going to be doing this on a frequent basis then I suggest considering writing a VB procedure to read in all the spreadsheets. However there are a few caveats
you must know the excel format,
and you ensure your users are disciplined enough so that they dont tinker with the format,
that the excel spreadsheet will always contain the full information (eg for foreign keys or required values),
you need to decide how to handle errors (do you reject the whole sheet, or just those rows you can't handle),
The whole process can be run as an automated/timed process thorugh windows scheduler say overnight. However you would need to be confident that the development cost is worth while investing in as opposed to the time taken to manually import the spreadsheets.
Last edited by healdem; 11-08-06 at 08:02.I'd rather be riding on the Tiger 800 or the Norton