OpenRowSet - Sql server
-
I am using Openrowset function of sql server to read data from excel files.
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=E:\IGM\Files\cis overview.xls;HDR=No;IMEX=1;','SELECT * FROM [CIS Overview$]')
But all columns are not showed by openrowset. below is just sample, the excel file which i am reading having 28 columns, and 27 having no data in it, just heading, and 28 is ignored by Openrowset.
col1 col2 col3
A X
B X
C X
. .
. .Openrowset is not returning col3 in resultset? Its only showing col1, col2. But if i insert some data in col2(any cell), result set will have all three columns. It is ignoring last column if previous has no data? i also include HDR=NO option in query, but no luck? Is their any way to get all columns in result set? any help? regards,
-
I am using Openrowset function of sql server to read data from excel files.
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=E:\IGM\Files\cis overview.xls;HDR=No;IMEX=1;','SELECT * FROM [CIS Overview$]')
But all columns are not showed by openrowset. below is just sample, the excel file which i am reading having 28 columns, and 27 having no data in it, just heading, and 28 is ignored by Openrowset.
col1 col2 col3
A X
B X
C X
. .
. .Openrowset is not returning col3 in resultset? Its only showing col1, col2. But if i insert some data in col2(any cell), result set will have all three columns. It is ignoring last column if previous has no data? i also include HDR=NO option in query, but no luck? Is their any way to get all columns in result set? any help? regards,