EDN Admin
Well-known member
MSDN Community,
First of all I really appreciate help on this. I have spent years writing VBA code that has got increasingly complicated. For the first time, I came across the error in VBA "Out Of String Space" when dealing with a 200MB CSV file.
To overcome this I copied and pasted all the code over to VB.NET, compiled changed a few errors and ran the new code. The VB.Net takes 15mins, when VBA took 3 mins.
For instance,
Previously, to read the contents of the CSV file in VBA I used the code,
Dim whole_str As String
Open file_path for Input As #1
whole_str = Input$(LOF(1),1)
Close #1
This takes 40 secs to read a 2 million line CSV file. In VB.Net I use the code
Dim whole_str As String
Dim whole_file_str As System.IO.StreamReader = New System.IO.StreamReader(file_path)
whole_file = whole_file_str.ReadToEnd()
<span>This takes nearly 5 mins to complete.
The rest of my calculation is simply taking the String, splitting into a very large array, deleting large parts of it, then ouputting a new CSV. Every facet of my code is slower. I copied the code into Visual Studio 2010 from Office Excel 2007 if this helps
If an experience programmer can point me in the direction of something obvious I might have missed, I would be extremely grateful. I have no formal programming training, just simply what I have managed to pick up over the years.
Once again, thank you for your help.
Chase
View the full article
First of all I really appreciate help on this. I have spent years writing VBA code that has got increasingly complicated. For the first time, I came across the error in VBA "Out Of String Space" when dealing with a 200MB CSV file.
To overcome this I copied and pasted all the code over to VB.NET, compiled changed a few errors and ran the new code. The VB.Net takes 15mins, when VBA took 3 mins.
For instance,
Previously, to read the contents of the CSV file in VBA I used the code,
Dim whole_str As String
Open file_path for Input As #1
whole_str = Input$(LOF(1),1)
Close #1
This takes 40 secs to read a 2 million line CSV file. In VB.Net I use the code
Dim whole_str As String
Dim whole_file_str As System.IO.StreamReader = New System.IO.StreamReader(file_path)
whole_file = whole_file_str.ReadToEnd()
<span>This takes nearly 5 mins to complete.
The rest of my calculation is simply taking the String, splitting into a very large array, deleting large parts of it, then ouputting a new CSV. Every facet of my code is slower. I copied the code into Visual Studio 2010 from Office Excel 2007 if this helps
If an experience programmer can point me in the direction of something obvious I might have missed, I would be extremely grateful. I have no formal programming training, just simply what I have managed to pick up over the years.
Once again, thank you for your help.
Chase
View the full article