How to remove the header underline in excel

when exporting to excel - the rendering adds an underline to the header, is
there a way so that when exporitng a report to excel that it does not do this?the report header that is...not column headings...
"ultra ak" wrote:
> when exporting to excel - the rendering adds an underline to the header, is
> there a way so that when exporitng a report to excel that it does not do this?

How to remove tabs from a field?

Is there any way to remove tabs from text fields? I see tabs at the end when I import the data in Excel.
Appreciate any helpSure, in the view that you are extracting use the Replace function, something like:CREATE VIEW vThingie
SELECT thingieId, Replace(thingieText, Char(9), ' ') AS d
FROM thingie
WHERE thingieId < 100...or something like that!

-PatP

How to remove Hyperlink from column while exporting to excel

Hi,

I have one report with few of columns as hyperlinks leading to subreports. When report exported to Excel, columns which are hyperlinks exported and these hyperlinks still present in Excel and is not causing some problem as relative links are used in hyperlinks in main report. So, is there any way to remove hyperlinks from report while exporting to Excel?

I found a sort of work around for this.

Response.Clear()
Response.Buffer = True
Response.ContentType = "application/vnd.ms-excel"

Dim sw As StringWriter = New StringWriter()
Dim htmlTextWriter As HtmlTextWriter = New HtmlTextWriter(sw)

ph.RenderControl(htmlTextWriter)
Dim sTemp As String = sw.ToString

sTemp = sTemp.Replace("href=", "")
Response.Write(sTemp)
Response.End()

Basically whatever you are giving the response.write to output, modify the string by taking out "href=". Even though there is still part of the <a> tag still there, excel seems to ignore it and just display the text.

Hope this works for you!!

And if it does work for, please mark this post as answer!!

Rob

sql

How to Receive emails

I've recently migrated to sql 2005 and I'm trying to find a solution
to some new requests.
I need to setup a system that process excel spreadsheet received by
email. The sheets will contain data coming from veterinaries that -
after an integration with centrally stored data - should trigger a
report.
I can receive emails both on Lotus Notes (that I don't manage) or POP3
account.
Can't' install Exchange because of the centralized Active Directory
policy.
Anyone, who had a similar issue, can recommend me a simple and robust
solution?
The alternative is to set up a web page to upload the files, but the
IIS server is located on the intranet, so people will need to dial a
modem or use the vpn. Not straight as an email...
Many Thanks,
josephOn Oct 11, 3:37 am, curi...@.spore.it wrote:
> I've recently migrated to sql 2005 and I'm trying to find a solution
> to some new requests.
> I need to setup a system that process excel spreadsheet received by
> email. The sheets will contain data coming from veterinaries that -
> after an integration with centrally stored data - should trigger a
> report.
> I can receive emails both on Lotus Notes (that I don't manage) or POP3
> account.
> Can't' install Exchange because of the centralized Active Directory
> policy.
> Anyone, who had a similar issue, can recommend me a simple and robust
> solution?
> The alternative is to set up a web page to upload the files, but the
> IIS server is located on the intranet, so people will need to dial a
> modem or use the vpn. Not straight as an email...
> Many Thanks,
> joseph
You will most likely want to set this up via a custom ASP.NET
application. You can either have an SSIS pkg triggered through an
ASP.NET application to loop through a directory and import several
excel files into a table, etc -or- you can use bulk insert or BCP
through an ASP.NET application'/stored procedure to import the excel
spreadsheets. Then you could call Reporting Services through the web
service method (http://msdn2.microsoft.com/en-us/library/
microsoft.wssux.reportingserviceswebservice.rsexecutionservice2005.reportexecutionservice.render.aspx )
to create a report. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

How to read a cell of Excel in Script task?

BTW, I cannot use Office automation because MS office is not installed on the machine.

Thanks,
Without using office automation, you are quite limited to what you can get out.
Either use Excel Source, which is like a flat table of rows and columns, or a 3rd party product like Data Defractor which allows you to "drill" into the document and return specific areas.

How to query an remote excel file using OPENROWSET

Hi,

can anyone help me out this problem.

i want to query an excel file which at remote location through openrowset.

Select * from openrowset(.... ?

thanks

Sunila, this will query an excel spreadsheet that is local or on a shared network drive:

SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Home\dannyboy\WORK\opeid_PEPS_database\PEPS_Reference_Codes.xls', [ref_territory$])

Replace the database=\\server\direcorty\filename.xls to access a different server (say a remote server). In the above case, ref_territory is a tab name in the Excel spreadsheet. Note you need to add a $ at the end of the tab name.

You could also define a linked server to the excel spreadsheet and then query without using OPENROWSET...

Assume Linked Server name is SomeLinkedServer

Select * from SomeLinkedServer...ref_territory$ (note there are 3 periods between Linked Server name and tab name)

See Books Online concerning OPENROWSET and/or Linked Servers for more info.

|||

thanks a lot

i need more clarification for the same. if the excel file is kept in a remote mechine then i need to access the same (\\ipaddress\foldername\xlsfilename.xls). the same query will work if i give like this?

|||Sunila, give it a try. It should work assuming the security is in place to allow you to access the remote directory or the services (SQL Agent, etc) have proper security if you are running this from a job...|||

i got another solution..

like

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;HDR=Yes;IMEX=1;database=\\<computername>\filename.xls;',
'SELECT * FROM [Sheet1$]')