Results 1 to 3 of 3
  1. #1
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Import Unix Files with INPUT (ALL from 97)

    Edited to remove superfluous line ends, the browser will automatically wrap lines - Mod

    I have a Unix File that needs bringing into a spreadsheet. Some are over 100,000 rows of data. Not all the rows are needed so it is read into an array using VBA
    and then the data required data is taken out and added to the sheet. This is done using normal file commands

    open strF for input as #1

    and using either INPUT or LINE INPUT to get the data. The problem occurs with some files, where the line ending code is a CTRL 10 LineFeed not a standard Carriage Return CTRL 13. The Line Input Command looks for either CTRL 13 or CTRL 13+CTRL 10. So it just reads the whole file into the one read.

    At the moment there is a routine that does this and then loops round using Instr looking for CHR(10) and splitting the data off into a one dimensional array. Is there an easier way to read in files over 66,000 rows where the lines terminate with just a CTRL 10 code ?

    Thanks
    Andrew

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import Unix Files with INPUT (ALL from 97)

    You can open the file as a binary file, read it in and write it out to another binary file byte by byte, replacing Chr(10) by Chr(13).

    If you have only a few files, the following manual procedure should work:
    - Open the file in Word or WordPad.
    - Save it as a text file (without changing anything).
    - The line feeds will be replaced by carriage return+line feed.

  3. #3
    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: Import Unix Files with INPUT (ALL from 97)

    How about taking the text file and find/replace all the Chr(10) with chr(13)+Chr(10), then manipulate it with your code?

    This should save writing the code to parse it "indirectly" and allow the parsing more directly.

    Steve

Posting Permissions

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