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 not all columns are being shown by openrowset.

Below is just sample, the Excel file which I am re开发者_JAVA技巧ading has 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 - why?

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,

Remove the space from your worksheet tab name: "CIS Overview" --> "CIS_Overview" or "CISOverview".

If you know number of columns you can try to specify if in a query to excel list:

'SELECT * FROM [CIS Overview$A:Z]'

here columns goes from 'A' to 'Z'.