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

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 03/03/2010 :  01:30:04  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by badpupsd

I am using -->
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\Reports\Morning\Confirmed-0301.xls;HDR=NO',
'SELECT * FROM [Sheet1$]')

I want to use a varable for the file name.

I've tried --> set @TO = "c:\Reports\Morning\Confirmed-" + @Prepend + ".xls" <--
modifying the above 2nd row to --> 'Excel 8.0;Database='+ @TO +';HDR=NO', <--

@TO defined as varchar(100)
(i've also tried defining @TO sysname

It doesn't seem to matter what i do as it doesn't evaluate correctly

My 2nd problem is that dates are not formatted as i want them (they are apparently passed as txt??)
I want them to be --> 3/1/10 08:56 AM <--
I am writing to a formatted .xls sheet so would expect the columns to be formatted correctly.

thanks for any assistance.



Try this

declare @sql varchar(1000),@filename varchar(200)
set @filename='c:\Reports\Morning\Confirmed-0301.xls'
set @sql='insert into OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',
''Excel 8.0;Database='+@filename+';HDR=NO'',
''SELECT * FROM [Sheet1$]'')'
exec(@sql)


Madhivanan

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

badpupsd
Starting Member

USA
8 Posts

Posted - 03/03/2010 :  14:55:17  Show Profile  Reply with Quote
Madhivanan

Thank you !!!!
Go to Top of Page

badpupsd
Starting Member

USA
8 Posts

Posted - 03/03/2010 :  14:58:57  Show Profile  Reply with Quote
One more hang up.

I'm using -->
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\Reports\Morning\TrackingReport.xls;HDR=NO',
'SELECT * FROM [Sheet1$]')
<--

I have one report that has
header (with merged cells) on Rows 1 and 2
My column headers are on Row 3

I want my data to fill at row 4, The data comes from 3 different selects.

1st one needs to go column 1, row 4
2nd one needs to go columns 3 - 7, row4
3rd one needs to go columns 9-13, row 4

I swear i saw something about column/row earlier in this thread, but can't seem to find it now.

thanks in advance.


Edited by - badpupsd on 03/03/2010 15:06:03
Go to Top of Page

sathiesh2005
Yak Posting Veteran

India
85 Posts

Posted - 03/04/2010 :  02:13:12  Show Profile  Reply with Quote
Hi Madhivanan,

This is Sathiesh, few days before i saw your thread, It was very useful, works fine & fulfilled my requirement.
Here, I am download the data to an excel and send this as attachment in email. Now i am using two different file for this. I need to send as single attachment.
Is it possible to export data to sheet1 and sheet2?

I am using the below example written by you.

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

Expecting your kind reply.
Thanks in advance.

Regards,
Sathieshkumar. R
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 03/05/2010 :  08:08:41  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by badpupsd

One more hang up.

I'm using -->
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\Reports\Morning\TrackingReport.xls;HDR=NO',
'SELECT * FROM [Sheet1$]')
<--

I have one report that has
header (with merged cells) on Rows 1 and 2
My column headers are on Row 3

I want my data to fill at row 4, The data comes from 3 different selects.

1st one needs to go column 1, row 4
2nd one needs to go columns 3 - 7, row4
3rd one needs to go columns 9-13, row 4

I swear i saw something about column/row earlier in this thread, but can't seem to find it now.

thanks in advance.




In this topic, search for Named Range

Madhivanan

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

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 03/05/2010 :  08:20:59  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by sathiesh2005

Hi Madhivanan,

This is Sathiesh, few days before i saw your thread, It was very useful, works fine & fulfilled my requirement.
Here, I am download the data to an excel and send this as attachment in email. Now i am using two different file for this. I need to send as single attachment.
Is it possible to export data to sheet1 and sheet2?

I am using the below example written by you.

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

Expecting your kind reply.
Thanks in advance.

Regards,
Sathieshkumar. R



I dont think you can create two sheets for the same filename using sp_makewebtask.
Instead make use of the method 1 where you can specify the sheet names

Madhivanan

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

badpupsd
Starting Member

USA
8 Posts

Posted - 03/05/2010 :  11:10:01  Show Profile  Reply with Quote
quote:
Originally posted by madhivanan

quote:
Originally posted by badpupsd

One more hang up.

I'm using -->
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\Reports\Morning\TrackingReport.xls;HDR=NO',
'SELECT * FROM [Sheet1$]')
<--

I have one report that has
header (with merged cells) on Rows 1 and 2
My column headers are on Row 3

I want my data to fill at row 4, The data comes from 3 different selects.

1st one needs to go column 1, row 4
2nd one needs to go columns 3 - 7, row4
3rd one needs to go columns 9-13, row 4

I swear i saw something about column/row earlier in this thread, but can't seem to find it now.

thanks in advance.




In this topic, search for Named Range

Madhivanan

Failing to plan is Planning to fail




Awesome Madhivanan
You Rock
That did exactly what i needed.
Thank you so very much.


Edited by - badpupsd on 03/05/2010 11:10:52
Go to Top of Page

sathiesh2005
Yak Posting Veteran

India
85 Posts

Posted - 03/09/2010 :  05:00:28  Show Profile  Reply with Quote
quote:
Originally posted by madhivanan

quote:
Originally posted by sathiesh2005

Hi Madhivanan,

This is Sathiesh, few days before i saw your thread, It was very useful, works fine & fulfilled my requirement.
Here, I am download the data to an excel and send this as attachment in email. Now i am using two different file for this. I need to send as single attachment.
Is it possible to export data to sheet1 and sheet2?

I am using the below example written by you.

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

Expecting your kind reply.
Thanks in advance.

Regards,
Sathieshkumar. R



I dont think you can create two sheets for the same filename using sp_makewebtask.
Instead make use of the method 1 where you can specify the sheet names

Madhivanan

Failing to plan is Planning to fail



Hi Madhi,
Thank you very much. I will try.

Regards,
Sathieshkumar. R
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 03/09/2010 :  05:31:15  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by badpupsd

quote:
Originally posted by madhivanan

quote:
Originally posted by badpupsd

One more hang up.

I'm using -->
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\Reports\Morning\TrackingReport.xls;HDR=NO',
'SELECT * FROM [Sheet1$]')
<--

I have one report that has
header (with merged cells) on Rows 1 and 2
My column headers are on Row 3

I want my data to fill at row 4, The data comes from 3 different selects.

1st one needs to go column 1, row 4
2nd one needs to go columns 3 - 7, row4
3rd one needs to go columns 9-13, row 4

I swear i saw something about column/row earlier in this thread, but can't seem to find it now.

thanks in advance.




In this topic, search for Named Range

Madhivanan

Failing to plan is Planning to fail




Awesome Madhivanan
You Rock
That did exactly what i needed.
Thank you so very much.




You are welcome and Thanks for the feedback

Madhivanan

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

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 03/09/2010 :  05:33:39  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by sathiesh2005

quote:
Originally posted by madhivanan

quote:
Originally posted by sathiesh2005

Hi Madhivanan,

This is Sathiesh, few days before i saw your thread, It was very useful, works fine & fulfilled my requirement.
Here, I am download the data to an excel and send this as attachment in email. Now i am using two different file for this. I need to send as single attachment.
Is it possible to export data to sheet1 and sheet2?

I am using the below example written by you.

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

Expecting your kind reply.
Thanks in advance.

Regards,
Sathieshkumar. R



I dont think you can create two sheets for the same filename using sp_makewebtask.
Instead make use of the method 1 where you can specify the sheet names

Madhivanan

Failing to plan is Planning to fail



Hi Madhi,
Thank you very much. I will try.

Regards,
Sathieshkumar. R


Ok. Let me know if it works well

Madhivanan

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

dpaez01
Starting Member

4 Posts

Posted - 03/12/2010 :  10:39:58  Show Profile  Reply with Quote
Hi madhivanan

I was using a very similar code since a year ago and it´s work perfect.

That code work to export data from sql server 2005 to Excel 2003. Now i´m doing the update to excel 2007.

I using this code:

SET @SECUENCIA= 'INSERT INTO OPENROWSET (''Microsoft.ACE.OLEDB.12.0'',''Excel 12.0;Database='+@CMD+' ;'',''SELECT * FROM [Sheet1$]'')
SELECT REGISTRO, NB_ANALISTA, NUM_SINIESTRO, CIUDAD, OFERTANTE, MARCA, MODELO,
ANIO, PIEZA, FE_ENT_REP, COSTO, CALIDAD, DISPONIBILIDAD
FROM TEMPORAL_PROVEE_GANAD'

EXEC(@SECUENCIA)

where @cmd are the full path to the .xlsx file.

The problem is that i made this to generate a group of 30 reports, every report have 10 sheets, and i´m having the next error:

Msg 7357, Level 16, State 2, Line 1
Cannot process the object "SELECT * FROM [Sheet1$]". The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.

The innusual is that this code works fine to generates some reports and fail to others (i mean that the code are doing what i expect to do)

