SELECT CONVERT(xml, BulkColumn, 2) FROM 
    OPENROWSET(Bulk 'C:\Files\Person.xls', SINGLE_BLOB) AS x;

Msg 9403, Level 16, State 1, Line 1
XML parsing: line 0, character 0, unrecognized input signature

INSERT INTO Person
   WITH (KEEPIDENTITY)
   (id, name, sname, yob)
   SELECT *
      FROM  OPENROWSET(BULK 'C:\Files\Person.xls', SINGLE_BLOB) AS x;

Msg 120, Level 15, State 1, Line 1
The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.

SELECT *
FROM  OPENROWSET(BULK 'C:\Files\Person.xls', SINGLE_BLOB) AS x;

quote:

---

Originally posted by tkizer

If the SELECT without the INSERt returns one column, then you can insert one column into four columns. Your INSERT statement has 4 columns.

Or am I missing the point?

---

quote:

---

Originally posted by Peso

It's because XLS files has a propritary format. You use BULK option with SINGLE_CLOB to read complete (bimary) file in one step.
Again, see http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926 how to import data from Excel.

---

SELECT *
      FROM  OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Files\Person.xls;HDR=YES',
'SELECT * FROM [Sheet1$]');

Msg 7308, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for
distributed queries because the provider is configured to run
in single-threaded apartment mode.

quote:

---

Originally posted by AK-85

quote:

---

Originally posted by Peso

It's because XLS files has a propritary format. You use BULK option with SINGLE_CLOB to read complete (bimary) file in one step.
Again, see http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926 how to import data from Excel.

---

Thanks for the help! Out of the discussion I came up to this but it still doesn't work:

SELECT *
      FROM  OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Files\Person.xls;HDR=YES',
'SELECT * FROM [Sheet1$]');

The error message that I get is this:

Msg 7308, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for
distributed queries because the provider is configured to run
in single-threaded apartment mode.

---