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

seoulaja
Starting Member

Indonesia
4 Posts

Posted - 06/28/2010 :  00:41:43  Show Profile  Reply with Quote
i'm sure the Server has read and write permission on that file.

still no luck. and i'm little desperate with this...


quote:
Originally posted by madhivanan

quote:
Originally posted by seoulaja

Hello,

I tried this script in my server machine :


SELECT * 
FROM  
OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=\\It-seouln\D$\Upload_Admedika\Claim_Reimbursement1.xls;HDR=YES;IMEX=1', [Sheet1$]) AS MyTable


I got this error message :

quote:

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 tried another script :


SELECT * 
FROM 
OPENROWSET('MSDASQL', 'Driver={Microsoft Excel Driver (*.xls)};DBQ=\\It-seouln\D$\Upload_Admedika\Claim_Reimbursement1.xls', 'SELECT * FROM [Sheet1$]')


i got this error message :

quote:

[OLE/DB provider returned message: [Microsoft][ODBC Excel Driver] Failure creating file.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ].



but, when I tried on my local machine, those scripts works fine.

then I tried this script :


DECLARE @exists INT
DECLARE @File VARCHAR(200)
SET @File = '\\It-seouln\D$\Upload_Admedika\Claim_Reimbursement1.xls'
EXEC master..xp_fileexist @File, @exists output
SELECT @Exists


the result is : 1 (True)

but, when I tried on my server machine for the same script, the result is : 0 (False)

So I can't access my excel file in my local machine from my server machine.

I'm pretty sure my local and server machine in same network.

can you help me?




Best Regards,

Seoul Nainggolan

"Try not to become a man of success but a man of value."

-- Albert Einstein



They may be in the same network. But make sure that the Server has read and write permission on that file

Madhivanan

Failing to plan is Planning to fail




Best Regards,

Seoul Nainggolan

"Try not to become a man of success but a man of value."

-- Albert Einstein
Go to Top of Page

seoulaja
Starting Member

Indonesia
4 Posts

Posted - 06/29/2010 :  06:10:25  Show Profile  Reply with Quote
i found nice article here : http://knol.google.com/k/surendran-moorthy/export-excel-spreadsheet-into-the/804wqtg9oqmk/2#

my problem solved.

1. create dts package in your database
2. save in your database
3. call the package in your vb code


