Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
asgast
Posting Yak Master
149 Posts |
 Posted - 2012-08-22 : 12:47:12
|
| Good day everyone,I have the following code: SELECT COUNT(1) FROM OPENROWSET( 'Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:\files\cleaned data 201208.xlsx;HDR=NO', 'SELECT * FROM [Sheet1$] ')dThe count that I'm getting back is 305055, but excel file contains 1M records, there is nothing specific about the line where it ends. There are no empty rows or any strange data.IS there any solution to this problem?My SQL server version is:Microsoft SQL Server 2005 - 9.00.5057.00 (X64) Mar 25 2011 13:33:31 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2) |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-08-22 : 14:50:03
|
are there any columns with intermixed datatypes? try likeSELECT COUNT(1)FROMOPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=D:\files\cleaned data 201208.xlsx;Extended Properties="Excel 12.0 Xml;HDR=NO;IMEX=1";','SELECT * FROM [Sheet1$]')d------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
asgast
Posting Yak Master
149 Posts |
Posted - 2012-08-23 : 03:48:39
|
| I neglected that option, since there is only 1 column and it's text, but it helped. Thank you. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-08-23 : 10:04:27
|
| welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
openrowset from excel, rows are missing