Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Text File Import vs FSO (2000)

    I have the dubious honor of parsing 16 or so text files of 33.5kb each into an Excel based rollup. Each text file is generated by a somewhat antiquated cash register system and a data map is necessary to read the contents of the file. Each line of the text file begins with a category related number, i.e., 10, 20, 30, etc. Within the lines of the file the data map points to certain sets of characters relating to various register functions and information, for example, on line 3, characters 8-14 is the store number, characters 15 - 21 is the system time open, characters 22 -36 is the software revision number, etc.

    With the exception of the first two characters of each line which relate to a general category, there seems to be no vertical method of separating the contents of the file, i.e., to separate the lines after character two works all the way down, but to separate the text file line again after the 7th character on line three results in erroneous separation at line 46.

    My questions are 1) should I use the fso.opentextfile method to scroll through all text files and read certain lines, write the lines to a cell and then "pick out" certain sections of the resulting cell contents or 2) should I import the entire text file and then use specific cell string references to pick data from cells or 3) import only certain rows of each text file as independent queries and use the lookup functions to return the data?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Text File Import vs FSO (2000)

    Why not write code or formulas that use the "datamap"?

    If it needs a datamap then the wizard will not wok since it is not delimited and the columns are not consistent. If you have the datamap, use it.

    If you do NOT have it, I have found the easiest way is to get it all, and look for sections to "extract, with MID, left and right functions, looking for various "segments and breaks". for some sections a space can be used as a delimiter though that is NOT always true for some fields.

    Steve

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text File Import vs FSO (2000)

    Thanks Steve, that makes sense. I have the datamap.

Posting Permissions

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