Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Jul 2003
    Location
    Newtown, Pennsylvania, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Importing text files into Access 2000

    I have a text file containing merged and deduplicated records from 3 online databases. Each record has a series of tagged fields, starting on a new line. Some of the fields are longer than one line - each line in multiline field ends with a carriage return, and the subsequent line starts with several spaces.

    The question is what is the best way to approach getting this into Access for analysis (the actual analysis is done with Excel pivot tables) after reviewing each of the records, and adding additional information in a relational database? I have done some work on this using Perl, but would like to have the whole thing made 'portable' so that I don't have to be the one to do all the importing[img]/w3timages/icons/smile.gif[/img]. Can/should it be done with VBA, or should I use VB? Where can I find some pointers on how to get started with opening and reading text files?

    Each of the databases has different formats, but that should be relatively straightforward to deal with, once I can open and read the file ...

    Thanks, for any help/guidance.

    Kiwi44

  2. #2
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing text files into Access 2000

    Gidday Kiwi,

    I don't know if you have control yourself to do this- but it is possible for Excel VBA to write directly to an Access database- is that any use to you?
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  3. #3
    2 Star Lounger
    Join Date
    Jul 2003
    Location
    Newtown, Pennsylvania, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing text files into Access 2000

    Thanks, that is part of the answer - I think the direct input to the database will work in Access too!

    I also found some very useful code to start with at http://www.mvps.org/access/modules/mdl0057.htm - takes out leading spaces, and does a whole lot more that makes it great for anyone wanting to deal with tagged text files!

    On the same site, I also found code for opening the find file dialog, etc., which will be very useful in avoiding hardcoding file path/names, which was something I did not want to do.

    Kiwi44

    Now I know where I am going with this - I just have to write all the code to actually manipulate the text [img]/w3timages/icons/smile.gif[/img].

Posting Permissions

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