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) = TrueThen File.Delete(DownloadPath)
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
Fat Claudius
0 Points
1 Post
OLEDB xlsx write row Issues
Jun 29, 2011 08:42 AM|LINK
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>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...
J.Jeyaseelan