Solved: How to fast write Excel VBA array contents to a text file!

[agarwaldvk]

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)


[vba]
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
[/vba]


Any suggestions?

Best regards

Deepak

[mancubus]

http://www.cpearson.com/excel/ImpText.aspx

dont know if the procedure by pearson resolves your performance issue but it's a good procedure that i use with small data sets.

btw, how you tried CreateTextFile method of Scripting.FileSystemObject?
see here: http://www.vbaexpress.com/kb/getarticle.php?kb_id=805

[agarwaldvk]

Hi Mancubus


Thanks for your response. No, I didn't know about this post that you referred me to but not long after I posted, I tried a few other things. One of those was along those lines and it seems to work ok.

Here is what I had done :-


[vba]
Sub FetchSourceData_CreateTextFileFromRecordset()
Dim outputTextFile As Object 'Could be declared as a TextStream
Dim fso As New Scripting.FileSystemObject
Dim i As Long, j As Long, file2Write As Long
Dim ubound1 As Long, ubound2 As Long
Dim recordsetDataArray As Variant
Dim strText as String
Set outputTextFile = fso.CreateTextFile("c:\Deepak\MyTextFile.txt", True)

'myRecordSet has been declared as a ADODB.Recordset elsewhere
recordsetDataArray = myRecordSet.GetRows
ubound1 = UBound(recordsetDataArray, 2)
ubound2 = UBound(recordsetDataArray, 1)
For i = 0 To ubound1
strText = ""
For j = 0 To ubound2
strText = strText & recordsetDataArray(j, i) & ","
Next j
strText = Left(strText, (Len(strText) - 1))
outputTextFile.WriteLine strText
Next i
outputTextFile.Close
Set outputTextFile = Nothing
Set fso = Nothing
End Sub
[/vba]

I was able to generate this text file for the recordset that contained a little over 615,000 '11 column' records in 12 seconds after the data was fetched/loaded in to the variant (recordsetDataArray) from the database query output or loaded in to array from an Excel spreadsheet range.


Best regards


Deepak