Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exporting issue to excel 2007
Hi,
I'm using ACE provider to extract data to excel 2007 file (xlsx). In my code I'm creating a table and inserting rows into it. The write is working for the first thousands of rows (~16000), but after it reaches some limit the file becomes empty from the new table (worksheet) and from all inserted rows. I searched and found some posts on this, but no solution was provided. Can you assist? Thanks in advance. Assaf |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exporting issue to excel 2007
I don't know if this is it, but if you are dimensioning a variable to track
row #'s, make sure it's dimensioned as LONG. -- HTH, Barb Reinhardt "Assaf Leibovich" wrote: Hi, I'm using ACE provider to extract data to excel 2007 file (xlsx). In my code I'm creating a table and inserting rows into it. The write is working for the first thousands of rows (~16000), but after it reaches some limit the file becomes empty from the new table (worksheet) and from all inserted rows. I searched and found some posts on this, but no solution was provided. Can you assist? Thanks in advance. Assaf |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exporting issue to excel 2007
Usually when there are problems that occur around a power of 2 like 16,384 the problem is associate with the size of the variable used to count the rows. You may have to try a different approach like sav ethe file as CSV format and then read using the same format. -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=176159 Microsoft Office Help |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exporting issue to excel 2007
Thank you for your assistance,
However, my problems doesn't seem to be related to a power of 2 issue, I insert 6 columns of data and my problem occurs after I insert row number 17616. I suspect its related to the amount of data inserted: - If I insert less columns, more rows are inserted - If I insert only a part of the actual string data (first 20 characters) I can insert more rows Any ideas? "joel" wrote: Usually when there are problems that occur around a power of 2 like 16,384 the problem is associate with the size of the variable used to count the rows. You may have to try a different approach like sav ethe file as CSV format and then read using the same format. -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=176159 Microsoft Office Help . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exporting issue to excel 2007
I read a fgew webpages and put my thoughts below. To give a better answer I would need to see what methods you are using to connect and to get the data. Some websites say to go back to the Jet engine instead of using ADO. Others say that ADO has problems reading larger amount of data if you attempt to read too much data at one time. So if you are using a query with ADO you probably have to read data in smaller blocks. I believve there is a block size property that you can use with ADO. I would have to do some research. So the solution would be to create a loop where you would get a record set (which is limited), move data to the worksheet, then get more data until you reach the end of the data. Read this URL 'Excel, the Office 12 ACE Provider, and performance - Dougbert on SSIS - Dougbert.com' (http://tinyurl.com/yekzy2h) I suspect like you said it a memory and and indexing issue. I did some reading on the web and found this webpage 'Methods for transferring data to Excel from Visual Basic' (http://support.microsoft.com/default.aspx/kb/247412) You may need to replace this line from conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _ sNWind & ";" to conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _ sNWind & ";" The 12.0 is the version with Office 2007. I'm not sure but I suspect to use office 2003 you would just change the 12.0 to 11.0. Maybe use the Jet Engine instead of ACE? It seems the jet engine is limited to around 64K. so the answer is probably switch fro Jet engine to ACE. See this URL 'Using oledb microsoft.ace provider to transfer data from .net to excel 2007 VB.NET' (http://tinyurl.com/y8ddmwg) I'm not sure how you are reading the data but you SQL statement may be exceeeding the 64K limit. If you are doing a query and trying to read all the data at once this could be the problem. Instead of doing a query read one line at a time. -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=176159 Microsoft Office Help |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exporting issue to excel 2007
Hi, thanks for your reply.
My code is pretty simple with no bulks inserts - I insert one row at a time. After ~17,000 rows the whole worksheet Tbl1 just disapears. This is how the code looks: using (OleDbConnection mapConnection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0 ;Data Source=c:\\book1.xslx;Extended Properties=Excel 12.0;")) { OleDbCommand mapCommand = new OleDbCommand(); mapCommand.CommandText = @" create table [Tbl1] ( [col1] numeric, [col2] char(20), [col3] char(20), [col4] char(20), [col5] char(20), [col6] char(20), [col7] char(20))"; mapCommand.ExecuteNonQuery(); mapCommand.CommandText = "insert into [Tbl1] ([col1], [col2], [col3], [col4], [col5], [col6], [col7]) values (?, ?, ?, ?, ?, ?, ?)"; OleDbParameterCollection parameters = mapCommand.Parameters; parameters.Add("[col1]", OleDbType.Numeric); parameters.Add("[col2]", OleDbType.VarChar); parameters.Add("[col3]", OleDbType.VarChar); parameters.Add("[col4]", OleDbType.VarChar); parameters.Add("[col5]", OleDbType.VarChar); parameters.Add("[col6]", OleDbType.VarChar); parameters.Add("[col7]", OleDbType.VarChar); // values is a list of X rows foreach (object value in values) { mapCommand.Parameters[0].Value = num1; mapCommand.Parameters[1].Value = str1; mapCommand.Parameters[2].Value = str2; mapCommand.Parameters[3].Value = str3; mapCommand.Parameters[4].Value = str4; mapCommand.Parameters[5].Value = str5; mapCommand.Parameters[6].Value = str6; } mapCommand.ExecuteNonQuery(); } With excel 2003 and the jet provider, more rows were inserted to the table above. Any ideas? "joel" wrote: I read a fgew webpages and put my thoughts below. To give a better answer I would need to see what methods you are using to connect and to get the data. Some websites say to go back to the Jet engine instead of using ADO. Others say that ADO has problems reading larger amount of data if you attempt to read too much data at one time. So if you are using a query with ADO you probably have to read data in smaller blocks. I believve there is a block size property that you can use with ADO. I would have to do some research. So the solution would be to create a loop where you would get a record set (which is limited), move data to the worksheet, then get more data until you reach the end of the data. Read this URL 'Excel, the Office 12 ACE Provider, and performance - Dougbert on SSIS - Dougbert.com' (http://tinyurl.com/yekzy2h) I suspect like you said it a memory and and indexing issue. I did some reading on the web and found this webpage 'Methods for transferring data to Excel from Visual Basic' (http://support.microsoft.com/default.aspx/kb/247412) You may need to replace this line from conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _ sNWind & ";" to conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _ sNWind & ";" The 12.0 is the version with Office 2007. I'm not sure but I suspect to use office 2003 you would just change the 12.0 to 11.0. Maybe use the Jet Engine instead of ACE? It seems the jet engine is limited to around 64K. so the answer is probably switch fro Jet engine to ACE. See this URL 'Using oledb microsoft.ace provider to transfer data from .net to excel 2007 VB.NET' (http://tinyurl.com/y8ddmwg) I'm not sure how you are reading the data but you SQL statement may be exceeeding the 64K limit. If you are doing a query and trying to read all the data at once this could be the problem. Instead of doing a query read one line at a time. -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=176159 Microsoft Office Help . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Exporting Quicken to Excel 2007 | Excel Discussion (Misc queries) | |||
Exporting Quickbooks 2007 to Excel 2007 | Excel Worksheet Functions | |||
Exporting from Access to Excel issue - blank graph view | Setting up and Configuration of Excel | |||
Exporting chart issue | Charts and Charting in Excel | |||
issue with concatonating string exporting to excel through C# | Excel Programming |