Importing Data from Spread Sheet

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

SQL Server 2005 Forums

Transact-SQL (2005)

Importing Data from Spread Sheet

Author

Topic

victory_vishnu
Starting Member

8 Posts

Posted - 2008-02-15 : 08:11:46

Can any one please let me know how i can import data from Excel Sheet into a table ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-15 : 08:14:10

Many ways
You do this by using bcp or Bulk Insert or using DTS/SSIS Export/Import wizards

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

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-15 : 08:15:42

Check this out for discussion of many different approaches to do this:
http://support.microsoft.com/kb/321686

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-15 : 08:31:13

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

Madhivanan

Failing to plan is Planning to fail

victory_vishnu
Starting Member

8 Posts

Posted - 2008-02-20 : 04:57:31

thanks for all ur inputs guys -

Posted - 05/25/2005 : 01:06:42
--------------------------------------------------------------------------------

bcp is usually used to export data to text file
Did you mean this?

Exec Master..xp_cmdshell 'bcp "Select * from myTable" queryout "C:\testing.xls" -c'

Madhivanan

when i try this : i get theis error , can u tell me why and what i should be doing ...?
I dont want to configure things in the server level , how can i modify the access in the Database level?

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell',

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-20 : 05:07:47

This is server level option which you can't override unless you have admin access.

You can use bcp from command-line as well which don't require playing around with xp_cmdshell.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-20 : 05:08:46

It seems like you are using the extended stored procedure xp_sendmail to do the export. Its disabled by default and needs to be enabled in surface area configuration manager before using. thats what error specifies.

victory_vishnu
Starting Member

8 Posts

Posted - 2008-02-20 : 06:40:05

Guys -
i created a Linked Server called TEST

NOw i try to import the data from the Linked Excel Sheet , using

SELECT * INTO XLImport1 FROM EXCELLINK...[Customers$]

but it still gives me this error :

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "TEST" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "TEST".

any idea why?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources