OLEDB xlsx write row Issues RSS

• • Reply
  

  Fat Claudius

  0 Points

  1 Post

  OLEDB xlsx write row Issues

  Jun 29, 2011 08:42 AM|LINK

  <div class="container"> <div class="body">

  I am writing some tables in SQL Server 2005 out into xlsx files using OLEDB and the process seems unable to write large files greater than 40000 rows.  I have no idea what is wrong the process doesn't error or fail but it appears that after 40000 rows the OLEDB Command stops inserting rows. 

  There is no problem with csv files, text or even xls files but xlsx is proving a headache.

  Here is my general code

   

  If File.Exists(DownloadPath) = True Then File.Delete(DownloadPath)

  If sFileExtension = ".xls" Then

  xlConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & downloadpath & ";" & "Extended Properties='Excel 8.0;'"

   ElseIf sFileExtension = ".xlsx" Then

  xlConnString ="Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & downloadpath & ";" & "Extended Properties='Excel 12.0 XML;'"

   End

  If

  xlconn = New OleDbConnection(xlConnString)

  xlConn.Open()

   

  sSQLDMLString = "CREATE TABLE [Skipped](" svaluesString1 = "INSERT INTO [Skipped$] ("

  query = String.Format("SELECT * FROM [{0}]", "Skipped$")

   

  For i = 0 To iEndColumnCounter

  sSQLDMLString += "[" & Convert.ToString(writedatatable.Columns(i)) & "]" & " varchar (100),"

  svaluesString1 += "[" + Convert.ToString(writedatatable.Columns(i)) + "],"

  Next

  sSQLDMLString = sSQLDMLString.substring(0, sSQLDMLString.length - 1) + ")"

  svaluesString1 = svaluesString1.substring(0, svaluesString1.length - 1) + ") "

   

  'Create excel sheet

  excelCmd = New OleDbCommand(sSQLDMLString, xlConn)

  excelCmd.ExecuteNonQuery()

   

  For icounter = 0 To iEndRowCounter

  svaluesString2 = "VALUES("

  i = 0

  For i = 0 To iEndColumnCounter

  svaluesString2 += """" & writedatatable.rows(icounter).item(i) + ""","

  Next

  svaluesString2 = svaluesString2.substring(0, svaluesString2.length - 1) + ")"

  svaluesString2 = svaluesString1 + svaluesString2

  excelCmd = New OleDbCommand(svaluesString2, xlConn)

  excelCmd.ExecuteNonQuery()

  Next

   

  I don't know what I'm doing wrong and I really don't know where to stgart looking in the Excel installation.

  Any help or suggestions would be greatly appreciated.

  Thanks

  </div> </div>
• • Reply
  

  jeyaseelan@a...

  Contributor

  5124 Points

  2025 Posts

  Re: OLEDB xlsx write row Issues

  Jun 29, 2011 12:14 PM|LINK

  Fat,

  we can write fourty thounsand rows in excel but unfortunately your server memory was overloaded and failed to write this file, do one thing increase your server virtual memory and RAM.

  else

  try to split the rows by 10,000, then you can write 10000 rows in one file and rest on the another file vice versa...

  Any doubts please feel free to ask me. If this post is answer of your question then don't forgot to Click "Mark As Answer".
  J.Jeyaseelan
  • Marked as answer by Damon Liu - MSFT on Jul 04, 2011 08:51 AM