May 17, 2011 at 3:12 pm
SORs$A13:M
select * from [SORs$A13:M]
I want to import more than 100,000 rows but openrowset stops at 65523 rows when I try to import data from excel by specifying the row numbers
I have to ignore first 12 rows as they consist of comments.
A13 TO M - as I want it to go till the end.
Please let me know any suggestions.
May 17, 2011 at 4:01 pm
sandeep1188 (5/17/2011)
SORs$A13:Mselect * from [SORs$A13:M]
I want to import more than 100,000 rows but openrowset stops at 65523 rows when I try to import data from excel by specifying the row numbers
I have to ignore first 12 rows as they consist of comments.
A13 TO M - as I want it to go till the end.
Please let me know any suggestions.
Which "Excel Version" are you using in your "Excel Source" component in SSIS?
Excel 2007 introduced worksheets that can hold more than 65523 rows but if you're using the Excel 97-2005 driver you may be hitting the row-limit even though the workbook was created in Excel 2007 or 2010 and contains more rows.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 17, 2011 at 5:27 pm
I do see this provider info in the connection string in the Excel Connection Manager
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=spreadsheet.xls;Extended Properties="Excel 12.0;HDR=YES";
I am using the Excel Source from the tool box and mapping to the Excel 2007 file .
May 17, 2011 at 5:30 pm
The excel version I am using is Microsoft excel 2007
May 17, 2011 at 6:03 pm
Just throwing mud at the wall here...
You may have Excel 2007 installed however I see you're using a .xls extension which is typical for Excel 2003 and before. Try using a .xlsx extension which is designated for Excel 2007 and up and see if that does anything to help the issue.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply