Results 1 to 3 of 3

Thread: Best approach

  1. #1
    Join Date
    Apr 2006
    Posts
    7

    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.

    Thanks!! Samantha

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    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.

    Code:
    docmd.transfertext acimportdelim, "myImportSpec01", "myTargetTable", "C:\temp\myFile.CSV", TrueFalse
    (True if first row of CSV is fieldname, else false)

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •