none
outofmemory exception w/ FILL RRS feed

  • Question

  • I am reading an XLSX that is about 100MB in size using the following (on XP 32bit):

    connS = "Provider=Microsoft.ACE.OLEDB.12.0;"
    connS &= "Data Source=\inware\data\" & frmConversion.DBCompany & "\" & in_file & ";"
    connS &= "Extended Properties=""Excel 12.0 Xml;HDR=No;"""

    Dim tmpConnection As New System.Data.OleDb.OleDbConnection(connS)
    tmpConnection.Open()

    strSQL = "SELECT * FROM [Sheet1$]"
    Dim da As New System.Data.OleDb.OleDbDataAdapter(strSQL, tmpConnection)
    Dim ds As New DataSet("Workbook")
    Dim dt As DataTable
    Dim drRow As DataRow

    da.Fill(ds, "Sheet1")
    dt = ds.Tables("Sheet1")
    For Each drRow In dt.Rows

    On the FILL command the memory in the task manager gets up to about 1.3GB and then gets an outofmemory exception. Is there a way i can specify this is read-only and read-forward since i am just reading through the XLSX and not moving backwards/forwards or doing any updates? Or, is there a better way to read the XLSX so i don't have to do a file.

    I do not want to use the excel add-on - i just want to read straight through the XLSX file, massage the data i read and then write it to an SQL database.

    THanks.


    darin
    Saturday, April 9, 2011 8:09 PM

Answers

  •  

    johm is correct but the fact that you are running out of memory may mean that it's time to segemt those reads. What are you doing so we will know the best wat to guide you. Br sure to include adequate paranetric (parameters) data in your reply.

    Renee


    Not a C officianado
    • Edited by Renee Culver Sunday, April 10, 2011 12:54 AM
    • Marked as answer by Darin Horton Tuesday, April 12, 2011 10:59 AM
    • Marked as answer by Darin Horton Tuesday, April 12, 2011 10:59 AM
    Sunday, April 10, 2011 12:48 AM

All replies

  • The DataSet is probably too large for 32 bit XP .NET.  (Check the unzipped size of the file.)  Use a VBA method to read and write the file.  You can write the VBA method from VB.NET, execute the method and remove it from the workbook.
    Saturday, April 9, 2011 8:18 PM
  •  

    johm is correct but the fact that you are running out of memory may mean that it's time to segemt those reads. What are you doing so we will know the best wat to guide you. Br sure to include adequate paranetric (parameters) data in your reply.

    Renee


    Not a C officianado
    • Edited by Renee Culver Sunday, April 10, 2011 12:54 AM
    • Marked as answer by Darin Horton Tuesday, April 12, 2011 10:59 AM
    • Marked as answer by Darin Horton Tuesday, April 12, 2011 10:59 AM
    Sunday, April 10, 2011 12:48 AM
  • I am reading an XLS that has data in it, reading each row, massaging the data, and then loading it into a SQL database table. That is all i am doing.
    darin
    Sunday, April 10, 2011 12:51 AM
  • Ok. Segmentation involves in taking only the data that you need and going back on reading more segments until you are finished. What kind of data is it that you are interested in?

    Renee


    Not a C officianado
    Sunday, April 10, 2011 1:02 AM
  • "and then loading it into a SQL database table. "

    Oh then I'd say that segmentation is a necessity. What kind of data are you interested in? Are there likely to be blank fields?

    Renee


    Not a C officianado
    Sunday, April 10, 2011 1:05 AM
  • Hello Darin,

    Thanks for your post.

    JohnWein and Renee's suggestions are helpful. Maybe you could read the records seperately. Here are many code samples about how to manipulate Excel Spreadsheet data in VB.NET. Please check:

    http://social.msdn.microsoft.com/forums/en-US/vbgeneral/thread/d24a3eae-d5a5-41f2-abeb-173dee7999ff

    By the way, this article is about importing Excel Spreadsheet Data into SQL Server Database Table using SqlBulkCopy. Hope it helpful.

    http://www.davidhayden.com/blog/dave/archive/2006/05/31/2976.aspx

    If you have any concerns, please feel free to follow up.

    Have a nice day.

    Best regards


    Liliane Teng [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, April 12, 2011 9:38 AM
  • thanks to everyone. i ended up getting it to work by using the FILL command with starting row count and max records set.
    darin
    Tuesday, April 12, 2011 10:57 AM