Reading an Excel File from SQL Server

Question

Post reply

Reading an Excel File from SQL Server

Kayal

Right there with Babe

Points: 750
More actions
September 15, 2005 at 2:51 am

#88987

Hey guys!
Please help me on this ! I have an Execel file on my local machine... I have to write code to read that Excel file and create a new table in SQL Server and then insert the rows in the XLS to the SQL Server table..
I just tried to read from an Excel file... someone suggested using OPENROWSET , and using the linked server concept...
Nothing seems to work.. Any ideas ????:

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Deni Kusdeni Default port Points: 1457 More actions · Answer 1

Deni Kusdeni

Default port

Points: 1457

September 15, 2005 at 3:23 am

#590046

Why do not try to use DTS?

Kayal Right there with Babe Points: 750 More actions · Answer 2

Well, I want to write it in T-SQL.. If it were DTS, that would have been very easy, but I want to write a procedure to go about it!!!!!!!!

Phil Parkin SSC Guru Points: 246985 More actions · Answer 3

I agree - DTS is the way to go. Why make things harder for yourself??

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Kayal Right there with Babe Points: 750 More actions · Answer 4

Hmmm.. I know DTS is the easiest way.. but please the requirement for me is such that I have to write the code... There is a webpage which has a excel sheet and I have to download that sheet and without any manual interuptions, use code in an ASP page and move that sheet as a table...

Help Someone

homebrew01 SSC Guru Points: 55548 More actions · Answer 5

Are you using ASP.NET, or "old" ASP ?? In ASP.NET, you can call the DTS, so that it's all part of the application. Write the DTS, then save it as a Structured Storage File (SSF). Then that SSF can be invoked from ASP.NET.

Gary Johnson-259336 SSCommitted Points: 1673 More actions · Answer 6

Try this from BOL

SELECT *

FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',

'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 8.0')...xactions

Also note that everything inside the parenthesis can not be set to a variable so if it needs to change you will need to do dynamic sql. You may also have problems with the file location. If you can't see the file by using xp_cmdshell 'dir <filename>' then you won't be able to see it with OpenDataSource either.

Gary Johnson
Sr Database Engineer

Kayal Right there with Babe Points: 750 More actions · Answer 7

Well.. I have to use ASP and ASP.net and Gary, correct me if i am wrong... the xl file need to be in the server for the above query to work right...!

What if the file is in our local machine and also it is Excel 2000.

Thanks Guys for all ur responses

Deni Kusdeni Default port Points: 1457 More actions · Answer 8

Yes, the xls file must in the server, if the xls location in the local machine, of course, you must create script to uploaded xls file to the web server.