CODEWITHSUNDEEP
sql-server
Humdum
Humdum

Reputation: 113

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 reading 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,

Upvotes: 1

Views: 1713

Answers (2)

Ross
Ross

Reputation: 11

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

Upvotes: 1

pabdulin
pabdulin

Reputation: 35245

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'.

Upvotes: 0

Related Questions