CODEWITHSUNDEEP
sqlsql-serverdatabasevb.netexcel
bheltzel
bheltzel

Reputation: 131

Mass data export from SQL Server to Excel in VB

I have some code that works for smaller data sets. I get an 'out of memory' error with the huge data sets I use though (800k rows, 25 columns). I was trying to figure out a way to change this to mass export column by column, or maybe split up sets of rows, instead of the whole thing at once.

Clearly it can't handle that much data. I couldn't figure out how to separate it out some. Any ideas? Thanks!

For Each dt As System.Data.DataTable In ds.Tables
        ' Copy the DataTable to an object array
        Dim rawData(dt.Rows.Count, dt.Columns.Count - 1) As Object

        ' Copy the column names to the first row of the object array
        For col = 0 To dt.Columns.Count - 1
            rawData(0, col) = dt.Columns(col).ColumnName
        Next

        ' Copy the values to the object array
        For col = 0 To dt.Columns.Count - 1
            For row = 0 To dt.Rows.Count - 1
                rawData(row + 1, col) = dt.Rows(row).ItemArray(col)
            Next
        Next

        ' Calculate the final column letter
        Dim finalColLetter As String = String.Empty
        Dim colCharset As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
        Dim colCharsetLen As Integer = colCharset.Length

        If dt.Columns.Count > colCharsetLen Then
            finalColLetter = colCharset.Substring( _
             (dt.Columns.Count - 1) \ colCharsetLen - 1, 1)
        End If

        finalColLetter += colCharset.Substring( _
          (dt.Columns.Count - 1) Mod colCharsetLen, 1)

        ' Fast data export to Excel
        Dim excelRange As String = String.Format("A1:{0}{1}", finalColLetter, dt.Rows.Count + 1)
        excelSheet.Range(excelRange, Type.Missing).Value2 = rawData

        excelSheet = Nothing
    Next

Upvotes: 1

Views: 680

Answers (1)

John C
John C

Reputation: 1978

Is there other code that manipulates the excel spreadsheet? If not, it would probably be faster to just write this out to a plane text file in CSV format. Excel will open the CSV and present it like a normal spreadsheet.

Upvotes: 3

Related Questions