I already sheck the name of the Sheet, the long of the path, and a lot of things. This is why i´m locking for help.

Thanks..


Edited by - dpaez01 on 03/12/2010 10:46:29
Go to Top of Page

dpaez01
Starting Member

4 Posts

Posted - 03/15/2010 :  14:45:00  Show Profile  Reply with Quote
Hi madhivanan

I was using a very similar code since a year ago and it´s work perfect.

That code work to export data from sql server 2005 to Excel 2003. Now i´m doing the update to excel 2007.

I using this code:

SET @SECUENCIA= 'INSERT INTO OPENROWSET (''Microsoft.ACE.OLEDB.12.0'',''Excel 12.0;Database='+@CMD+' ;'',''SELECT * FROM [Sheet1$]'')
SELECT REGISTRO, NB_ANALISTA, NUM_SINIESTRO, CIUDAD, OFERTANTE, MARCA, MODELO,
ANIO, PIEZA, FE_ENT_REP, COSTO, CALIDAD, DISPONIBILIDAD
FROM TEMPORAL_PROVEE_GANAD'

EXEC(@SECUENCIA)

where @cmd are the full path to the .xlsx file.

The problem is that i made this to generate a group of 30 reports, every report have 10 sheets, and i´m having the next error:

Msg 7357, Level 16, State 2, Line 1
Cannot process the object "SELECT * FROM [Sheet1$]". The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.

The innusual is that this code works fine to generates some reports and fail to others (i mean that the code are doing what i expect to do)

I already sheck the name of the Sheet, the long of the path, and a lot of things. This is why i´m locking for help.

Thanks.

quote:
Originally posted by madhivanan

quote:
Originally posted by sathiesh2005

Very useful post.

Thanks.

Regards,
Sathieshkumar. R


Thanks for the feedback

Madhivanan

Failing to plan is Planning to fail



Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 03/16/2010 :  03:19:10  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by dpaez01

Hi madhivanan

I was using a very similar code since a year ago and it´s work perfect.

That code work to export data from sql server 2005 to Excel 2003. Now i´m doing the update to excel 2007.

I using this code:

SET @SECUENCIA= 'INSERT INTO OPENROWSET (''Microsoft.ACE.OLEDB.12.0'',''Excel 12.0;Database='+@CMD+' ;'',''SELECT * FROM [Sheet1$]'')
SELECT REGISTRO, NB_ANALISTA, NUM_SINIESTRO, CIUDAD, OFERTANTE, MARCA, MODELO,
ANIO, PIEZA, FE_ENT_REP, COSTO, CALIDAD, DISPONIBILIDAD
FROM TEMPORAL_PROVEE_GANAD'

EXEC(@SECUENCIA)

where @cmd are the full path to the .xlsx file.

The problem is that i made this to generate a group of 30 reports, every report have 10 sheets, and i´m having the next error:

Msg 7357, Level 16, State 2, Line 1
Cannot process the object "SELECT * FROM [Sheet1$]". The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.

The innusual is that this code works fine to generates some reports and fail to others (i mean that the code are doing what i expect to do)

I already sheck the name of the Sheet, the long of the path, and a lot of things. This is why i´m locking for help.

Thanks.

quote:
Originally posted by madhivanan

quote:
Originally posted by sathiesh2005

Very useful post.

Thanks.

Regards,
Sathieshkumar. R


Thanks for the feedback

Madhivanan

Failing to plan is Planning to fail






Make sure the first row of the sheet has column infomrations
Also check the string returned by @SECUENCIA

Madhivanan

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

dpaez01
Starting Member

4 Posts

Posted - 03/16/2010 :  12:52:43  Show Profile  Reply with Quote
quote:
Originally posted by madhivanan

quote:
Originally posted by dpaez01

Hi madhivanan

I was using a very similar code since a year ago and it´s work perfect.

That code work to export data from sql server 2005 to Excel 2003. Now i´m doing the update to excel 2007.

I using this code:

SET @SECUENCIA= 'INSERT INTO OPENROWSET (''Microsoft.ACE.OLEDB.12.0'',''Excel 12.0;Database='+@CMD+' ;'',''SELECT * FROM [Sheet1$]'')
SELECT REGISTRO, NB_ANALISTA, NUM_SINIESTRO, CIUDAD, OFERTANTE, MARCA, MODELO,
ANIO, PIEZA, FE_ENT_REP, COSTO, CALIDAD, DISPONIBILIDAD
FROM TEMPORAL_PROVEE_GANAD'

EXEC(@SECUENCIA)

where @cmd are the full path to the .xlsx file.

The problem is that i made this to generate a group of 30 reports, every report have 10 sheets, and i´m having the next error:

Msg 7357, Level 16, State 2, Line 1
Cannot process the object "SELECT * FROM [Sheet1$]". The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.

The innusual is that this code works fine to generates some reports and fail to others (i mean that the code are doing what i expect to do)

I already sheck the name of the Sheet, the long of the path, and a lot of things. This is why i´m locking for help.

Thanks.

quote:
Originally posted by madhivanan

quote:
Originally posted by sathiesh2005

Very useful post.

Thanks.

Regards,
Sathieshkumar. R


Thanks for the feedback

Madhivanan

Failing to plan is Planning to fail






Make sure the first row of the sheet has column infomrations
Also check the string returned by @SECUENCIA

Madhivanan

Failing to plan is Planning to fail



Thanks for the answer.

I already check this two things. I really don't know what's happening with this code. There another suggest to this? or maybe we can talk more private and describe you all the process..

Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 03/18/2010 :  09:11:36  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Can you post the result of @SECUENCIA?

Madhivanan

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

dpaez01
Starting Member

4 Posts

Posted - 03/18/2010 :  15:01:43  Show Profile  Reply with Quote
quote:
Originally posted by madhivanan

Can you post the result of @SECUENCIA?

Madhivanan

Failing to plan is Planning to fail



Sure.. this is the information in @secuencia

INSERT INTO OPENROWSET ('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=E:\REPORTES_COATI_AS\2-2010\MAPFRE_LA_SEGURIDAD_CA_DE_SEGUROS-2-2010.xlsx ;HDR=NO','SELECT * FROM [Proveedores_Ganadores$]')
SELECT REGISTRO, NB_ANALISTA, NUM_SINIESTRO, CIUDAD, OFERTANTE, MARCA, MODELO,
ANIO, PIEZA, FE_ENT_REP, COSTO, CALIDAD, DISPONIBILIDAD
FROM TEMPORAL_PROVEE_GANAD

after this i just execute @secuencia and the query should generate the report.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 03/19/2010 :  11:07:37  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by dpaez01

quote:
Originally posted by madhivanan

Can you post the result of @SECUENCIA?

Madhivanan

Failing to plan is Planning to fail



Sure.. this is the information in @secuencia

INSERT INTO OPENROWSET ('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=E:\REPORTES_COATI_AS\2-2010\MAPFRE_LA_SEGURIDAD_CA_DE_SEGUROS-2-2010.xlsx ;HDR=NO','SELECT * FROM [Proveedores_Ganadores$]')
SELECT REGISTRO, NB_ANALISTA, NUM_SINIESTRO, CIUDAD, OFERTANTE, MARCA, MODELO,
ANIO, PIEZA, FE_ENT_REP, COSTO, CALIDAD, DISPONIBILIDAD
FROM TEMPORAL_PROVEE_GANAD

after this i just execute @secuencia and the query should generate the report.


Your select statement resturns 13 columns
Do you have 13 columns in EXCEL sheet too?

Madhivanan

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

cvvikram1
Starting Member

India
1 Posts

Posted - 03/24/2010 :  05:59:03  Show Profile  Reply with Quote
Hi Friend,

Here is my situation, I am creating separate excel sheets for exporting the data from different tables using the sp_makewebtask.

Now I want to create dynamically the excel sheet at first instance and then update that document with new worksheets.

Please help me on how to implement this.

Thanks

Edited by - cvvikram1 on 03/24/2010 06:06:39
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 03/29/2010 :  07:13:10  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by cvvikram1

Hi Friend,

Here is my situation, I am creating separate excel sheets for exporting the data from different tables using the sp_makewebtask.

Now I want to create dynamically the excel sheet at first instance and then update that document with new worksheets.

Please help me on how to implement this.

Thanks


If you use that option the new file will be created with data
Use other options to export data to existing sheets


Madhivanan

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

sathiesh2005
Yak Posting Veteran

India
85 Posts

Posted - 04/06/2010 :  07:20:48  Show Profile  Reply with Quote
Hi Madhivanan,

I am using the below code. This works fine when i execute in in a administrator(sa) login. But it is not working in my regular sql login.

SELECT * INTO #XLImportTEST
FROM
OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\WebApp~1\Internet-Portal\sggi\Domestic\Testing.xls','SELECT * FROM [Sheet1$]')

This is the error message:
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.

Is it possible to execute it through my login? i.e., by giving permission to my login ? If possible which permission should i give to my login.

Thanks in advance.


Regards,
Sathieshkumar. R
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