Input # or Line Input # (excel vba)

Topic

New Reply

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,

Reply | Quote

Replies

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.

Reply | Quote

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

for i = 1 to 1000000
line input #1, lsRecord
lsBits = split(lsRecord,",")
next i

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

for i = 1 to 1000000
for j = 1 to 40
input #1, lsBits(j)
next j
next i


This took 34 seconds.

Reply | Quote

(Edited by jscher2000 on 27-Jul-06 18:28. Why did I think you were using VBScript? Never mind about that.)

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.

Reply | Quote