Exporting 10,000+ records to Excel 1

[daughtery]

I am trying to export over 10,000 records to excel using the following code. It runs fine when I have a normal amount of records but when I run the sproc the way it is to run live (over 10,000 records) the program locks up. Is there a maximum number of records that a recordset can hold? Is there any way to fix it?

VB Code:

Private Sub mnuTaxFormApprovalReport_Click()
    On Error GoTo HandleError

 Dim poCatalogContact As TrxObject.clsReports
 Dim lrsTaxFormReportData As ADODB.Recordset
 Set lrsTaxFormReportData = New ADODB.Recordset
 Set poCatalogContact = New TrxObject.clsReports
 Set lrsTaxFormReportData = poCatalogContact.GetTaxFormApprovalReport()

 Dim strBuffer As String
 Dim fld As Field
 Dim objExcel As Excel.Application





strBuffer = "Return Code" & vbTab
   strBuffer = strBuffer & "Tax Return ID" & vbTab
   strBuffer = strBuffer & "Form Description" & vbTab
   strBuffer = strBuffer & "Contact Name" & vbTab
   strBuffer = strBuffer & "Contact #" & vbTab
   strBuffer = strBuffer & "Email" & vbTab
   strBuffer = strBuffer & "Address1" & vbTab
   strBuffer = strBuffer & "Address2" & vbTab
   strBuffer = strBuffer & "State" & vbTab
   strBuffer = strBuffer & "County" & vbTab
   strBuffer = strBuffer & "City" & vbTab
   strBuffer = strBuffer & "Zip" & vbTab
   strBuffer = strBuffer & "First Approval Date" & vbTab
   strBuffer = strBuffer & "Last Approval Date" & vbTab
   strBuffer = strBuffer & "Next Approval Date" & vbCrLf

 ' Copy Recordset to string buffer
 lrsTaxFormReportData.MoveFirst
 Do While Not lrsTaxFormReportData.EOF
    For Each fld In lrsTaxFormReportData.Fields
        strBuffer = strBuffer & fld.Value & vbTab
    Next fld
    If strBuffer <> "" Then
        strBuffer = Left(strBuffer, Len(strBuffer) - 1)
    End If
    strBuffer = strBuffer & vbCrLf
    lrsTaxFormReportData.MoveNext
Loop
' Copy Stringbuffer to clipboard
Clipboard.Clear
Clipboard.SetText strBuffer
' Insert clipboard in excel
Set objExcel = New Excel.Application
objExcel.Visible = True
objExcel.Workbooks.Add
'******************************************************
objExcel.ActiveSheet.Range("A1:O1").Font.Bold = True
'*****************************************************
objExcel.ActiveSheet.Paste
Set lrsTaxFormReportData = Nothing
Exit Sub
HandleError:
   Dim liReturnValue As Integer
   liReturnValue = CentralErrorHandler(Err.Number, "mnuReportsTaxReturn_Click", _
                   "frmMain", "TRACS.vbp")
   If liReturnValue = 0 Then
      Resume
   ElseIf liReturnValue = 1 Then
      Resume Next
   ElseIf liReturnValue = 2 Then
     Exit Sub
   End If
End Sub

My Stored Procedure

CREATE PROCEDURE dbo.cas_TaxApprovalContactRpt
AS		     
SELECT  Catalog.RtnCode, 
        Catalog.TaxRtnID, 
        Catalog.Descript,
        CatalogTaxFormContactInfo.ContactName,
        CatalogTaxFormContactInfo.ContactNum,
        CatalogTaxFormContactInfo.ContactEmail,
        CatalogTaxFormContactInfo.ContactAdd1,
	CatalogTaxFormContactInfo.ContactAdd2,
	CatalogTaxFormContactInfo.ContactState,
	CatalogTaxFormContactInfo.ContactCounty,
	CatalogTaxFormContactInfo.ContactCity,
	CatalogTaxFormContactInfo.ContactZip,
	CatalogTaxFormContactInfo.FirstAppDate,
	CatalogTaxFormContactInfo.LastAppDate,
	CatalogTaxFormContactInfo.NextAppDate
FROM    Catalog
       Inner Join CatalogTaxFormContactInfo 
         on  CatalogTaxFormContactInfo.CatalogID = Catalog.CatalogID
	Order By IsNull(NextAppDate, '30000101') ASC, RtnCode ASC

 

[gmmastros]

>> I am trying to export over 10,000 records to excel using the following code. It runs fine when I have a normal amount of records but when I run the sproc the way it is to run live (over 10,000 records) the program locks up.

Putting 10,000 records in to Excel isn't a good idea. Are you trying to give someone this data, and they requested it in excel format? Can you use a tab delimited file instead?

>> Is there a maximum number of records that a recordset can hold?

No. Well, maybe, but it's a lot bigger than 10,000 or even 100,000 records.

Is there any way to fix it?

Sure.

There are several ways to fix this problem. If this were my project, here's how I would approach it.

First, I would modify the stored procedure so that it accepts a 'start' and 'count' parameter. The start parameter would represent a 'record number to start at' and the count parameter would represent the number of records to return. You would, of course, have to modify the stored procedure to return just the data you wanted. This isn't hard to do. Essentially, you create a table variable that has a column as RowId Integer Identity(1,1) Then, select the records in to the temp table. Then pull out the records Where RowId Between @Start and @Start + @Count.

Then, on the VB side, call the stored procedure with the proper parameters, drop that data in to a sheet of the excel document. Then, call the stored procedure again with different parameters and put the data in to the next sheet of the excel document. This way, you can control the number of records that get added to the excel sheet.

Of course, I still think the best way to do this would be to forget about excel. I mean, think about it. Why do you want the data in excel? To create a graph? To be able to send it to someone else? Regardless of the reason, excel is probably not the best choice to do this.

I hope this helps. Post back if you have any followup questions.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SBerthold]

You are saying that you are trying to copy over 10,000 records, each with possibly 200 to 400 chars, into a single string, and then to the clipboard at once? (10,000 * 400 =)

I would strongly reconsider this, and do it in smaller chunks.

 

[daughtery]

I totally understand that sending this to Excel isn't a good idea. But I have Business Analysts asking for it. The really bad part is that the majority of the records are useless with many null values except for some ID fields. I have recommended allowing some sort of dialog to prompt the user for some filtering values. Hopefully they will come to thier senses but until then I have to try to make miracles happen.

 

[daughtery]

Can someone give me an answer to this question from my BA:


I don’t understand. I can copy 10,000 records and paste them fairly quickly so why would it take that long to export? I know I’m simplifying the issue but could you go into more detail.

 

[Swi]

Using the below method will greatly speed up the transfer of records:

thread222-1190113

Swi

 

[strongm]

You might like to look at the GetString method of an ADO recordset.

Building the string the way you are at the moment, record by record is sloooowww. String operations in VB are not renowned for speed, and you probably didn't realise that everytime you concatenate a string VB actually creates a new string, which is pretty slow, so your code - with 15 columns - is creating 160000 progressively bigger new strings and destroying 159999 old ones. And that is assuming no nulls.

GetString will allow you to avoid the loop and therefore the string contruction (and it'll handle the replacement of Null values as well)

 

[dilettante]

I think an Excel range has a CopyFromRecordset method. Between that and SQL to clean up your data you ought to be home free.

I'm not sure what your stored procedure references. If that isn't Jet SQL there you may have the option of applying Jet anyway connecting to whatever the underlying data is. Then you could use Jet's expression capabilities to weed out Null values and such into something more palatable.

Even running in sandbox mode Jet will allow the functions you'd need:

[tt]SELECT F1, F2, IIf(IsNull(F3), "N/A", F3) As FF3 FROM SomeTable[/tt]

T-SQL has a COALESCE() function for similar purposes I believe.

 

[tsh73]

my thought whould be (second to Strongm) that time is spent on string concatenation.
to verify that you could measure some times (before, after big string creation, after pasting to Excel) on increasing number of records (starting from one what works).

 

[SBerthold]

GetString is indeed nice to use, and like GetRows, I use it on smaller recordsets.
Although much faster than the loop, you may also find the GetString can also be slow, (compared to a direct export to text file via the dbms), depending on the amount of records and columns and data it holds, at least it always has been so for me, whether assigning the results to a string variable or directly to a stream object.

And the fast Jet ISAM method sometimes has it's quirks with Excel files, depending on the format version and the data imported (Using ISAM Text may be an option - very very fast) - so test it thoroughly for the version used.
However, I do not think in the OP the data is being exported from a Jet Mdb.

I always found that if it is possible, exporting the data directly from the dbms to a delimited file seemed for me to be the best method (Jet ISAM Text Export similar to what Swi posted, or for Sql Server the DTS or BCP Utility: For the later, see Forum183).

 

[gmmastros]

You can export this data directly from SQL Server by using BCP. The syntax would be...

Exec Master..xp_cmdshell 'bcp "exec [!]DatabaseName[/!].dbo.cas_TaxApprovalContactRpt" queryout "C:\testing.xls" -c'

Copy/Paste this to a query analyzer window, change the database name, and run it. There should now be a file on the server's C: drive called testing.xls containing your data. I think you will be a bit surprised at how fast SQL Server can export 10,000 + records.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

I should mention that bcp is usually a command line utility. When used as a command line, you'll need to specify the database, username and password (or trusted connection / windows authentication). Also, when used in command line mode, the file would be put on your computer, not the server's. I find it easier to put the BCP command in a batch file and then call the batch file from VB.

Use Notepad to create the batch file, giving it a .bat extension. The batch file would like this this...

bcp "exec [!]DatabaseName[/!].dbo.cas_TaxApprovalContactRpt" queryout "C:\[!]FileName[/!].xls" -c -S[!]ServerName[/!]  -U[!]Username[/!] -P[!]password[/!]

bcp "exec [!]DatabaseName[/!].dbo.cas_TaxApprovalContactRpt" queryout "C:\[!]FileName[/!].xls" -c -S[!]ServerName[/!]  -T

Your vb code would like this this...

Shell "cmd /c [!]batchfilename.bat[/!]"

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[RonRepp]

Why hasn't anyone thought of the query table? I use this constantly to load far more than 10 K records, and it usually only takes a minute at most.

You can figure out the code by recording a macro.

It should look something like this. (I have replaced the Excel SQL string with a standardized SQL)

Public Function GetSql()
    Dim s As String
 
 
 s = "SELECT dbo.CARDITROL_OUTMAIL_SAT.XTeleLink, dbo.CallHist.UserName, dbo.CARDITROL_OUTMAIL_SAT.Fname, dbo.CARDITROL_OUTMAIL_SAT.Lname, " & _
"dbo.CARDITROL_OUTMAIL_SAT.Representative, dbo.CARDITROL_OUTMAIL_SAT.Item1, dbo.CARDITROL_OUTMAIL_SAT.QTY1, dbo.CARDITROL_OUTMAIL_SAT.Price1, " & _
"dbo.CARDITROL_OUTMAIL_SAT.Item2, dbo.CARDITROL_OUTMAIL_SAT.QTY2, dbo.CARDITROL_OUTMAIL_SAT.Price2, dbo.CARDITROL_OUTMAIL_SAT.Item3, " & _
"dbo.CARDITROL_OUTMAIL_SAT.QTY3, dbo.CARDITROL_OUTMAIL_SAT.Price3, dbo.CallHist.ResultCode " & _
"FROM dbo.CARDITROL_OUTMAIL_SAT INNER JOIN " & _
"dbo.CallHist ON dbo.CARDITROL_OUTMAIL_SAT.XTeleLink = dbo.CallHist.XTeleLink WHERE (dbo.CallHist.ResultCode = 4) AND " & _
"(dbo.CallHist.CallDT BETWEEN CONVERT(DATETIME, '" & sDate & " 00:00:00', 102) AND " & _
"CONVERT(DATETIME, '" & eDate & " 23:59:00', 102))"
 
    GetSql = s

End Function

Public Sub GetData()
    SQL = GetSql
    ''Bold text is dependant on your connection.
    With XL.ActiveSheet.QueryTables.Add(Connection:= _
        "ODBC;DRIVER=SQL Server;SERVER=[b]SERVER_NAME[/b];UID=sa;PWD=sa;APP=Microsoft® Query;WSID=[b]COMPTERNAME[/b];DATABASE=[b]DATABASE_NAME[/b];Network=DBMSSOCN" _
        , Destination:=XL.Range("A1"))
        .CommandText = SQL
        .Name = "Carditrol"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh
    End With
End Sub

I hope this helps.




Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

 

[gmmastros]

I could be proved wrong, but I don't think there is any faster way to create an excel document from data within SQL Server than using the BCP method I showed in my most recent post in this thread.

The beauty of the bcp solution is that it is very simple, and very fast.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom