Reading from a xl sheet - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Reading from a xl sheet

Author

Topic

vishu_av
Yak Posting Veteran

69 Posts

Posted - 2007-07-17 : 08:00:27

Hi All,

I would like to know if i can read data from xl sheet using a stored procedure?

Thanks in advance

vishu

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-17 : 08:36:35

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Peter Larsson
Helsingborg, Sweden

vishu_av
Yak Posting Veteran

69 Posts

Posted - 2007-07-18 : 00:54:12

Hi peso,
I dont intend to create a DTS package or do import/export.
I want to populate my host variables in my stored proc by READING from an excel file.
Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-18 : 02:25:29

quote:
Originally posted by vishu_av

Hi peso,
I dont intend to create a DTS package or do import/export.
I want to populate my host variables in my stored proc by READING from an excel file.
Thanks

Read the link carefully. It is not about DTS. It shows how to do it using sql which you can use it in stored procedure

Madhivanan

Failing to plan is Planning to fail

vishu_av
Yak Posting Veteran

69 Posts

Posted - 2007-07-18 : 06:58:24

Hi
i got the following error, am unable to interprete though,

Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'SELECT * FROM [SheetName$]'. The OLE DB provider 'Microsoft.Jet.OLEDB.4.0' indicates that the object has no columns.
OLE DB error trace [Non-interface error: OLE DB provider unable to process object, since the object has no columnsProviderName='Microsoft.Jet.OLEDB.4.0', Query=SELECT * FROM [SheetName$]'].

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-18 : 07:25:50

Read that thread fully. You can find the solution
You should close the EXCEL file when running that code

Madhivanan

Failing to plan is Planning to fail

vishu_av
Yak Posting Veteran

69 Posts

Posted - 2007-07-18 : 23:49:43

Hi mdhivanan,
I had already checked this. The Excel file was closed.
Inspite of that i got the error.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-19 : 01:37:46

Please post your code relevant to this error.
Notice that the path to the Excel file is relative from the server, not your workstation.

Peter Larsson
Helsingborg, Sweden

vishu_av
Yak Posting Veteran

69 Posts

Posted - 2007-07-20 : 07:47:50

Hi,
Here is the query that i tried to execute.

Insert into temp Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=E:\testing.xls;HDR=YES',
'SELECT * FROM [SheetName$]')

My table temp contain only 2 fileds viz 'ID' and 'DateError' is as below.
correspondingly, i have created a excel file with first row as the column names and consecutive
rows with the data.

Thanks in advance

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-20 : 09:43:09

quote:
Originally posted by vishu_av

Hi,
Here is the query that i tried to execute.

Insert into temp Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=E:\testing.xls;HDR=YES',
'SELECT * FROM [SheetName$]')

My table temp contain only 2 fileds viz 'ID' and 'DateError' is as below.
correspondingly, i have created a excel file with first row as the column names and consecutive
rows with the data.

Thanks in advance

still did you get any error?

Madhivanan

Failing to plan is Planning to fail

vishu_av
Yak Posting Veteran

69 Posts

Posted - 2007-07-24 : 03:17:36

Hi madhivanan,
Ya, thats true.
I still i get this error.

vishu

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-30 : 04:03:12

Make sure the first row should have column names in EXCEL Sheet

Madhivanan

Failing to plan is Planning to fail

vishu_av
Yak Posting Veteran

69 Posts

Posted - 2007-08-07 : 02:37:05

Hi madhivana,
I am pretty sure the first row is the column names of the table in proper order.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-08 : 02:25:01

Run this and see what column names you are getting

Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=E:\testing.xls;HDR=YES',
'SELECT * FROM [SheetName$]')

Madhivanan

Failing to plan is Planning to fail

vishu_av
Yak Posting Veteran

69 Posts

Posted - 2007-08-08 : 23:28:14

Hi Madhivanan,
i still get the following error when i run the above select query

OLE DB error trace [Non-interface error: OLE DB provider unable to process object, since the object has no columnsProviderName='Microsoft.Jet.OLEDB.4.0', Query=SELECT * FROM [SheetName$]'].
Msg 7357, Level 16, State 2, Line 1
Could not process object 'SELECT * FROM [SheetName$]'. The OLE DB provider 'Microsoft.Jet.OLEDB.4.0' indicates that the object has no columns.

vishu

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-09 : 02:57:04

Make sure that the file exists in Server's directly and not at the system you are querying to

Madhivanan

Failing to plan is Planning to fail

vishu_av
Yak Posting Veteran

69 Posts

Posted - 2007-08-09 : 23:14:30

Hi madhivanan,
The file exists on the server where SQL is running.

The format of the excel file is as below.

ID1 ID2 ID3 -----------> this is first row with table columns
10 100 1000 ----------->
20 200 2000 -----------> these 2 line contain some test data.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-10 : 01:31:22

Well. As an alternate, try Linked Server. Read about sp_addLinkedServer in sql server help file

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources