agarwaldvk
06-19-2013, 03:49 PM
Hi Everybody
I need to create a single text file - delimited by either a comma (,) or any other character as chosen by the user - from 'n' number of workbooks. The data is all in the same format. The data set is big - each workbook can contain upto 500,000 records with each record containing 11 columns. There could be as many as 4 workbooks that I may have to read at one go.
I can read all this much of data in an Excel VBA array (including the field and row separators as a part of the individual array element) very very quickly - just under 500,000 records and 11 columns from one (1) workbook in 20 seconds flat. This is acceptable. Reading all of the data say from the 4 workbooks in to the array might take a little over a minute - that also is acceptable. Now having loaded the data in the array, is there a fast way to dump the contents of this array in a text file? This is where I have fallen down.
The idea is to eventually use this text file to upload data in to a table in our database through automation!
I have done a lot of googling but coul find nothing that suggested anything other than trawling through the data set and building a string of the values from a record and writing this string to the text file, repeating this process over the whole dataset. This is unacceptably slow for large data set as mine.
As an aside, if its of any help, I have been able to generate a binary text file rather quickly (10 secs or less for nearly 500,000 records with 11 columns) but obviously you can't open and see the contents of the same as its in binary form and I do not know if database systems like SQL Server 2008R2, Oracle and other relational database systems can import data from binary text files.
This is the code for generating the binary text file (file was about 60 mb)
Sub testing1()
Dim i As Long, j As Double
Dim testArray() As Variant
Dim varRange As Variant
Dim myfilename As String
Dim spreadsheetRange As Range
Dim ubound1 As Long, ubound2 As Long, file2Write As Long
Set spreadsheetRange = Worksheets("Testing").Range(Worksheets("Testing").Cells(1, 1), Worksheets("Testing").Cells(472832, 11))
varRange = spreadsheetRange
ubound1 = UBound(varRange, 1) - 1
ubound2 = UBound(varRange, 2) - 1
ReDim testArray((ubound1), (ubound2)) As Variant
For i = 0 To ubound1
For j = 0 To ubound2
testArray(i, j) = varRange((i + 1), (j + 1)) & ","
Next j
testArray(i, (j - 1)) = Left(testArray(i, (j - 1)), (Len(testArray(i, (j - 1))) - 1)) & vbCrLf
Next i
myfilename = "c:\deepak\TestTextFile_nml.txt"
file2Write = FreeFile()
Open myfilename For Binary Access Write As #file2Write
Put #file2Write, , testArray
Close #file2Write
End Sub
Any suggestions?
Best regards
Deepak
I need to create a single text file - delimited by either a comma (,) or any other character as chosen by the user - from 'n' number of workbooks. The data is all in the same format. The data set is big - each workbook can contain upto 500,000 records with each record containing 11 columns. There could be as many as 4 workbooks that I may have to read at one go.
I can read all this much of data in an Excel VBA array (including the field and row separators as a part of the individual array element) very very quickly - just under 500,000 records and 11 columns from one (1) workbook in 20 seconds flat. This is acceptable. Reading all of the data say from the 4 workbooks in to the array might take a little over a minute - that also is acceptable. Now having loaded the data in the array, is there a fast way to dump the contents of this array in a text file? This is where I have fallen down.
The idea is to eventually use this text file to upload data in to a table in our database through automation!
I have done a lot of googling but coul find nothing that suggested anything other than trawling through the data set and building a string of the values from a record and writing this string to the text file, repeating this process over the whole dataset. This is unacceptably slow for large data set as mine.
As an aside, if its of any help, I have been able to generate a binary text file rather quickly (10 secs or less for nearly 500,000 records with 11 columns) but obviously you can't open and see the contents of the same as its in binary form and I do not know if database systems like SQL Server 2008R2, Oracle and other relational database systems can import data from binary text files.
This is the code for generating the binary text file (file was about 60 mb)
Sub testing1()
Dim i As Long, j As Double
Dim testArray() As Variant
Dim varRange As Variant
Dim myfilename As String
Dim spreadsheetRange As Range
Dim ubound1 As Long, ubound2 As Long, file2Write As Long
Set spreadsheetRange = Worksheets("Testing").Range(Worksheets("Testing").Cells(1, 1), Worksheets("Testing").Cells(472832, 11))
varRange = spreadsheetRange
ubound1 = UBound(varRange, 1) - 1
ubound2 = UBound(varRange, 2) - 1
ReDim testArray((ubound1), (ubound2)) As Variant
For i = 0 To ubound1
For j = 0 To ubound2
testArray(i, j) = varRange((i + 1), (j + 1)) & ","
Next j
testArray(i, (j - 1)) = Left(testArray(i, (j - 1)), (Len(testArray(i, (j - 1))) - 1)) & vbCrLf
Next i
myfilename = "c:\deepak\TestTextFile_nml.txt"
file2Write = FreeFile()
Open myfilename For Binary Access Write As #file2Write
Put #file2Write, , testArray
Close #file2Write
End Sub
Any suggestions?
Best regards
Deepak