Import/connect Excel Files into SQL Server

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

Import/connect Excel Files into SQL Server

Author

Topic

Yu Ong
Starting Member

3 Posts

Posted - 2010-12-30 : 22:54:59

Hi,

I have a question about SQL, I am trying to connect to an excel file on my desktop to the SQL server so I could open the excel file as a table on my SQL management studio, however I have been unsuccessful in doing so.

These are the codes I tried to use

SELECT * INTO dbo.testAccountUpload FROM OPENDATASOURCE(Microsoft.Jet.OLEDB.4.0,
Data Source=C:\Documents and Settings\Yong\Desktop\testUploadAcc.xls;Extended Properties=Excel 8.0)...[Sheet1$]

SELECT * INTO dbo.testAccountUpload FROM OPENROWSET(Microsoft.Jet.OLEDB.4.0,
Excel 8.0;Database=C:\Documents and Settings\Yong\Desktop\testUploadAcc.xls, [Sheet1$])

SELECT * INTO dbo.testAccountUpload FROM OPENROWSET(Microsoft.Jet.OLEDB.4.0,
Excel 8.0;Database=C:\Documents and Settings\Yong\Desktop\testUploadAcc.xls, SELECT * FROM [Sheet1$])

To get the file testUploadAcc into testAccountUpload. They dont seem to be working. originally, some statements like Microsoft.Jet.OLEDB.4.0 had quotes beside them like - 'Microsoft.Jet.OLEDB.4.0', I had to remove them if not, they will show up RED(as errors) and it wont run. If I remove them, they turn black. But they wont run either, do you know what could be the reason? Thanks so much in advance!

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-31 : 01:22:55

>> if not, they will show up RED(as errors)
They aren't errors - just indicating that it's a string.

try
select 'hello world'

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-12-31 : 04:35:47

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

Madhivanan

Failing to plan is Planning to fail

Yu Ong
Starting Member

3 Posts

Posted - 2011-01-04 : 03:30:12

Thanks alot for the advice, nigelrivett & Madhivanan

I believe I dont have access to load excel files into SQL, thats why the error.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-04 : 03:35:15

Also note that the file should be in Server's location. Otherwise you need to use UNC path

Madhivanan

Failing to plan is Planning to fail

MAG_KU
Starting Member

1 Post

Posted - 2011-01-04 : 04:14:26

hi,

pls check the file permission, excel file permission is read_only..

Mag_ku,
SQL Learner

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-04 : 04:23:45

quote:
Originally posted by MAG_KU

hi,

pls check the file permission, excel file permission is read_only..

Mag_ku,
SQL Learner

Why did you create new account?
Are you the same user as http://www.sqlteam.com/forums/pop_profile.asp?mode=display&id=65334?

Madhivanan

Failing to plan is Planning to fail

MageshkumarM
Yak Posting Veteran

61 Posts

Posted - 2011-01-04 : 04:37:35

Any one look out this

In my point of view the file permission setting status READ_ONLY,
so that why Yu Ong unable to access that excel file..

MAG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources