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
 Script Library
 Export to Excel
 New Topic  Reply to Topic
 Printer Friendly
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 30

may_cwn
Starting Member

2 Posts

Posted - 09/24/2007 :  06:31:40  Show Profile  Reply with Quote
Sorry, I am a newer to SQL server. I would like to know where I should put below script to export data from Excel to new SQL Server table?

select *
into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')

May

quote:
Originally posted by madhivanan

Apart from using DTS and Export wizard, we can also use this query to export data from SQL Server2000 to Excel

Create an Excel file named testing having the headers same as that of table columns and use this query

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;',
'SELECT * FROM [SheetName$]') select * from SQLServerTable

To export data from Excel to new SQL Server table,

select *
into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')

To export data from Excel to existing SQL Server table,

Insert into SQLServerTable Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;HDR=YES',
'SELECT * FROM [SheetName$]')



Madhivanan

Failing to plan is Planning to fail

Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 09/24/2007 :  08:13:41  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by may_cwn

Sorry, I am a newer to SQL server. I would like to know where I should put below script to export data from Excel to new SQL Server table?

select *
into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')

May

quote:
Originally posted by madhivanan

Apart from using DTS and Export wizard, we can also use this query to export data from SQL Server2000 to Excel

Create an Excel file named testing having the headers same as that of table columns and use this query

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;',
'SELECT * FROM [SheetName$]') select * from SQLServerTable

To export data from Excel to new SQL Server table,

select *
into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')

To export data from Excel to existing SQL Server table,

Insert into SQLServerTable Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;HDR=YES',
'SELECT * FROM [SheetName$]')



Madhivanan

Failing to plan is Planning to fail




Run that query using Query Analyser

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

may_cwn
Starting Member

2 Posts

Posted - 09/24/2007 :  21:58:46  Show Profile  Reply with Quote
Thank you!

And I got this error after execute.

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: Unspecified error]

What should I do?

May

quote:
Originally posted by madhivanan

quote:
Originally posted by may_cwn

Sorry, I am a newer to SQL server. I would like to know where I should put below script to export data from Excel to new SQL Server table?

select *
into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')

May

quote:
Originally posted by madhivanan

Apart from using DTS and Export wizard, we can also use this query to export data from SQL Server2000 to Excel

Create an Excel file named testing having the headers same as that of table columns and use this query

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;',
'SELECT * FROM [SheetName$]') select * from SQLServerTable

To export data from Excel to new SQL Server table,

select *
into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')

To export data from Excel to existing SQL Server table,

Insert into SQLServerTable Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;HDR=YES',
'SELECT * FROM [SheetName$]')



Madhivanan

Failing to plan is Planning to fail




Run that query using Query Analyser

Madhivanan

Failing to plan is Planning to fail

Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 09/25/2007 :  01:50:59  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Make sure the file exists in the server's directory and it is closed when you run the query


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

albertkohl
Aged Yak Warrior

USA
740 Posts

Posted - 09/27/2007 :  00:09:40  Show Profile  Visit albertkohl's Homepage  Reply with Quote
Madhivanan, nice thread!

quick question though, is there a trick to creating a NEW excel file? or do you have to have an already existing one?

i'm running sql2k5

code:

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;Database=c:\testing.xls;HDR=no', 
'SELECT * FROM [Sheet1$]') select * from process_temp


i would like to dump it to a new file though. not one that already exists
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 09/27/2007 :  01:47:35  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
I think you can refer here
http://sqlteam.com/forums/topic.asp?TOPIC_ID=90075

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17587 Posts

Posted - 09/27/2007 :  02:07:14  Show Profile  Reply with Quote
quote:
Originally posted by madhivanan

I think you can refer here
http://sqlteam.com/forums/topic.asp?TOPIC_ID=90075

Madhivanan

Failing to plan is Planning to fail



That's albertkohl same question on the other thread


KH
Time is always against us

Go to Top of Page

albertkohl
Aged Yak Warrior

USA
740 Posts

Posted - 09/27/2007 :  02:14:52  Show Profile  Visit albertkohl's Homepage  Reply with Quote
I know, when i original found the post (this one) it looked like it was old, so i posted a new thread. then i noticed this was at the top of this list, so i figured what the heck, and put a reply in here



sorry for the redundance, i planned on looking at the link you posted tomorrow. i'm a little burnt for tonight!

Thanks again guys!
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17587 Posts

Posted - 09/27/2007 :  04:25:04  Show Profile  Reply with Quote
quote:
Originally posted by albertkohl

I know, when i original found the post (this one) it looked like it was old, so i posted a new thread. then i noticed this was at the top of this list, so i figured what the heck, and put a reply in here



sorry for the redundance, i planned on looking at the link you posted tomorrow. i'm a little burnt for tonight!

Thanks again guys!


It may be old. But it is sure the most popular thread ever

Redundancy is always good but cross posting may not


KH
Time is always against us

Go to Top of Page

vbx
Starting Member

USA
38 Posts

Posted - 10/09/2007 :  00:36:38  Show Profile  Visit vbx's Homepage  Reply with Quote
It looks like I may have an unique issue. I have a db on a server hosted by Crystal tech.

I am using the below code. ( Which does not point to my db only my table)

Insert into cfb_boxscores Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;Database=C:\CFB Update 2007.xls;HDR=YES', 
'SELECT * FROM [SQL$]')