Sub ExecutePackage(sFileName As String)
'reference from : http://knol.google.com/k/surendran-moorthy/export-excel-spreadsheet-into-the/804wqtg9oqmk/2#
On Error GoTo err_flag
    'Purpose :
    'To Execute a package that has been created in SQL Server DTS with the argument.
   
    Dim oPKG As DTS.Package, oStep As DTS.Step
    Set oPKG = New DTS.Package
    Dim sServer As String, sUsername As String, sPassword As String
    Dim sPackageName As String, sMessage As String
    Dim lErr As Long, sSource As String, sDesc As String
   
    ' Set Parameter Values
    sServer = <your server>
    sUsername = <your username>
    sPassword = <your password>
    sPackageName = <your dts package>
    
    'Load Package
    'for windows authentication login
    'oPKG.LoadFromSQLServer sServer, , , _
    '     DTSSQLStgFlag_UseTrustedConnection, , , , sPackageName
        
    oPKG.LoadFromSQLServer sServer, sUsername, sPassword, _
        DTSSQLStgFlag_Default, , , , sPackageName
   
    oPKG.Connections.Item("Microsoft Excel 97-2000").DataSource = sFileName 
   
     'For i = 1 To oPKG.Connections.Item("Connection 1").Properties.Count
     '   Debug.Print oPKG.Connections.Item("Connection 1").Properties.Item(i).Name & "  --- " & oPKG.Connections.Item("Connection 1").Properties.Item(i).Value
    'Next
   
    ' Set Exec on Main Thread
    For Each oStep In oPKG.Steps
        oStep.ExecuteInMainThread = True
    Next
   
    oPKG.Execute
   
    ' Get Status and Error Message
    For Each oStep In oPKG.Steps
        If oStep.ExecutionResult = DTSStepExecResult_Failure Then
            oStep.GetExecutionErrorInfo lErr, sSource, sDesc
            sMessage = sMessage & "Step """ & oStep.Name & _
                """ Failed" & vbCrLf & _
                vbTab & "Error: " & lErr & vbCrLf & _
                vbTab & "Source: " & sSource & vbCrLf & _
                vbTab & "Description: " & sDesc & vbCrLf & vbCrLf
        Else
            sMessage = sMessage & "Step """ & oStep.Name & _
                """ Succeeded" & vbCrLf & vbCrLf
        End If
    Next
   
    oPKG.UnInitialize
    Set oPKG = Nothing
    
exit_Flag:
    Exit Sub
err_flag:
    MsgBox Err.Description, vbCritical, "Upload Excel!"
    GoTo exit_Flag
   
End Sub



Best Regards,

Seoul Nainggolan

"Try not to become a man of success but a man of value."

-- Albert Einstein
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 07/06/2010 :  08:19:06  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
seoulaja,

Thanks for posting the SSIS solution

Madhivanan

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

tengola
Starting Member

1 Posts

Posted - 08/14/2010 :  19:54:55  Show Profile  Reply with Quote
I would recommend using a tool for the export.
There exists many tools for export to excel.

One that i am using recently is DbTransfer.
It can export and import whole SQL Databases, not just single tabbles.

See http://www.rent-a-developer.de/go/dbtransfer

ca
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 08/17/2010 :  02:01:44  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by tengola

I would recommend using a tool for the export.
There exists many tools for export to excel.

One that i am using recently is DbTransfer.
It can export and import whole SQL Databases, not just single tabbles.

See http://www.rent-a-developer.de/go/dbtransfer

ca


It doesn't seem to be a free tool

Madhivanan

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

sevilla.larry
Starting Member

Philippines
1 Posts

Posted - 08/17/2010 :  03:18:52  Show Profile  Reply with Quote
Hi madhivanan,

I modified your #5.
Added schema and -T in bcp. I'm using 2005 version.

Thanks for your Great help.

create procedure proc_generate_excel_with_columns
(
@db_name varchar(100),
@schm_name varchar(100),
@table_name varchar(100),
@file_name varchar(100)
)
as

--Generate column names as a recordset
declare @columns varchar(8000), @sql varchar(8000), @data_file varchar(100)
select
@columns=coalesce(@columns+',','')+column_name+' as '+column_name
from
information_schema.columns
where
table_name=@table_name
select @columns=''''''+replace(replace(@columns,' as ',''''' as '),',',',''''')

--Create a dummy file to have actual data
select @data_file=substring(@file_name,1,len(@file_name)-charindex('\',reverse(@file_name)))+'\data_file.xls'

--Generate column names in the passed EXCEL file
set @sql='exec master..xp_cmdshell ''bcp " select * from (select '+@columns+') as t" queryout "'+@file_name+'" -c -T'''
exec(@sql)

--Generate data in the dummy file
set @sql='exec master..xp_cmdshell ''bcp "select * from '+@db_name+'.'+@schm_name+'.'+@table_name+'" queryout "'+@data_file+'" -c -T'''
exec(@sql)

--Copy dummy file to passed EXCEL file
set @sql= 'exec master..xp_cmdshell ''type '+@data_file+' >> "'+@file_name+'"'''
exec(@sql)

--Delete dummy file
set @sql= 'exec master..xp_cmdshell ''del '+@data_file+''''
exec(@sql)
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 08/23/2010 :  02:52:55  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Yes. Adding Schema parameter is also a good idea

Madhivanan

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

helen82
Starting Member

USA
1 Posts

Posted - 09/16/2010 :  22:51:39  Show Profile  Reply with Quote
I wonder to know Which version of Excel are you using 97 or 2000?

__________________________
http://www.softwareoutsourcing.biz/services/open-source-development.html

Edited by - helen82 on 09/19/2010 22:18:20
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 09/21/2010 :  10:22:06  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by helen82

I wonder to know Which version of Excel are you using 97 or 2000?

__________________________
http://www.softwareoutsourcing.biz/services/open-source-development.html


The example code posted would work for both the versions of EXCEL

Madhivanan

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

truthseeker
Starting Member

15 Posts

Posted - 09/22/2010 :  08:37:16  Show Profile  Reply with Quote
Hi! I'm new at this and I'm trying to import excel data into a SQL Server 2005 table.

I tried this:

Insert into MyTable (CountryCode)
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\Book1.xls;HDR=YES',
'SELECT [Sheet1$].[A] FROM [Sheet1$]')

But I keep getting this error:

Msg 7354, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" supplied invalid metadata for column "A". The data type is not supported.

Can anyone help me please?
Go to Top of Page

truthseeker
Starting Member

15 Posts

Posted - 09/24/2010 :  08:58:33  Show Profile  Reply with Quote
This is a different question but I hope I get replies for this one, though.

I have this query:


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

And it works fine.

What I want to find out is how to name the columns as the data is imported?

Right now, the columns are named F1, F2, F3, etc. after executing the query. I want them to be named like [Product_Code],[Product_Name],[Price],etc.

Hoping to get a reply soon. Thanks in advance!

Go to Top of Page

mylraghu
Starting Member

3 Posts

Posted - 09/26/2010 :  14:22:48  Show Profile  Reply with Quote
Hi Madhivanan,

I am write this code in my jsp page

EXEC sp_makewebtask
@outputfile = 'd:\testing.xls',
@query = 'Select * from dps_hyd..view4',
@colheaders =1,
@FixedFont=0,@lastupdated=0,@resultstitle='Testing details'

and excuting it from my remote machine. Jsp file is in server machine.

I am getting this error

javax.servlet.ServletException: [Microsoft][ODBC SQL Server Driver][SQL Server]SQL Web Assistant: Could not open the output file.


Can you please guide regarding this issue?

Thank You.

With Regards,

Raghu
Go to Top of Page

mylraghu
Starting Member

3 Posts

Posted - 09/26/2010 :  15:34:22  Show Profile  Reply with Quote
How can I store the output file on my client machine with different name after the export is completed?
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 09/28/2010 :  09:59:55  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by mylraghu

Hi Madhivanan,

I am write this code in my jsp page

EXEC sp_makewebtask
@outputfile = 'd:\testing.xls',
@query = 'Select * from dps_hyd..view4',
@colheaders =1,
@FixedFont=0,@lastupdated=0,@resultstitle='Testing details'

and excuting it from my remote machine. Jsp file is in server machine.

I am getting this error

javax.servlet.ServletException: [Microsoft][ODBC SQL Server Driver][SQL Server]SQL Web Assistant: Could not open the output file.


Can you please guide regarding this issue?

Thank You.

With Regards,

Raghu


Make sure the excel file is in Server's directory

Madhivanan

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

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 09/28/2010 :  10:00:57  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by mylraghu

How can I store the output file on my client machine with different name after the export is completed?


You can use UNC path like \\System_name\directory_name\file_name in the query

Madhivanan

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

mylraghu
Starting Member

3 Posts

Posted - 09/29/2010 :  07:55:53  Show Profile  Reply with Quote
Sir,
Thank you for the support.

Small clarification should I write this in the output parameter. Can you please provide an example for better understanding?
quote:
Originally posted by madhivanan

quote:
Originally posted by mylraghu

How can I store the output file on my client machine with different name after the export is completed?


You can use UNC path like \\System_name\directory_name\file_name in the query

Madhivanan

Failing to plan is Planning to fail

Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 09/29/2010 :  11:01:01  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by mylraghu

Sir,
Thank you for the support.

Small clarification should I write this in the output parameter. Can you please provide an example for better understanding?
quote:
Originally posted by madhivanan

quote:
Originally posted by mylraghu

How can I store the output file on my client machine with different name after the export is completed?


You can use UNC path like \\System_name\directory_name\file_name in the query

Madhivanan

Failing to plan is Planning to fail




Use this code

EXEC sp_makewebtask
@outputfile = '\\System_name\directory_name\testing.xls',
@query = 'Select * from dps_hyd..view4',
@colheaders =1,
@FixedFont=0,@lastupdated=0,@resultstitle='Testing details'


Madhivanan

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

awollf
Starting Member

USA
1 Posts

Posted - 11/06/2010 :  04:11:56  Show Profile  Reply with Quote
I need some help with my SQL Export to Excel.

I have a test_results table that has 1 to 20 records for each Sample. I need to export this data to excel consolidating all records for a specific Sample ID to a single record (one row of data).

To add to the complexity the sample results need to be sorted by Department and sent to different tabs of the excel spreadsheet. There will be one tab for each department.

Test_Results Table
Sample_Id,Department,Test_Name,Test_Result
000001,QUALITY,A,2
000001,QUALITY,C,5
000001,QUALITY,D,7
000001,QUALITY,E,1
000002,ENGINEERING,B,4
000002,ENGINEERING,C,3
000002,ENGINEERING,E,6
000003,QUALITY,B,3
000003,QUALITY,D,4
000003,QUALITY,E,8
000004,ENGINEERING,A,1
000004,ENGINEERING,C,2
000004,ENGINEERING,D,5

The above data needs to be exported to an Excel Worksheet sorted to different tabs for each department:

Excel - Quality Department Tab
Sample_Id,Department,A,B,C,D,E
000001,QUALITY,2,,5,7,1
000003,QUALITY,,3,,4,8

Excel - Engineering Department Tab
Sample_Id,Department,A,B,C,D,E
000002,ENGINEERING,,4,3,,6
000004,ENGINEERING,1,,2,5,

Edited by - awollf on 11/06/2010 11:49:00
Go to Top of Page

prateekfgiet
Starting Member

India
1 Posts

Posted - 11/11/2010 :  02:52:14  Show Profile  Reply with Quote
according to this query

EXEC sp_makewebtask
@outputfile = 'd:\vishal.xls',
@query = 'Select fullname,code from owner.empmaster where empid in (1,2,3)',
@colheaders =1,
@FixedFont=0,@lastupdated=0,@resultstitle='employee fullname'

i am able to create excel file with multiple rows in single sheet,while i want to create multiple sheet and each sheet contain one row result from above query in single excel file.

thanks in advancered3
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 11/11/2010 :  05:29:26  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by awollf

I need some help with my SQL Export to Excel.

I have a test_results table that has 1 to 20 records for each Sample. I need to export this data to excel consolidating all records for a specific Sample ID to a single record (one row of data).

To add to the complexity the sample results need to be sorted by Department and sent to different tabs of the excel spreadsheet. There will be one tab for each department.

Test_Results Table
Sample_Id,Department,Test_Name,Test_Result
000001,QUALITY,A,2
000001,QUALITY,C,5
000001,QUALITY,D,7
000001,QUALITY,E,1
000002,ENGINEERING,B,4
000002,ENGINEERING,C,3
000002,ENGINEERING,E,6
000003,QUALITY,B,3
000003,QUALITY,D,4
000003,QUALITY,E,8
000004,ENGINEERING,A,1
000004,ENGINEERING,C,2
000004,ENGINEERING,D,5

The above data needs to be exported to an Excel Worksheet sorted to different tabs for each department:

Excel - Quality Department Tab
Sample_Id,Department,A,B,C,D,E
000001,QUALITY,2,,5,7,1
000003,QUALITY,,3,,4,8

Excel - Engineering Department Tab
Sample_Id,Department,A,B,C,D,E
000002,ENGINEERING,,4,3,,6
000004,ENGINEERING,1,,2,5,



Refer this
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

Madhivanan

Failing to plan is Planning to fail
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.17 seconds. Powered By: Snitz Forums 2000