Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2004
    Posts
    47

    Unanswered: Import unformatted file

    Hi Group,
    I have a couple of unformatted files which I will need to import into Access to report on - please see attachment.

    There are 5 fields. As you can see, there is summary data which I would like to exclude.

    How can I use VBA to import the file(s)?

    Kind Regards
    Attached Files Attached Files

  2. #2
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    This is a normal layout for a typical file (Fixed width)

    If you define an import specification you can use Docmd.transfertext to import it (into a temp table)
    Then use an append query to send it to a fixed table if you need to keep it. And transfer only the records where LNR is like "####" (numbers) or Isnumeric(LNR) = true

    Alternatively if you dont need to keep it you can delete the same records from the table using a delete query (and the same Where clause as above append append query)

    Regards

  3. #3
    Join Date
    Apr 2004
    Posts
    47

    How do I define an import spec...

    Namliam,
    Thanks for this....
    How do I define an import specification(again). I haven't touched Access for over 6 years now!! I'm using 2000.

    Kind Regards

  4. #4
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    File => Get external data => Import
    Find/click the file you want to import
    Click import button
    Click Advanced button
    OR
    Instead you can specify first your dilimeter/fixed file and set the lines and stuff in the wizard and click Advanced button after that

    Then click save As, name it, click ok

    Et voila one import spec created....

    Regards

  5. #5
    Join Date
    Apr 2004
    Posts
    47

    Unrecognized File format

    Hi,
    I get an unrecognized file format error when I try to import the data file.
    Were you successful in importing it?

    Kind Regards

  6. #6
    Join Date
    Apr 2004
    Posts
    47

    Ignore my last reply

    I had to rename the file so that it had a recognizable extension i.e .txt.

    Kind Regards

  7. #7
    Join Date
    Apr 2004
    Posts
    47

    The original extension was .hl

    I had to rename it to .txt to upload it....

  8. #8
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    Good then, glad you got it working ...

    Happy to help

    Regards

  9. #9
    Join Date
    Apr 2004
    Posts
    47

    Now onto some VBA...

    To strip out the blank lines, summary lines etc....

Posting Permissions

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