Ideas for quicker way to populate adjacent cells with array elemen

Hi,

I written a macro to read in line by line multiple CSV files of varying
lengths.

Can anyone suggest a faster way in terms of runtime execution time to speed
this code up?

ie. I thought of reading in multiple lines in bulk to reduce I/O but VBA
doesn't seem to have a bulk read mode.

Basically i do the following:

Dim columnArrays as Variant
Dim newColumnArray as Variant
..
..
Do until EOF(inputFile)
Line Input #inputFile, inputLine
columnArray = Split(inputLine, ",")
....merge/massage/apply functions to certain columns in columnArray
and copy into newColumnArray
for i = 0 to Ubound(newColumnArray)
aRange.Offset(0,i).Value = newColumnArray(i)
next i
set aRnage = aRange.Offset(1,0)
Loop

Thanks to everyone for their valuable insights.

Is there a reason you couldn't just use:
workbooks.open filename:="C:\myfolder\inputname.csv"

If you want more control of individual field formats, rename your .csv file to
..txt and then record a macro when you do file|open.

I would think that opening multiple files this way (and combining later) would
be quicker (even noticeable when the number of lines gets larger).

Another option would be to use an old DOS command to combine your .csv files
into a single file, then import that once.

If the files are in the same folder and all files named *.csv should be
combined:

Shell to DOS
traverse to that folder
copy *.csv all.txt
Then exit the command prompt.

Back to excel and import All.Txt.

===
You could even mechanize this in your code via the shell command (and maybe an
application.wait to make sure the files are combined before continuing).





Bing wrote:
>
> Hi,
>
> I written a macro to read in line by line multiple CSV files of varying
> lengths.
>
> Can anyone suggest a faster way in terms of runtime execution time to speed
> this code up?
>
> ie. I thought of reading in multiple lines in bulk to reduce I/O but VBA
> doesn't seem to have a bulk read mode.
>
> Basically i do the following:
>
> Dim columnArrays as Variant
> Dim newColumnArray as Variant
> .
> .
> Do until EOF(inputFile)
> Line Input #inputFile, inputLine
> columnArray = Split(inputLine, ",")
> ....merge/massage/apply functions to certain columns in columnArray
> and copy into newColumnArray
> for i = 0 to Ubound(newColumnArray)
> aRange.Offset(0,i).Value = newColumnArray(i)
> next i
> set aRnage = aRange.Offset(1,0)
> Loop
>
> Thanks to everyone for their valuable insights.

--

Dave Peterson

Hi Dave,

Thanks for responding.

The reason why i didn't use workbooks.open or workbooks.opentext is because
of several reasons:

1)Some files are much more than 65K lines. Since excel has a limit of 65536,
i need to import the file across multiple worksheets.
2)Yes, i wanted more control. Although i haven't actually tested to
determine which method is faster (ie. using workbooks.open first, and then
combining the columns later versus using Line Input, combining columns first,
then pasting to worksheet later) my gut feeling would be that the former is
quicker in loading lines into workbook, but the latter would be quicker in
combining the columns). But i think you may be right that as the number of
lines increase it would be quicker using the workbooks.open first, combine
later. But due to 1) i can't seem to find a alternative to doing the way
that i had outlined earliar (reading in manually line by line so that i can
add a new worksheets in the even the number of lines is >64K).

This application is being developed for customers so i'd like to minimize
steps that they have to perform. ie. i dont want them to have to separate
the large file into individual 64K line subfiles.

"Dave Peterson" wrote:

> Is there a reason you couldn't just use:
> workbooks.open filename:="C:\myfolder\inputname.csv"
>
> If you want more control of individual field formats, rename your .csv file to
> ..txt and then record a macro when you do file|open.
>
> I would think that opening multiple files this way (and combining later) would
> be quicker (even noticeable when the number of lines gets larger).
>
> Another option would be to use an old DOS command to combine your .csv files
> into a single file, then import that once.
>
> If the files are in the same folder and all files named *.csv should be
> combined:
>
> Shell to DOS
> traverse to that folder
> copy *.csv all.txt
> Then exit the command prompt.
>
> Back to excel and import All.Txt.
>
> ===
> You could even mechanize this in your code via the shell command (and maybe an
> application.wait to make sure the files are combined before continuing).
>
>
>
>
>
> Bing wrote:
> >
> > Hi,
> >
> > I written a macro to read in line by line multiple CSV files of varying
> > lengths.
> >
> > Can anyone suggest a faster way in terms of runtime execution time to speed
> > this code up?
> >
> > ie. I thought of reading in multiple lines in bulk to reduce I/O but VBA
> > doesn't seem to have a bulk read mode.
> >
> > Basically i do the following:
> >
> > Dim columnArrays as Variant
> > Dim newColumnArray as Variant
> > .
> > .
> > Do until EOF(inputFile)
> > Line Input #inputFile, inputLine
> > columnArray = Split(inputLine, ",")
> > ....merge/massage/apply functions to certain columns in columnArray
> > and copy into newColumnArray
> > for i = 0 to Ubound(newColumnArray)
> > aRange.Offset(0,i).Value = newColumnArray(i)
> > next i
> > set aRnage = aRange.Offset(1,0)
> > Loop
> >
> > Thanks to everyone for their valuable insights.
>
> --
>
> Dave Peterson
>

Hi Bing,

Firstly, try replacing the following code ,

> for i = 0 to Ubound(newColumnArray)
> aRange.Offset(0,i).Value = newColumnArray(i)
> next i

with this...

aRange.Resize(, UBound(newColumnArray) + 1) = newColumnArray


What this does is transfer the whole array to the worksheet in one fell
swoop, as opposed to transferring each element one by one. The speed gain
comes from the fact that your calls to Excel's slow Range object are reduced
from multiple calls, to a single call.
Now take this concept a little further. Instead of creating a 1-dimensional
array of each and every row, then transferring each row separately, you
should create a 2-dimensional array of all rows and columns, and transfer the
whole
array in one hit after EOF has been reached.

You'll be removing hundreds of thousands of calls to the Range object.
You're in for a nice surprise. It's gonna fly !


Regards,
Vic Eldridge



"Bing" wrote:

> Hi,
>
> I written a macro to read in line by line multiple CSV files of varying
> lengths.
>
> Can anyone suggest a faster way in terms of runtime execution time to speed
> this code up?
>
> ie. I thought of reading in multiple lines in bulk to reduce I/O but VBA
> doesn't seem to have a bulk read mode.
>
> Basically i do the following:
>
> Dim columnArrays as Variant
> Dim newColumnArray as Variant
> .
> .
> Do until EOF(inputFile)
> Line Input #inputFile, inputLine
> columnArray = Split(inputLine, ",")
> ....merge/massage/apply functions to certain columns in columnArray
> and copy into newColumnArray
> for i = 0 to Ubound(newColumnArray)
> aRange.Offset(0,i).Value = newColumnArray(i)
> next i
> set aRnage = aRange.Offset(1,0)
> Loop
>
> Thanks to everyone for their valuable insights.

First, excel may not be the best application for this amount of data.

I notice that excel starts to slow down considerably when I have lots of rows of
data (no formulas even).

I don't use Access (and maybe your customers don't either), but have you thought
of using a different application.





Bing wrote:
>
> Hi Dave,
>
> Thanks for responding.
>
> The reason why i didn't use workbooks.open or workbooks.opentext is because
> of several reasons:
>
> 1)Some files are much more than 65K lines. Since excel has a limit of 65536,
> i need to import the file across multiple worksheets.
> 2)Yes, i wanted more control. Although i haven't actually tested to
> determine which method is faster (ie. using workbooks.open first, and then
> combining the columns later versus using Line Input, combining columns first,
> then pasting to worksheet later) my gut feeling would be that the former is
> quicker in loading lines into workbook, but the latter would be quicker in
> combining the columns). But i think you may be right that as the number of
> lines increase it would be quicker using the workbooks.open first, combine
> later. But due to 1) i can't seem to find a alternative to doing the way
> that i had outlined earliar (reading in manually line by line so that i can
> add a new worksheets in the even the number of lines is >64K).
>
> This application is being developed for customers so i'd like to minimize
> steps that they have to perform. ie. i dont want them to have to separate
> the large file into individual 64K line subfiles.
>
> "Dave Peterson" wrote:
>
> > Is there a reason you couldn't just use:
> > workbooks.open filename:="C:\myfolder\inputname.csv"
> >
> > If you want more control of individual field formats, rename your .csv file to
> > ..txt and then record a macro when you do file|open.
> >
> > I would think that opening multiple files this way (and combining later) would
> > be quicker (even noticeable when the number of lines gets larger).
> >
> > Another option would be to use an old DOS command to combine your .csv files
> > into a single file, then import that once.
> >
> > If the files are in the same folder and all files named *.csv should be
> > combined:
> >
> > Shell to DOS
> > traverse to that folder
> > copy *.csv all.txt
> > Then exit the command prompt.
> >
> > Back to excel and import All.Txt.
> >
> > ===
> > You could even mechanize this in your code via the shell command (and maybe an
> > application.wait to make sure the files are combined before continuing).
> >
> >
> >
> >
> >
> > Bing wrote:
> > >
> > > Hi,
> > >
> > > I written a macro to read in line by line multiple CSV files of varying
> > > lengths.
> > >
> > > Can anyone suggest a faster way in terms of runtime execution time to speed
> > > this code up?
> > >
> > > ie. I thought of reading in multiple lines in bulk to reduce I/O but VBA
> > > doesn't seem to have a bulk read mode.
> > >
> > > Basically i do the following:
> > >
> > > Dim columnArrays as Variant
> > > Dim newColumnArray as Variant
> > > .
> > > .
> > > Do until EOF(inputFile)
> > > Line Input #inputFile, inputLine
> > > columnArray = Split(inputLine, ",")
> > > ....merge/massage/apply functions to certain columns in columnArray
> > > and copy into newColumnArray
> > > for i = 0 to Ubound(newColumnArray)
> > > aRange.Offset(0,i).Value = newColumnArray(i)
> > > next i
> > > set aRnage = aRange.Offset(1,0)
> > > Loop
> > >
> > > Thanks to everyone for their valuable insights.
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

"Bing" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> I written a macro to read in line by line multiple CSV files of varying
> lengths.
>
> Can anyone suggest a faster way in terms of runtime execution time to
speed
> this code up?
>
> ie. I thought of reading in multiple lines in bulk to reduce I/O but VBA
> doesn't seem to have a bulk read mode.
>
> Basically i do the following:
>
> Dim columnArrays as Variant
> Dim newColumnArray as Variant
> .
> .
> Do until EOF(inputFile)
> Line Input #inputFile, inputLine
> columnArray = Split(inputLine, ",")
> ....merge/massage/apply functions to certain columns in columnArray
> and copy into newColumnArray
> for i = 0 to Ubound(newColumnArray)
> aRange.Offset(0,i).Value = newColumnArray(i)
> next i
> set aRnage = aRange.Offset(1,0)
> Loop
>
> Thanks to everyone for their valuable insights.

You do call Application.ScreenUpdate=False, I hope?

/ Fredrik

Bing wrote:
> I written a macro to read in line by line multiple CSV files of
varying
> lengths.
>
> Can anyone suggest a faster way in terms of runtime execution time to
speed
> this code up?

See:

http://msdn.microsoft.com/library/de...ng03092004.asp
Jamie.

--

Hi Jamie,

Interesting. Do you know what version of Excel is required for this
particular methodogly, and what sort of libraries neet to be installed? (ie.
it look like Microsoft Jet has to be installed as the "database" driver")

Also,do you have any ideas how fast a query using ADO on a very large file
to do things like subtotaling compared to writing vba code to parse same data
to do the subtotaling?

Thanks

"onedaywhen" wrote:

> Bing wrote:
> > I written a macro to read in line by line multiple CSV files of
> varying
> > lengths.
> >
> > Can anyone suggest a faster way in terms of runtime execution time to
> speed
> > this code up?
>
> See:
>
> http://msdn.microsoft.com/library/de...ng03092004.asp
> Jamie.
>
> --
>
>

Hi Vic,

Thanks i tried it.. i got an almost 6x speed improvement. Only 1 thing..
this operation seems to automatically convert numbers as text. So rather
than have to call aRange.PasteSpecial to convert from text back to numbers,
is there a way to do this so that the resize operation treats the data as is,
without converting to text.

Thanks again.

"Vic Eldridge" wrote:

> Hi Bing,
>
> Firstly, try replacing the following code ,
>
> > for i = 0 to Ubound(newColumnArray)
> > aRange.Offset(0,i).Value = newColumnArray(i)
> > next i
>
> with this...
>
> aRange.Resize(, UBound(newColumnArray) + 1) = newColumnArray
>
>
> What this does is transfer the whole array to the worksheet in one fell
> swoop, as opposed to transferring each element one by one. The speed gain
> comes from the fact that your calls to Excel's slow Range object are reduced
> from multiple calls, to a single call.
> Now take this concept a little further. Instead of creating a 1-dimensional
> array of each and every row, then transferring each row separately, you
> should create a 2-dimensional array of all rows and columns, and transfer the
> whole
> array in one hit after EOF has been reached.
>
> You'll be removing hundreds of thousands of calls to the Range object.
> You're in for a nice surprise. It's gonna fly !
>
>
> Regards,
> Vic Eldridge
>
>
>
> "Bing" wrote:
>
> > Hi,
> >
> > I written a macro to read in line by line multiple CSV files of varying
> > lengths.
> >
> > Can anyone suggest a faster way in terms of runtime execution time to speed
> > this code up?
> >
> > ie. I thought of reading in multiple lines in bulk to reduce I/O but VBA
> > doesn't seem to have a bulk read mode.
> >
> > Basically i do the following:
> >
> > Dim columnArrays as Variant
> > Dim newColumnArray as Variant
> > .
> > .
> > Do until EOF(inputFile)
> > Line Input #inputFile, inputLine
> > columnArray = Split(inputLine, ",")
> > ....merge/massage/apply functions to certain columns in columnArray
> > and copy into newColumnArray
> > for i = 0 to Ubound(newColumnArray)
> > aRange.Offset(0,i).Value = newColumnArray(i)
> > next i
> > set aRnage = aRange.Offset(1,0)
> > Loop
> >
> > Thanks to everyone for their valuable insights.

Hi Bing,

The only way I could get this process to convert from values to text was to
dimension the array as String. Arrays dimmed as Longs,Doubles,Variants all
transferred to the worksheet as values. Is that where the problem lies ?


Regards,
Vic Eldridge


"Bing" wrote:

> Hi Vic,
>
> Thanks i tried it.. i got an almost 6x speed improvement. Only 1 thing..
> this operation seems to automatically convert numbers as text. So rather
> than have to call aRange.PasteSpecial to convert from text back to numbers,
> is there a way to do this so that the resize operation treats the data as is,
> without converting to text.
>
> Thanks again.
>
> "Vic Eldridge" wrote:
>
> > Hi Bing,
> >
> > Firstly, try replacing the following code ,
> >
> > > for i = 0 to Ubound(newColumnArray)
> > > aRange.Offset(0,i).Value = newColumnArray(i)
> > > next i
> >
> > with this...
> >
> > aRange.Resize(, UBound(newColumnArray) + 1) = newColumnArray
> >
> >
> > What this does is transfer the whole array to the worksheet in one fell
> > swoop, as opposed to transferring each element one by one. The speed gain
> > comes from the fact that your calls to Excel's slow Range object are reduced
> > from multiple calls, to a single call.
> > Now take this concept a little further. Instead of creating a 1-dimensional
> > array of each and every row, then transferring each row separately, you
> > should create a 2-dimensional array of all rows and columns, and transfer the
> > whole
> > array in one hit after EOF has been reached.
> >
> > You'll be removing hundreds of thousands of calls to the Range object.
> > You're in for a nice surprise. It's gonna fly !
> >
> >
> > Regards,
> > Vic Eldridge
> >
> >
> >
> > "Bing" wrote:
> >
> > > Hi,
> > >
> > > I written a macro to read in line by line multiple CSV files of varying
> > > lengths.
> > >
> > > Can anyone suggest a faster way in terms of runtime execution time to speed
> > > this code up?
> > >
> > > ie. I thought of reading in multiple lines in bulk to reduce I/O but VBA
> > > doesn't seem to have a bulk read mode.
> > >
> > > Basically i do the following:
> > >
> > > Dim columnArrays as Variant
> > > Dim newColumnArray as Variant
> > > .
> > > .
> > > Do until EOF(inputFile)
> > > Line Input #inputFile, inputLine
> > > columnArray = Split(inputLine, ",")
> > > ....merge/massage/apply functions to certain columns in columnArray
> > > and copy into newColumnArray
> > > for i = 0 to Ubound(newColumnArray)
> > > aRange.Offset(0,i).Value = newColumnArray(i)
> > > next i
> > > set aRnage = aRange.Offset(1,0)
> > > Loop
> > >
> > > Thanks to everyone for their valuable insights.

Hi Vic,

The array is defined as variants, with some elements being integer values,
other's are string literals.

So when i do the resize, the problem is the resize converts all integer
values to text ie. 8 becomes "8". And hence, i can't perform any
mathematical operations on cell contents until i convert the cell contents
back into integer values.

I tried using the PasteSpecial method to convert back to integer but doing
this really slows everything back down to a crawl again.


"Vic Eldridge" wrote:

> Hi Bing,
>
> The only way I could get this process to convert from values to text was to
> dimension the array as String. Arrays dimmed as Longs,Doubles,Variants all
> transferred to the worksheet as values. Is that where the problem lies ?
>
>
> Regards,
> Vic Eldridge
>
>
> "Bing" wrote:
>
> > Hi Vic,
> >
> > Thanks i tried it.. i got an almost 6x speed improvement. Only 1 thing..
> > this operation seems to automatically convert numbers as text. So rather
> > than have to call aRange.PasteSpecial to convert from text back to numbers,
> > is there a way to do this so that the resize operation treats the data as is,
> > without converting to text.
> >
> > Thanks again.
> >
> > "Vic Eldridge" wrote:
> >
> > > Hi Bing,
> > >
> > > Firstly, try replacing the following code ,
> > >
> > > > for i = 0 to Ubound(newColumnArray)
> > > > aRange.Offset(0,i).Value = newColumnArray(i)
> > > > next i
> > >
> > > with this...
> > >
> > > aRange.Resize(, UBound(newColumnArray) + 1) = newColumnArray
> > >
> > >
> > > What this does is transfer the whole array to the worksheet in one fell
> > > swoop, as opposed to transferring each element one by one. The speed gain
> > > comes from the fact that your calls to Excel's slow Range object are reduced
> > > from multiple calls, to a single call.
> > > Now take this concept a little further. Instead of creating a 1-dimensional
> > > array of each and every row, then transferring each row separately, you
> > > should create a 2-dimensional array of all rows and columns, and transfer the
> > > whole
> > > array in one hit after EOF has been reached.
> > >
> > > You'll be removing hundreds of thousands of calls to the Range object.
> > > You're in for a nice surprise. It's gonna fly !
> > >
> > >
> > > Regards,
> > > Vic Eldridge
> > >
> > >
> > >
> > > "Bing" wrote:
> > >
> > > > Hi,
> > > >
> > > > I written a macro to read in line by line multiple CSV files of varying
> > > > lengths.
> > > >
> > > > Can anyone suggest a faster way in terms of runtime execution time to speed
> > > > this code up?
> > > >
> > > > ie. I thought of reading in multiple lines in bulk to reduce I/O but VBA
> > > > doesn't seem to have a bulk read mode.
> > > >
> > > > Basically i do the following:
> > > >
> > > > Dim columnArrays as Variant
> > > > Dim newColumnArray as Variant
> > > > .
> > > > .
> > > > Do until EOF(inputFile)
> > > > Line Input #inputFile, inputLine
> > > > columnArray = Split(inputLine, ",")
> > > > ....merge/massage/apply functions to certain columns in columnArray
> > > > and copy into newColumnArray
> > > > for i = 0 to Ubound(newColumnArray)
> > > > aRange.Offset(0,i).Value = newColumnArray(i)
> > > > next i
> > > > set aRnage = aRange.Offset(1,0)
> > > > Loop
> > > >
> > > > Thanks to everyone for their valuable insights.

Hi Vic,

Wasn't too sure what you meant when you said "only way I(you) could get his
process to convert from values to text".... in my case, i don't want to
convert to text, but want to remain as values (ie. integers) in the cells so
i can do mathematical formulas on them.

"Vic Eldridge" wrote:

> Hi Bing,
>
> The only way I could get this process to convert from values to text was to
> dimension the array as String. Arrays dimmed as Longs,Doubles,Variants all
> transferred to the worksheet as values. Is that where the problem lies ?
>
>
> Regards,
> Vic Eldridge
>
>
> "Bing" wrote:
>
> > Hi Vic,
> >
> > Thanks i tried it.. i got an almost 6x speed improvement. Only 1 thing..
> > this operation seems to automatically convert numbers as text. So rather
> > than have to call aRange.PasteSpecial to convert from text back to numbers,
> > is there a way to do this so that the resize operation treats the data as is,
> > without converting to text.
> >
> > Thanks again.
> >
> > "Vic Eldridge" wrote:
> >
> > > Hi Bing,
> > >
> > > Firstly, try replacing the following code ,
> > >
> > > > for i = 0 to Ubound(newColumnArray)
> > > > aRange.Offset(0,i).Value = newColumnArray(i)
> > > > next i
> > >
> > > with this...
> > >
> > > aRange.Resize(, UBound(newColumnArray) + 1) = newColumnArray
> > >
> > >
> > > What this does is transfer the whole array to the worksheet in one fell
> > > swoop, as opposed to transferring each element one by one. The speed gain
> > > comes from the fact that your calls to Excel's slow Range object are reduced
> > > from multiple calls, to a single call.
> > > Now take this concept a little further. Instead of creating a 1-dimensional
> > > array of each and every row, then transferring each row separately, you
> > > should create a 2-dimensional array of all rows and columns, and transfer the
> > > whole
> > > array in one hit after EOF has been reached.
> > >
> > > You'll be removing hundreds of thousands of calls to the Range object.
> > > You're in for a nice surprise. It's gonna fly !
> > >
> > >
> > > Regards,
> > > Vic Eldridge
> > >
> > >
> > >
> > > "Bing" wrote:
> > >
> > > > Hi,
> > > >
> > > > I written a macro to read in line by line multiple CSV files of varying
> > > > lengths.
> > > >
> > > > Can anyone suggest a faster way in terms of runtime execution time to speed
> > > > this code up?
> > > >
> > > > ie. I thought of reading in multiple lines in bulk to reduce I/O but VBA
> > > > doesn't seem to have a bulk read mode.
> > > >
> > > > Basically i do the following:
> > > >
> > > > Dim columnArrays as Variant
> > > > Dim newColumnArray as Variant
> > > > .
> > > > .
> > > > Do until EOF(inputFile)
> > > > Line Input #inputFile, inputLine
> > > > columnArray = Split(inputLine, ",")
> > > > ....merge/massage/apply functions to certain columns in columnArray
> > > > and copy into newColumnArray
> > > > for i = 0 to Ubound(newColumnArray)
> > > > aRange.Offset(0,i).Value = newColumnArray(i)
> > > > next i
> > > > set aRnage = aRange.Offset(1,0)
> > > > Loop
> > > >
> > > > Thanks to everyone for their valuable insights.

Ok, i got you know.. i think i know where the problem lies.

Dim columns as Variant
columns = split(inputString, ",") <- i think this returns an array of strings.
range.resize(,UBound(columns)+1) = columns <- puts in text in the cells

I think i need to keepthe columns array as, like you said, as variant.

Guess i have to use some other method other than split?

"Vic Eldridge" wrote:

> Hi Bing,
>
> The only way I could get this process to convert from values to text was to
> dimension the array as String. Arrays dimmed as Longs,Doubles,Variants all
> transferred to the worksheet as values. Is that where the problem lies ?
>
>
> Regards,
> Vic Eldridge
>
>
> "Bing" wrote:
>
> > Hi Vic,
> >
> > Thanks i tried it.. i got an almost 6x speed improvement. Only 1 thing..
> > this operation seems to automatically convert numbers as text. So rather
> > than have to call aRange.PasteSpecial to convert from text back to numbers,
> > is there a way to do this so that the resize operation treats the data as is,
> > without converting to text.
> >
> > Thanks again.
> >
> > "Vic Eldridge" wrote:
> >
> > > Hi Bing,
> > >
> > > Firstly, try replacing the following code ,
> > >
> > > > for i = 0 to Ubound(newColumnArray)
> > > > aRange.Offset(0,i).Value = newColumnArray(i)
> > > > next i
> > >
> > > with this...
> > >
> > > aRange.Resize(, UBound(newColumnArray) + 1) = newColumnArray
> > >
> > >
> > > What this does is transfer the whole array to the worksheet in one fell
> > > swoop, as opposed to transferring each element one by one. The speed gain
> > > comes from the fact that your calls to Excel's slow Range object are reduced
> > > from multiple calls, to a single call.
> > > Now take this concept a little further. Instead of creating a 1-dimensional
> > > array of each and every row, then transferring each row separately, you
> > > should create a 2-dimensional array of all rows and columns, and transfer the
> > > whole
> > > array in one hit after EOF has been reached.
> > >
> > > You'll be removing hundreds of thousands of calls to the Range object.
> > > You're in for a nice surprise. It's gonna fly !
> > >
> > >
> > > Regards,
> > > Vic Eldridge
> > >
> > >
> > >
> > > "Bing" wrote:
> > >
> > > > Hi,
> > > >
> > > > I written a macro to read in line by line multiple CSV files of varying
> > > > lengths.
> > > >
> > > > Can anyone suggest a faster way in terms of runtime execution time to speed
> > > > this code up?
> > > >
> > > > ie. I thought of reading in multiple lines in bulk to reduce I/O but VBA
> > > > doesn't seem to have a bulk read mode.
> > > >
> > > > Basically i do the following:
> > > >
> > > > Dim columnArrays as Variant
> > > > Dim newColumnArray as Variant
> > > > .
> > > > .
> > > > Do until EOF(inputFile)
> > > > Line Input #inputFile, inputLine
> > > > columnArray = Split(inputLine, ",")
> > > > ....merge/massage/apply functions to certain columns in columnArray
> > > > and copy into newColumnArray
> > > > for i = 0 to Ubound(newColumnArray)
> > > > aRange.Offset(0,i).Value = newColumnArray(i)
> > > > next i
> > > > set aRnage = aRange.Offset(1,0)
> > > > Loop
> > > >
> > > > Thanks to everyone for their valuable insights.

> Guess i have to use some other method other than split?

I believe so. It appears Split() will only ever return an array of strings.
Here's a Split-free example to have a play with.

Sub ArrayToWorksheet()

Dim InputString As String
Dim vArray(1 To 4) As Variant
Dim Comma1 As Long
Dim Comma2 As Long
Dim Comma3 As Long

InputString = "1,Two,3,Four"

Comma1 = InStr(1, InputString, ",")
Comma2 = InStr(Comma1 + 1, InputString, ",")
Comma3 = InStr(Comma2 + 1, InputString, ",")

vArray(1) = Mid(InputString, 1, Comma1 - 1)
vArray(2) = Mid(InputString, Comma1 + 1, Comma2 - Comma1 - 1)
vArray(3) = Mid(InputString, Comma2 + 1, Comma3 - Comma2 - 1)
vArray(4) = Mid(InputString, Comma3 + 1)

Range("A1:D1") = vArray

End Sub




"Bing" wrote:

> Ok, i got you know.. i think i know where the problem lies.
>
> Dim columns as Variant
> columns = split(inputString, ",") <- i think this returns an array of strings.
> range.resize(,UBound(columns)+1) = columns <- puts in text in the cells
>
> I think i need to keepthe columns array as, like you said, as variant.
>
> Guess i have to use some other method other than split?
>
> "Vic Eldridge" wrote:
>
> > Hi Bing,
> >
> > The only way I could get this process to convert from values to text was to
> > dimension the array as String. Arrays dimmed as Longs,Doubles,Variants all
> > transferred to the worksheet as values. Is that where the problem lies ?
> >
> >
> > Regards,
> > Vic Eldridge
> >
> >
> > "Bing" wrote:
> >
> > > Hi Vic,
> > >
> > > Thanks i tried it.. i got an almost 6x speed improvement. Only 1 thing..
> > > this operation seems to automatically convert numbers as text. So rather
> > > than have to call aRange.PasteSpecial to convert from text back to numbers,
> > > is there a way to do this so that the resize operation treats the data as is,
> > > without converting to text.
> > >
> > > Thanks again.
> > >
> > > "Vic Eldridge" wrote:
> > >
> > > > Hi Bing,
> > > >
> > > > Firstly, try replacing the following code ,
> > > >
> > > > > for i = 0 to Ubound(newColumnArray)
> > > > > aRange.Offset(0,i).Value = newColumnArray(i)
> > > > > next i
> > > >
> > > > with this...
> > > >
> > > > aRange.Resize(, UBound(newColumnArray) + 1) = newColumnArray
> > > >
> > > >
> > > > What this does is transfer the whole array to the worksheet in one fell
> > > > swoop, as opposed to transferring each element one by one. The speed gain
> > > > comes from the fact that your calls to Excel's slow Range object are reduced
> > > > from multiple calls, to a single call.
> > > > Now take this concept a little further. Instead of creating a 1-dimensional
> > > > array of each and every row, then transferring each row separately, you
> > > > should create a 2-dimensional array of all rows and columns, and transfer the
> > > > whole
> > > > array in one hit after EOF has been reached.
> > > >
> > > > You'll be removing hundreds of thousands of calls to the Range object.
> > > > You're in for a nice surprise. It's gonna fly !
> > > >
> > > >
> > > > Regards,
> > > > Vic Eldridge
> > > >
> > > >
> > > >
> > > > "Bing" wrote:
> > > >
> > > > > Hi,
> > > > >
> > > > > I written a macro to read in line by line multiple CSV files of varying
> > > > > lengths.
> > > > >
> > > > > Can anyone suggest a faster way in terms of runtime execution time to speed
> > > > > this code up?
> > > > >
> > > > > ie. I thought of reading in multiple lines in bulk to reduce I/O but VBA
> > > > > doesn't seem to have a bulk read mode.
> > > > >
> > > > > Basically i do the following:
> > > > >
> > > > > Dim columnArrays as Variant
> > > > > Dim newColumnArray as Variant
> > > > > .
> > > > > .
> > > > > Do until EOF(inputFile)
> > > > > Line Input #inputFile, inputLine
> > > > > columnArray = Split(inputLine, ",")
> > > > > ....merge/massage/apply functions to certain columns in columnArray
> > > > > and copy into newColumnArray
> > > > > for i = 0 to Ubound(newColumnArray)
> > > > > aRange.Offset(0,i).Value = newColumnArray(i)
> > > > > next i
> > > > > set aRnage = aRange.Offset(1,0)
> > > > > Loop
> > > > >
> > > > > Thanks to everyone for their valuable insights.

HI Vic,

Eureka! Works great now.. Get a tremendous speed improvement by a factor
of 5... it could even be faster but i do alot of massaging of the data before
resizing.

Thanks for your help buddy! How did you find out that you can resize a
range by using the range as an L-Value with an array as the R-Value. Of all
the documentation i've read, i have not seen reference to this.?

Thanks to all for their input as well!

"Vic Eldridge" wrote:

> > Guess i have to use some other method other than split?
>
> I believe so. It appears Split() will only ever return an array of strings.
> Here's a Split-free example to have a play with.
>
> Sub ArrayToWorksheet()
>
> Dim InputString As String
> Dim vArray(1 To 4) As Variant
> Dim Comma1 As Long
> Dim Comma2 As Long
> Dim Comma3 As Long
>
> InputString = "1,Two,3,Four"
>
> Comma1 = InStr(1, InputString, ",")
> Comma2 = InStr(Comma1 + 1, InputString, ",")
> Comma3 = InStr(Comma2 + 1, InputString, ",")
>
> vArray(1) = Mid(InputString, 1, Comma1 - 1)
> vArray(2) = Mid(InputString, Comma1 + 1, Comma2 - Comma1 - 1)
> vArray(3) = Mid(InputString, Comma2 + 1, Comma3 - Comma2 - 1)
> vArray(4) = Mid(InputString, Comma3 + 1)
>
> Range("A1:D1") = vArray
>
> End Sub
>
>
>
>
> "Bing" wrote:
>
> > Ok, i got you know.. i think i know where the problem lies.
> >
> > Dim columns as Variant
> > columns = split(inputString, ",") <- i think this returns an array of strings.
> > range.resize(,UBound(columns)+1) = columns <- puts in text in the cells
> >
> > I think i need to keepthe columns array as, like you said, as variant.
> >
> > Guess i have to use some other method other than split?
> >
> > "Vic Eldridge" wrote:
> >
> > > Hi Bing,
> > >
> > > The only way I could get this process to convert from values to text was to
> > > dimension the array as String. Arrays dimmed as Longs,Doubles,Variants all
> > > transferred to the worksheet as values. Is that where the problem lies ?
> > >
> > >
> > > Regards,
> > > Vic Eldridge
> > >
> > >
> > > "Bing" wrote:
> > >
> > > > Hi Vic,
> > > >
> > > > Thanks i tried it.. i got an almost 6x speed improvement. Only 1 thing..
> > > > this operation seems to automatically convert numbers as text. So rather
> > > > than have to call aRange.PasteSpecial to convert from text back to numbers,
> > > > is there a way to do this so that the resize operation treats the data as is,
> > > > without converting to text.
> > > >
> > > > Thanks again.
> > > >
> > > > "Vic Eldridge" wrote:
> > > >
> > > > > Hi Bing,
> > > > >
> > > > > Firstly, try replacing the following code ,
> > > > >
> > > > > > for i = 0 to Ubound(newColumnArray)
> > > > > > aRange.Offset(0,i).Value = newColumnArray(i)
> > > > > > next i
> > > > >
> > > > > with this...
> > > > >
> > > > > aRange.Resize(, UBound(newColumnArray) + 1) = newColumnArray
> > > > >
> > > > >
> > > > > What this does is transfer the whole array to the worksheet in one fell
> > > > > swoop, as opposed to transferring each element one by one. The speed gain
> > > > > comes from the fact that your calls to Excel's slow Range object are reduced
> > > > > from multiple calls, to a single call.
> > > > > Now take this concept a little further. Instead of creating a 1-dimensional
> > > > > array of each and every row, then transferring each row separately, you
> > > > > should create a 2-dimensional array of all rows and columns, and transfer the
> > > > > whole
> > > > > array in one hit after EOF has been reached.
> > > > >
> > > > > You'll be removing hundreds of thousands of calls to the Range object.
> > > > > You're in for a nice surprise. It's gonna fly !
> > > > >
> > > > >
> > > > > Regards,
> > > > > Vic Eldridge
> > > > >
> > > > >
> > > > >
> > > > > "Bing" wrote:
> > > > >
> > > > > > Hi,
> > > > > >
> > > > > > I written a macro to read in line by line multiple CSV files of varying
> > > > > > lengths.
> > > > > >
> > > > > > Can anyone suggest a faster way in terms of runtime execution time to speed
> > > > > > this code up?
> > > > > >
> > > > > > ie. I thought of reading in multiple lines in bulk to reduce I/O but VBA
> > > > > > doesn't seem to have a bulk read mode.
> > > > > >
> > > > > > Basically i do the following:
> > > > > >
> > > > > > Dim columnArrays as Variant
> > > > > > Dim newColumnArray as Variant
> > > > > > .
> > > > > > .
> > > > > > Do until EOF(inputFile)
> > > > > > Line Input #inputFile, inputLine
> > > > > > columnArray = Split(inputLine, ",")
> > > > > > ....merge/massage/apply functions to certain columns in columnArray
> > > > > > and copy into newColumnArray
> > > > > > for i = 0 to Ubound(newColumnArray)
> > > > > > aRange.Offset(0,i).Value = newColumnArray(i)
> > > > > > next i
> > > > > > set aRnage = aRange.Offset(1,0)
> > > > > > Loop
> > > > > >
> > > > > > Thanks to everyone for their valuable insights.

Bing wrote:
> Do you know what version of Excel is required for this
> particular methodogly, and what sort of libraries neet to be
installed? (ie.
> it look like Microsoft Jet has to be installed as the "database"
driver")

Jet 4.0 SP8 can be downloaded:

http://support.microsoft.com/default...b;en-us;829558

AFAIK this includes all the files needed to use Jet e.g. the OLE DB
providers for Jet.

ADO ships with MDAC and MDAC 2.8 is also a Microsoft download
(redistributable available):

http://www.microsoft.com/downloads/d...DisplayLang=en

However, it is likely MDAC and Jet is available to you. MDAC ships with
Windows (but don't ask me which version with which version). To the
best of my knowledge, Jet 4.0 ships with Office/Excel version 2000 and
above, Jet 3.51 with Excel95 and Excel95. Jet (including the OLE DB
providers) formerly shipped with MDAC but was removed effective from
MDAC 2.6.

> do you have any ideas how fast a query using ADO on a very large file

> to do things like subtotaling compared to writing vba code to parse
same data
> to do the subtotaling?

This sort of thing is what SQL was invented to do! My experience is
that using ADO and SQL to do such work can be orders of magnitude
faster than doing the same using procedural code. However, your data
must be suitable e.g. text files often do not have a consistent format,
data typing can be problematic, etc. Also, some text manipulations
(e.g. extracting a substring) can be hard to define using SQL alone, so
you may need some post-query processing.

Jamie.

--

> Eureka! Works great now.. Get a tremendous speed improvement by a factor
> of 5... it could even be faster but i do alot of massaging of the data before
> resizing.

Glad it worked out for you. Just remember, when your'e looping so many times
it's important to try and avoid the use of relatively slow objects/methods
such as
Range, Cells, Offset (anything that either requires or returns a Range
object).

If you still need more speed then you would probably have to redesign your app
around a proper database management system. As Jamie mentioned, handling
large amounts of data is exactly what they're designed to do.



> How did you find out that you can resize a range by using the range as...

Same way you did. If I remember rightly it was Alan "Array" Beban who
enlightened me to the technique a number of years ago. Thanks again Alan !


Regards,
Vic Eldridge


"Bing" wrote:

> HI Vic,
>
> Eureka! Works great now.. Get a tremendous speed improvement by a factor
> of 5... it could even be faster but i do alot of massaging of the data before
> resizing.
>
> Thanks for your help buddy! How did you find out that you can resize a
> range by using the range as an L-Value with an array as the R-Value. Of all
> the documentation i've read, i have not seen reference to this.?
>
> Thanks to all for their input as well!
>
> "Vic Eldridge" wrote:
>
> > > Guess i have to use some other method other than split?
> >
> > I believe so. It appears Split() will only ever return an array of strings.
> > Here's a Split-free example to have a play with.
> >
> > Sub ArrayToWorksheet()
> >
> > Dim InputString As String
> > Dim vArray(1 To 4) As Variant
> > Dim Comma1 As Long
> > Dim Comma2 As Long
> > Dim Comma3 As Long
> >
> > InputString = "1,Two,3,Four"
> >
> > Comma1 = InStr(1, InputString, ",")
> > Comma2 = InStr(Comma1 + 1, InputString, ",")
> > Comma3 = InStr(Comma2 + 1, InputString, ",")
> >
> > vArray(1) = Mid(InputString, 1, Comma1 - 1)
> > vArray(2) = Mid(InputString, Comma1 + 1, Comma2 - Comma1 - 1)
> > vArray(3) = Mid(InputString, Comma2 + 1, Comma3 - Comma2 - 1)
> > vArray(4) = Mid(InputString, Comma3 + 1)
> >
> > Range("A1:D1") = vArray
> >
> > End Sub
> >
> >
> >
> >
> > "Bing" wrote:
> >
> > > Ok, i got you know.. i think i know where the problem lies.
> > >
> > > Dim columns as Variant
> > > columns = split(inputString, ",") <- i think this returns an array of strings.
> > > range.resize(,UBound(columns)+1) = columns <- puts in text in the cells
> > >
> > > I think i need to keepthe columns array as, like you said, as variant.
> > >
> > > Guess i have to use some other method other than split?
> > >
> > > "Vic Eldridge" wrote:
> > >
> > > > Hi Bing,
> > > >
> > > > The only way I could get this process to convert from values to text was to
> > > > dimension the array as String. Arrays dimmed as Longs,Doubles,Variants all
> > > > transferred to the worksheet as values. Is that where the problem lies ?
> > > >
> > > >
> > > > Regards,
> > > > Vic Eldridge
> > > >
> > > >
> > > > "Bing" wrote:
> > > >
> > > > > Hi Vic,
> > > > >
> > > > > Thanks i tried it.. i got an almost 6x speed improvement. Only 1 thing..
> > > > > this operation seems to automatically convert numbers as text. So rather
> > > > > than have to call aRange.PasteSpecial to convert from text back to numbers,
> > > > > is there a way to do this so that the resize operation treats the data as is,
> > > > > without converting to text.
> > > > >
> > > > > Thanks again.
> > > > >
> > > > > "Vic Eldridge" wrote:
> > > > >
> > > > > > Hi Bing,
> > > > > >
> > > > > > Firstly, try replacing the following code ,
> > > > > >
> > > > > > > for i = 0 to Ubound(newColumnArray)
> > > > > > > aRange.Offset(0,i).Value = newColumnArray(i)
> > > > > > > next i
> > > > > >
> > > > > > with this...
> > > > > >
> > > > > > aRange.Resize(, UBound(newColumnArray) + 1) = newColumnArray
> > > > > >
> > > > > >
> > > > > > What this does is transfer the whole array to the worksheet in one fell
> > > > > > swoop, as opposed to transferring each element one by one. The speed gain
> > > > > > comes from the fact that your calls to Excel's slow Range object are reduced
> > > > > > from multiple calls, to a single call.
> > > > > > Now take this concept a little further. Instead of creating a 1-dimensional
> > > > > > array of each and every row, then transferring each row separately, you
> > > > > > should create a 2-dimensional array of all rows and columns, and transfer the
> > > > > > whole
> > > > > > array in one hit after EOF has been reached.
> > > > > >
> > > > > > You'll be removing hundreds of thousands of calls to the Range object.
> > > > > > You're in for a nice surprise. It's gonna fly !
> > > > > >
> > > > > >
> > > > > > Regards,
> > > > > > Vic Eldridge
> > > > > >
> > > > > >
> > > > > >
> > > > > > "Bing" wrote:
> > > > > >
> > > > > > > Hi,
> > > > > > >
> > > > > > > I written a macro to read in line by line multiple CSV files of varying
> > > > > > > lengths.
> > > > > > >
> > > > > > > Can anyone suggest a faster way in terms of runtime execution time to speed
> > > > > > > this code up?
> > > > > > >
> > > > > > > ie. I thought of reading in multiple lines in bulk to reduce I/O but VBA
> > > > > > > doesn't seem to have a bulk read mode.
> > > > > > >
> > > > > > > Basically i do the following:
> > > > > > >
> > > > > > > Dim columnArrays as Variant
> > > > > > > Dim newColumnArray as Variant
> > > > > > > .
> > > > > > > .
> > > > > > > Do until EOF(inputFile)
> > > > > > > Line Input #inputFile, inputLine
> > > > > > > columnArray = Split(inputLine, ",")
> > > > > > > ....merge/massage/apply functions to certain columns in columnArray
> > > > > > > and copy into newColumnArray
> > > > > > > for i = 0 to Ubound(newColumnArray)
> > > > > > > aRange.Offset(0,i).Value = newColumnArray(i)
> > > > > > > next i
> > > > > > > set aRnage = aRange.Offset(1,0)
> > > > > > > Loop
> > > > > > >
> > > > > > > Thanks to everyone for their valuable insights.