Error importing text files larger than 65536 rows

Question

Error importing text files larger than 65536 rows

srathna77

SSCommitted

Points: 1982
More actions
December 24, 2008 at 9:51 am

#203283

Hi.,
I am getting an Error importing text files larger than 65536 rows when i tried to Get Data From Cube In Excel 2003.
is there any possibility to fix this in excel 2003 .and also i want to know the limitation of excel 2007.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

denglishbi SSCrazy Eights Points: 8831 More actions · Answer 1

Kind of confused by the posting with importing data from text file and then getting data from cube in Excel. What are you doing exactly?

With Excel 2003 you are out of luck:

Excel specifications and limits

Feature Maximum limit

Open workbooks Limited by available memory and system resources

Worksheet size 65,536 rows by 256 columns

Column width 255 characters

In Excel 2007 you can have 1M rows.

So what is new in Excel 2007?

Would need more information in regards to what you are trying to do in order to help resolve the issue.

----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com

Ron McCullough SSC Guru Points: 63877 More actions · Answer 2

This may help with data import/export to Excel 2007

http://www.support.microsoft.com/ph/2523

Here is an extract from the above link.

Office 2007 files have a new format and therefore are not supported by the Jet 4.0 OLE DB provider. Specifically, the Microsoft Excel data source and data destination, which uses the underlying Excel connetion manager based on JET, cannot be used with Microsoft Excel 2007 files. Microsoft Office 2007 shipped with a new OLE DB provider, the Office 12 Microsoft Access Engine OLE DB Provider (aka ACE provider). The ACE provider must be used with Microsoft Excel 2007 and Microsoft Access 2007 data files, but ACE supports only Microsoft Office 2007 data. SSIS has not been specifically tested with other data sources that is compatible with ACE, such as Microsoft Sharepoint files, though it is possible to use them with ACE.
To be able to use the ACE provider, one has to pick the ACE OleDB provider, just like any other OleDB provider in SSIS, and not use the availble Excel & Access options.
ACE provider is supported in SQL Server 2005 SP2 and later.

also

http://dougbert.com/blogs/dougbert/archive/2008/06/16/excel-in-integration-services-part-1-of-3-connections-and-components.aspx

Extract from dougbert.com link:

Bob Beauschemin provided us with a nice Excel12 sample package that uses the new ACE OleDB provider to connect to Excel12 files.
Clicking on the link will allow you to down load the Sample package as a zip file.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]