Exporting issue to excel 2007

[Assaf Leibovich]

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

 

[Barb Reinhardt]

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.

 

[Assaf Leibovich]

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?

 

[Assaf Leibovich]

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?

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/showthread.php?t=176159

Microsoft Office Help

.