Results 1 to 4 of 4
  1. #1
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    Location
    New York, New York, USA
    Posts
    768
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Input # or Line Input # (excel vba)

    I have a comma delimited record with an unknown number of variables. Questions:

    Can I read this record with Input #, ...
    At present, I read it with Line Input#, and then split the resulting text variable using the split() function - is there a better / faster way?

    Cheers,
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

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

    Re: Input # or Line Input # (excel vba)

    Input # only works with a fixed number of variables - you have to specify them explicitly. If you don't know the number of variables, you must use Line Input # and parse the (single) variable. If there is only one delimiter, using Split is the easiest way to do that.

  3. #3
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Input # or Line Input # (excel vba)

    <P ID="edit" class=small>(Edited by jscher2000 on 27-Jul-06 18:28. Why did I think you were using VBScript? Never mind about that.)</P>Are there drawbacks to your current method or were you just curious?

    In another context, I use Microsoft's ADO library to read a CSV-formatted file. This uses more "overhead" than the built-in VBA functions, but has some nice database-like features.

  4. #4
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    Location
    New York, New York, USA
    Posts
    768
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Input # or Line Input # (excel vba)

    Thx Hans. The good thing about the file I am reading is that the first record is a dummy record with various bits of info about the rest of the records and has a pre-defined number of variables. Records 2 to n all have the same number of variables per record. So, I am thinking that I can add the number of variables to the dummy record and then loop through enough "Input #1" lines to read the whole record.

    I tested it by writing a million records with 40 variables each.

    Testing for speed ...

    Option 1 - line input #1 with split to get individual variables into array
    <code>
    for i = 1 to 1000000
    line input #1, lsRecord
    lsBits = split(lsRecord,",")
    next i</code>

    VBA with million reads and a million splits to give me an array of the input record. This read took 54 seconds.

    Option 2 - input #1 inside a for next loop to read all the variables into array
    <code>
    for i = 1 to 1000000
    for j = 1 to 40
    input #1, lsBits(j)
    next j
    next i
    </code>

    This took 34 seconds.
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

Posting Permissions

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