| Author |
 Topic  |
|
madhivanan
Premature Yak Congratulator
India
22469 Posts |
 Posted - 01/23/2006 : 02:54:27
|
quote:
Originally posted by anglianthon
Hi, I'm new to SQL.
Is this used to export from SQL table to Excel?
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;',
'SELECT * FROM [SheetName$]') select * from SQLServerTable
What if I want to export result of a query (that was run in query analyzer) to Excel?
THank you
This is used to export data from SQL Server to EXCEL
Madhivanan
Failing to plan is Planning to fail |
 |
|
|
madhivanan
Premature Yak Congratulator
India
22469 Posts |
|
|
ziggy
Starting Member
1 Posts |
Posted - 04/25/2006 : 19:50:15
|
Great post! I have been able to export successfully. One question though... If I want to run the script regularly is there something I can add to it to overwrite pre-existing data in the file rather than appending to the end of the file?
Thanks |
|
|
|
madhivanan
Premature Yak Congratulator
India
22469 Posts |
Posted - 04/26/2006 : 01:03:32
|
No. I think you cant overwrite the data in Excel. You can append it. Otherwise export it in other file; Delete Old file; Rename new file to have old name
Madhivanan
Failing to plan is Planning to fail |
|
|
|
kanwarnet
Starting Member
7 Posts |
Posted - 05/02/2006 : 13:18:22
|
Hi all
I am trying to import data from a excel file
Insert into _PATIENTS select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=\\svrsqlLive\Processing\PATIENTS.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')
This is giving me the following error
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].
The same Query on another SQL Server works fine
Both servers have same version(4.0.9025.0) of MSJET40.dll.
Thanks
|
|
|
|
madhivanan
Premature Yak Congratulator
India
22469 Posts |
Posted - 05/03/2006 : 01:43:25
|
Make sure the file path is correct. Also when you run that query the EXCEL file should be closed
Madhivanan
Failing to plan is Planning to fail |
|
|
|
kanwarnet
Starting Member
7 Posts |
Posted - 05/03/2006 : 09:02:46
|
hi Madhivanan
Thanks for replying,I am sure that file is not opened and path is also correct as it is working if i try it on another SQL server. |
|
|
|
madhivanan
Premature Yak Congratulator
India
22469 Posts |
Posted - 05/03/2006 : 10:43:43
|
Which version of EXCEL are you using?
Madhivanan
Failing to plan is Planning to fail |
|
|
|
kanwarnet
Starting Member
7 Posts |
Posted - 05/03/2006 : 11:40:17
|
it is 9.0.2720
|
|
|
|
madhivanan
Premature Yak Congratulator
India
22469 Posts |
Posted - 05/04/2006 : 04:26:17
|
Is it EXCEL 97 or 2000?
Madhivanan
Failing to plan is Planning to fail |
|
|
|
kanwarnet
Starting Member
7 Posts |
Posted - 05/04/2006 : 08:49:54
|
| It is Excel 2000 |
|
|
|
niraligdesai
Starting Member
USA
4 Posts |
Posted - 05/05/2006 : 09:58:42
|
Hello! Madhivanan,
I need your help. I want to transfer data from Excel to SQL Server, and I have following code.
Dim myConnection As System.Data.SqlClient.SqlConnection
Dim myCommand As System.Data.SqlClient.SqlCommand
Dim SQLStmt As String
myConnection = New System.Data.SqlClient.SqlConnection(ConfigurationSettings.AppSettings("PeptideDBConn"))
myConnection.Open()
SQLStmt = "Insert into Imagetable Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=" & ExcelFile.Value & ";HDR=YES','SELECT * FROM [Sheet2$]')"
myCommand = New System.Data.SqlClient.SqlCommand(SQLStmt, myConnection)
myCommand.ExecuteNonQuery()
myConnection.Close()
and I am getting following error.
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 am using Excel 2002 and SQL Server 8.0. Can you please help me out here? I need to get done with this by Monday. Please Help me!!! I'll be very thankful to you.
Thanks,
Nirali Desai
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
Nirali Desai |
|
|
|
niraligdesai
Starting Member
USA
4 Posts |
Posted - 05/05/2006 : 10:02:17
|
Hello! Madhivanan,
I also have created linked server, but what I have to do next. and is it posible without linked server? Please help me....
Thanks,
Nirali Desai
quote:
Originally posted by madhivanan
Is it EXCEL 97 or 2000?
Madhivanan
Failing to plan is Planning to fail
Nirali Desai |
|
|
|
madhivanan
Premature Yak Congratulator
India
22469 Posts |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 05/19/2006 : 06:42:29
|
quote:
Originally posted by MBeaudreau
Do you know if in addition to identifying the excel sheet name you could state which cell to start at?
select *
into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 5.0;HDR=No;IMEX=0;Database=\\seludt2135\c$\documents and settings\selupln\desktop\book2.xls', 'select * from [Sheet1$b35:q50]')
--where f1 <> 'a' |
Edited by - SwePeso on 08/01/2006 02:18:07 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 05/19/2006 : 06:43:58
|
quote:
Originally posted by MBeaudreau
I need to start reading the excel data from a certain point in the excel file. All rows starting at cell B35. If need be I could read the entire excel file, not use headers, search for the value in B35 that I want to start at by using 'WHERE F1 like' but I'd prefer not to.
thanks.
M
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 5.0;HDR=No;IMEX=0;Database=\\seludt2135\c$\documents and settings\selupln\desktop\book2.xls', 'select * from [Sheet1$b35:q50]')
--where f1 <> 'a' |
|
|
|
madhivanan
Premature Yak Congratulator
India
22469 Posts |
|
|
datagod
Starting Member
37 Posts |
Posted - 05/24/2006 : 12:41:16
|
quote:
Originally posted by madhivanan
Make sure the file path is correct. Also when you run that query the EXCEL file should be closed
Madhivanan, you are a life saver...
---
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].
---
I was scratching my head over the mysterious "80004005" error with no luck. Then I decided to read this entire thread and visit all KB articles mentioned. Still no luck. Then I saw this post.
It worked! Thanks!! |
Edited by - datagod on 05/24/2006 12:42:01 |
|
|
|
madhivanan
Premature Yak Congratulator
India
22469 Posts |
Posted - 05/25/2006 : 09:54:03
|
Glad to know that It worked. Thanks for the feedback
Madhivanan
Failing to plan is Planning to fail |
|
|
|
Senjaya
Starting Member
USA
2 Posts |
Posted - 06/02/2006 : 16:30:36
|
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. |
|
|
|
Topic |
|
Export to Excel