How do I make it point to my db called boxscores and then my tables?

also I am getting this error msg.
quote:
Msg 7415, Level 16, State 1, Line 1
Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server.


I have not asked the hosting company yet. But for some reason, I can not create a linked server.

any suggestions.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 10/09/2007 :  03:29:48  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Try


Insert into boxscores..cfb_boxscores Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;Database=C:\CFB Update 2007.xls;HDR=YES', 
'SELECT * FROM [SQL$]'


Madhivanan

Failing to plan is Planning to fail

Edited by - madhivanan on 10/09/2007 03:45:39
Go to Top of Page

vbx
Starting Member

USA
38 Posts

Posted - 10/09/2007 :  09:22:54  Show Profile  Visit vbx's Homepage  Reply with Quote
Thanks for the support. I got this error
quote:
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'SELECT * FROM [SQL$]'.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 10/10/2007 :  01:58:26  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by vbx

Thanks for the support. I got this error
quote:
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'SELECT * FROM [SQL$]'.



Well. I missed out last bracket. Try this

Insert into boxscores..cfb_boxscores Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;Database=C:\CFB Update 2007.xls;HDR=YES', 
'SELECT * FROM [SQL$]')


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

vbx
Starting Member

USA
38 Posts

Posted - 10/10/2007 :  09:00:15  Show Profile  Visit vbx's Homepage  Reply with Quote
Thanks for your help!!
Here is what I got this time.
quote:
Msg 7415, Level 16, State 1, Line 1
Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 10/11/2007 :  02:43:08  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Try using Linked Server. Read about sp_addLinkedServer in sql server help file
If you read all the pages of this thread you can find the answer

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

riho92
Starting Member

1 Posts

Posted - 10/22/2007 :  05:10:40  Show Profile  Reply with Quote
I need something for exporting one table (view) from MS SQL Database to MS Jet Database. This export (renewing) I need once in day. I am trying resolving this problem with SQL stored procedure and SQL Server Agent. I have stored procedure for exporting data from SQL Database to other SQL Database, but I can’t create procedure for export to MS Jet. Maybe its possible with ‘insert into OPENROWSET’.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 10/22/2007 :  06:24:32  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by riho92

I need something for exporting one table (view) from MS SQL Database to MS Jet Database. This export (renewing) I need once in day. I am trying resolving this problem with SQL stored procedure and SQL Server Agent. I have stored procedure for exporting data from SQL Database to other SQL Database, but I can’t create procedure for export to MS Jet. Maybe its possible with ‘insert into OPENROWSET’.


You can use the same OPENROWSET to add data to ACCESS tables.
See more examples in the sql server help file

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

vishu_av
Yak Posting Veteran

69 Posts

Posted - 10/25/2007 :  02:08:00  Show Profile  Reply with Quote
Hi all,
i have a table as below
create table test
(
id numeric not null,
Dated datetime
)
i have created a excel sheet with two columns in first row as id and Dated as columns

i get the following error:

Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'SELECT id,Dated FROM [SheetName1$]'. The OLE DB provider 'Microsoft.Jet.OLEDB.4.0' indicates that the object has no columns.
OLE DB error trace [Non-interface error: OLE DB provider unable to process object, since the object has no columnsProviderName='Microsoft.Jet.OLEDB.4.0', Query=SELECT id,Dated FROM [SheetName1$]'].

Please let me know the reason...
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 10/25/2007 :  04:50:39  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by vishu_av

Hi all,
i have a table as below
create table test
(
id numeric not null,
Dated datetime
)
i have created a excel sheet with two columns in first row as id and Dated as columns

i get the following error:

Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'SELECT id,Dated FROM [SheetName1$]'. The OLE DB provider 'Microsoft.Jet.OLEDB.4.0' indicates that the object has no columns.
OLE DB error trace [Non-interface error: OLE DB provider unable to process object, since the object has no columnsProviderName='Microsoft.Jet.OLEDB.4.0', Query=SELECT id,Dated FROM [SheetName1$]'].

Please let me know the reason...



Can you post the exact query you used?
Also make sure the column names are correct in EXCEL file

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

pshafae
Starting Member

1 Posts

Posted - 11/12/2007 :  01:45:09  Show Profile  Reply with Quote
Senjaya/All,

I had similar problems with number type not being honored when exporting rows from SQL Server view to Excel. All integer and float columns were formated as text in Excel. However, the values did *not* have the ' in front. The problem persisted despite setting all the cells in the columns to Number type.

After some experimentation, I discovered that the values in the header column were causing ambiguity in selecting the type. My target Excel sheet had a header column with only text (as expected). I added second row of values, except these were mock data in the correct type. This fixed the problem and Excel no longer complained of "Number stored as Text".

I know this thread is cold, but I felt compelled to reply since I did not come across a another like solution on a light survey of Google results. I also want to thank madhivanan for all the posts - they have been very helpful.


quote:
Originally posted by Senjaya

Hi,
I want to know if I export from SQL to Excel 2003 - All the number become text and even I try to format the excel file first still did not help....I have a pivot table in Excel file to generate a graph and this text makes it imposible.



J Shafaee
Go to Top of Page
Page: of 30 Previous Topic Topic Next Topic  
Previous Page | Next Page
 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.14 seconds. Powered By: Snitz Forums 2000