SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 HELP-Import excel data to SQL 2005 server
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

geoman
Starting Member

Greece
4 Posts

Posted - 10/03/2012 :  19:28:37  Show Profile  Reply with Quote
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

Malaysia
965 Posts

Posted - 10/03/2012 :  22:25:59  Show Profile  Reply with Quote
maybe u can refer to madhivanan post
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/04/2012 :  00:35:26  Show Profile  Reply with Quote
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/

Go to Top of Page

geoman
Starting Member

Greece
4 Posts

Posted - 10/04/2012 :  12:00:27  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/04/2012 :  12:02:55  Show Profile  Reply with Quote
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/

Go to Top of Page

geoman
Starting Member

Greece
4 Posts

Posted - 10/04/2012 :  15:11:53  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/04/2012 :  16:19:03  Show Profile  Reply with Quote
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/

Go to Top of Page

Bertie
Starting Member

7 Posts

Posted - 10/04/2012 :  18:31:20  Show Profile  Reply with Quote
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! :)

http://msdn.microsoft.com/en-us/library/ms188365.aspx
Go to Top of Page

geoman
Starting Member

Greece
4 Posts

Posted - 10/04/2012 :  19:54:23  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000