HELP-Import excel data to SQL 2005 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

HELP-Import excel data to SQL 2005 server

Author

Topic

geoman
Starting Member

4 Posts

Posted - 2012-10-03 : 19:28:37

Hi guys,

I use MS SQL 2005 server and I want to import excel data into an SQL db.
I am looking into some youtube webinars and the instructors say to right click on the database and then choose import data. The strange thing is that if I right click on my databases, I don't have this option 'import data'. Is this because I don t have some feature installed??

Is there any other way where I can import data from excel??

I found this code, but it doesn't work
SELECT H1, H2 INTO DAS
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\SQL\20120414_DAS.xls', [20120414_DAS$])

Any advice would be helpful.

waterduck
Aged Yak Warrior

982 Posts

Posted - 2012-10-03 : 22:25:59

maybe u can refer to madhivanan post
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-04 : 00:35:26

it may be that you're using sql express and doesnt have export import wizard.

Didnt understand what error you got from OPENROWSET. statement looks good though. can you post exact error message?

or refer this

http://support.microsoft.com/kb/321686

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

geoman
Starting Member

4 Posts

Posted - 2012-10-04 : 12:00:27

Hi Visakh 16,

I have MS SQL Server management studio express.

I want to import some excel columns into my SQL DAS table.

I saw your link. First the error that I get from OPENROWSET is the following:

Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

So how I enable the ad Hoc Distributed Queries?

I tried to use the code from this link in order to enable them:
http://msdn.microsoft.com/en-us/library/ms187569.aspx

sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

but I still get an error

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'sp_configure'.

Any help on these?

BTW,
I didn't expect the import Excel data process to be so complex with my Express version.
Is there any quick way where i can upgrade the express version or do I need to install another version with the import/export wizard?

quote:
Originally posted by visakh16

it may be that you're using sql express and doesnt have export import wizard.

Didnt understand what error you got from OPENROWSET. statement looks good though. can you post exact error message?

or refer this

http://support.microsoft.com/kb/321686

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

G Tzallas

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-04 : 12:02:55

try


EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

geoman
Starting Member

4 Posts

Posted - 2012-10-04 : 15:11:53

Dear Visakh16,

I got this error message now

Msg 15247, Level 16, State 1, Procedure sp_configure, Line 94
User does not have permission to perform this action.
Msg 5812, Level 14, State 1, Line 3
You do not have permission to run the RECONFIGURE statement.
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
The configuration option 'Ad Hoc Distributed Queries' does not exist, or it may be an advanced option.
Msg 5812, Level 14, State 1, Line 2
You do not have permission to run the RECONFIGURE statement.

Thks for your time

quote:
Originally posted by visakh16

try

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

G Tzallas

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-04 : 16:19:03

I dont thin you've permissions to run RDCONFIGURE statement so you might have to take the help of DBA

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Bertie
Starting Member

7 Posts

Posted - 2012-10-04 : 18:31:20

I'm an SQL novice, and I tend to create the table manually, save the spreadsheet as CSV and use BULK INSERT. It's not the most elegant of solutions, but it gets the job done. Only issue I generally find is if the data contains commas or quotations, but I just do a find and replace in excel to change them to {comma}, {quote} and {dquote} then run an equivalent REPLACE statement to reverse it once the data is in the SQL database.

Generally works for me as I work on lots of customers' systems who have varying levels and versions of SQL installed, so going back to basics is the best way to avoid any issues. Although if you're wanting to do this often on the same system then a proper solution as per these more knowledgeable people would probably be better! :)

[url]http://msdn.microsoft.com/en-us/library/ms188365.aspx[/url]

geoman
Starting Member

4 Posts

Posted - 2012-10-04 : 19:54:23

Bertie thank you very much for this, I am gonna give it a try.

Dear Visakh16,

You mean a DB administrator?
Can you think of another way I can do this import into SQL?

I tried also Querying an Excel data source on a linked server

http://support.microsoft.com/kb/306397/EN-US

but I have problem with step 2: ...and then click New linked server.
I can't get this general tab mentioned in step 3.

A window pops up saying
additional information:
cannot show requested dialog...

This route doesn't work either.

quote:
Originally posted by visakh16

I dont thin you've permissions to run RDCONFIGURE statement so you might have to take the help of DBA

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

G Tzallas

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources