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

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" wrote in message
...
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.

--

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.

--