Export to Excel

Hi Madhivanan,
I tried this query for last 2 days but couldn't transfer. Now I have given it up. Please tell me can I transfer database from SQL Server 2000 to SQL server Express 2005.

Also Can I have SQL server 2000 and 2005 on the same machine.

Please let me know.
Thanking YOu
Anshu

Hello

I wanted to export the contents of a excel file to sql server table.

I tried the code that u have given , but there is a problem,

After using this query it returns the contents of the file, but if ther first few rows have null data in it then it makes all the entries in that column as null

for example

consider this excel file

field name Q1-91 Q2-91 Q3-91 Q4-91 1991Total................ Q4-04 2004total
field 1
.
.
.
.
.
Field15 8 9 0 8 25
field16
.
.
.
.
Field25


In the above excel file the values in the column "Q1-91" are present after 16 rows.
But when i use the Query


SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D: est.xls;HDR=YES', 'SELECT * FROM [Statement 1$]') Rowset_1


it shows all the values in the column Q1-91 as null

Pls can u tell me why this is happening and what should I do to overcome this problem

Is there ay other method to get the excel file contents?

The same thing is happening when i tried to create package for this whole procedure.

Pls do let me know ur answer for this

thanks

Regards!
Pallavi

Actually i want the data from excel file " d: est.xls" into a table "StatementData"

the columns are the same

I have just tried selecting the records from excel file, but as they are not in proper format.

Pallavi

Happy to know there is a coding for SQL to Excel
however, I fail to run it.
1) testing.xls should be in SQL server or my local pc ?
2) I got this error when I run "
Server: 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."
3) I have installed Access in my local pc

This is my coding :
Server: 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.

Can someone give a little more help on this question. I can extract data out of SQL Server into Excel, but I want to UPDATE the table instead of inserting new.

So if I have the following before values:



key style mon val
1 862 JAN 30000


After

key style mon val
1 862 JAN 30001

So only value I want to update is in the val column.

Cheers, Mark

Madhivanan,

this looks great and I will give it try later, but instead of actually referring to a value 30001, how do I dynamically refer to the value? Do I do something like sheetName$+cell location?

Update T1 Set T1.val=??????? Cell Location? from yourTable T1
inner join
(Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C: esting.xls;HDR=YES',
'SELECT * FROM [sheetName$]')) T2 on T1.key=T2.key

Thanks

I am trying to do something similiar, except the Excel workbook I am using has graphs already set up for the data area.
Is it possible to overwrite a certain cell range instead of appending new data to the spreadsheet?
I am trying to graph perfmon data out of a database, so I know i will always have the same number of rows/collumns.

Any help you can give would be appreciated.

Hi Madhivanan


I am runnig just simple insert from excel file into table...


Insert into process_upload1
Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:csat
etgearAgent wise Parameter wise Raw format 09 apr 07.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')


I am having this following error...


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.


Thnaks in advance..

Hello,
I tried to follow the example code for importing the data from excel to SQL server.
I have the excel file on my production server and a different box for sql. Below is my code
appPath=server.mappath("testdata.xls")
response.write appPath


'declare SQL statement that will query the database
sSQL="Insert into xxxx Select * FROM OPENROWSET('sqloledb','Excel 8.0;Database="& appPath &";HDR=YES;Data Source=xxxxx;','SELECT * FROM [Sheet1$]')"

But when i try to run the asp code it gives me the following error
Microsoft OLE DB Provider for SQL Server error '80040e14'

OLE DB provider "SQLNCLI" for linked server "(null)" returned message "Invalid connection string attribute".

Could anyone please help

Hi there,
Is there a query that will create the excel file and allow you to create the headings through a stored procedure rather than having to create the file and the headings manually?
I have tried the following which creates and empty excel file with no headings. How can I get the headings in so I can use the 'insert into openrowset' command? What I'd like to be able to do is use a cursor to create, open, write and close many excel files based upon the output of query and then email the files to different people. I think if I could get the excel file piece to work then the rest as the say is history.DECLARE @RetCode int ,
@FileSystem int ,
@FileHandle int,
@text varchar(200)
--SELECT @text = 'hello ' + 'Jeff' + char(13) + char(10)
EXECUTE @RetCode = sp_OACreate 'Scripting.FileSystemObject' , @FileSystem OUTPUT
IF (@@ERROR|@RetCode > 0 Or @FileSystem < 0)
RAISERROR ('could not create FileSystemObject',16,1)
EXECUTE @RetCode = sp_OAMethod @FileSystem , 'OpenTextFile' , @FileHandle OUTPUT , 'c:jeff.xls', 2, 1
IF (@@ERROR|@RetCode > 0 Or @FileHandle < 0)
RAISERROR ('Could not open File.',16,1)
Thanks,
Jeff