Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2005
    Posts
    144

    Red face Unanswered: Importing data from excel to access

    Hello All,
    i have a problem with importing excel data into access. let me explain briefly how i am doing it.
    Everyday we run reports from a program we use here called sap which exports data into excel or txt format. In the near future instead of deleting the table afterwords we might be archiving it.

    excel export:
    After exporting it i run a macro which sorts out the data into proper format, however there are normally 2 fields with text and numbers in them.
    When i use the transferspreadsheet method it skips data with text since field starts with numbers and then it has some text in it as well.

    I can copy and paste it which works fine but its pain in the neck trying to do that everyday, specailly when you have 10,000 records per report.

    Is there another way i can import the data from excel without missing some of the data???

    Thanks alot guys!

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7
    What I would Do is play around with the Exporting Function in Sap

    as it can export in excel or txt

    is the txt file csv or fixed lenght format

    not much help but mite open a can for you
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    my preference is to import from .CSV using an import specification. this denies Access the opportunity to make 'clever' guesses about datatypes.

    izy
    currently using SS 2008R2

  4. #4
    Join Date
    Jul 2005
    Posts
    144

    let me try it your way

    Thanks myle and izy for your prompt reply,
    let me work on the text format and see if that helps.

    So i see mighty excel is still limited or is it access??

    THanks

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    neither really!

    A is part of the mass-market Office suite: Word; Excel; Outlook; blah blah.
    99.9% of Office users don't get beyond the e-typewriter stage in any of the applications.

    A got added to the suite with it's wizards etc in an attempt to make it the same sort of 'e-typewriter experience'.

    to give M$ their due, they did a reasonable job, but a db is never going to be the same as a typewriter - the XLS import wizard does it's fluff-and-smoke-and-mirrors thing looking at the first few rows ...and mostly gets it wrong! you could argue that the wizard should be 'tuned' to allow the user to select a 'typical' row on which to base the import.
    ...but it's actually so easy to do the job yourself via CSV to temporary table to final table, that by the time you have understod that the wizard is brain-dead, you already know enough to do the job correctly from CSV.

    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Sep 2006
    Posts
    3
    Ran,
    I like to export the SAP download as as CSV text file. Then link a table in Access to this. Then I run an action query to create or append the file to an existing or new Access table.

    Once you have the link in Access established to the CSV file, overwrite the download each time (keep the name the same) so that you don't have to re-establish the link.

  7. #7
    Join Date
    Feb 2004
    Location
    Wales
    Posts
    343
    Hi Ran

    the problem you are hving is a common "bug" (or as MS call it a feature) when importing alph text in to access.

    I have a small fix for the excel column data. if you want it let me know with an e-mail address and Ill send it to you straight away

    Gareth

Posting Permissions

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