Fromatting excel column

Hi,

I have an excel export with numeric values. When the SSIS package writes into the excel it treats all data types as strings and attaches an apostrophe in the beginning. I tried formatting the excel sheet with the number data type and saving it . It doesnt work.

Other strange thing is that when I go into the advanced editor for the excel destination and look into the properties of the external columns all of them have the data type as Unicode String[DT_WSTR] irrespective of what the data type is from the input.The UI allows me to change it to numeric for numeric columns without any error but the value is not saved at all which is very frustrating. I also tried changing it in the xml file of the package, it some how seems to overwrite it after validation. It would be really nice if it threw an error saying that it cannot be changed.

Anyways there seems to be no way of changing the destination data type if it s an existing file and if I create a new excel sheet there is no way of formatting data. Is there any way out?

Thanks

Interesting report on the behavior your seeing. My experience was quite the opposite when working with an Excel import package.

I found the Excel connector to be VERY tightly bound to the defined data types within the actual Excel file. I also found that it seemed to determine the type by the top few rows and not necessarily by the whole data set.|||chandrala's issues are with an Excel destination, not a source though.|||Ya my issue is with destination if I create a new excel file or if I already have an excel file it doesnt matter. It is kind of dumb to expect that the file already has to have some data before the export to determine the type. Most exports are into an unpopulated file. Please let me know how to tackle this.|||I'm having the same issue. Anyone come up with a resolution?|||On the destination connection, I had first row has column names. Set this to no, and everything worked.|||

Grant Mackay wrote:

On the destination connection, I had first row has column names. Set this to no, and everything worked.

This make sense as SSIS try to guess the data type based on the first values of the column. If you have the column names in you first row; then SSIS will treat it as String; which be correct. May that be your problem?

I also found few trheads taking about data conversition when excel is the destination:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=64475&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=193526&SiteID=1

Fromatting excel column

Hi,

I have an excel export with numeric values. When the SSIS package writes into the excel it treats all data types as strings and attaches an apostrophe in the beginning. I tried formatting the excel sheet with the number data type and saving it . It doesnt work.

Other strange thing is that when I go into the advanced editor for the excel destination and look into the properties of the external columns all of them have the data type as Unicode String[DT_WSTR] irrespective of what the data type is from the input.The UI allows me to change it to numeric for numeric columns without any error but the value is not saved at all which is very frustrating. I also tried changing it in the xml file of the package, it some how seems to overwrite it after validation. It would be really nice if it threw an error saying that it cannot be changed.

Anyways there seems to be no way of changing the destination data type if it s an existing file and if I create a new excel sheet there is no way of formatting data. Is there any way out?

Thanks

Interesting report on the behavior your seeing. My experience was quite the opposite when working with an Excel import package.

I found the Excel connector to be VERY tightly bound to the defined data types within the actual Excel file. I also found that it seemed to determine the type by the top few rows and not necessarily by the whole data set.|||chandrala's issues are with an Excel destination, not a source though.|||Ya my issue is with destination if I create a new excel file or if I already have an excel file it doesnt matter. It is kind of dumb to expect that the file already has to have some data before the export to determine the type. Most exports are into an unpopulated file. Please let me know how to tackle this.|||I'm having the same issue. Anyone come up with a resolution?|||On the destination connection, I had first row has column names. Set this to no, and everything worked.|||

Grant Mackay wrote:

On the destination connection, I had first row has column names. Set this to no, and everything worked.

This make sense as SSIS try to guess the data type based on the first values of the column. If you have the column names in you first row; then SSIS will treat it as String; which be correct. May that be your problem?

I also found few trheads taking about data conversition when excel is the destination:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=64475&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=193526&SiteID=1

sql

from sql 2005 to sqlexpress 2005

Is there any way to export/import/copy a database from sql 2005 to sql
express2005?
Thanks.Detach/attach and backup/restore are two options.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alex D." <alexware69@.hotmail.com> wrote in message news:emHL1HrbGHA.1960@.TK2MSFTNGP05.phx.g
bl...
> Is there any way to export/import/copy a database from sql 2005 to sql
> express2005?
> Thanks.
>|||thanks I'll try dettach/attach cause restore is not working
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23SYgFvrbGHA.4716@.TK2MSFTNGP03.phx.gbl...[vbcol=seagreen]
> Detach/attach and backup/restore are two options.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Alex D." <alexware69@.hotmail.com> wrote in message
> news:emHL1HrbGHA.1960@.TK2MSFTNGP05.phx.gbl...|||"Is not working" isn't a very good problem description. Restore should work,
and if you show us some
details (the RESTORE command, error message etc), we can probably help you o
ut with that. But attach
is a good option as well. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alex D." <alexware69@.hotmail.com> wrote in message news:uouca9rbGHA.3352@.TK2MSFTNGP03.phx.g
bl...
> thanks I'll try dettach/attach cause restore is not working
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:%23SYgFvrbGHA.4716@.TK2MSFTNGP03.phx.gbl...
>|||The most likely cause of a failure here would be that the database is over
4GB so it can't run in Express. If that's the case, attach won't work
either.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ewa1khsbGHA.3504@.TK2MSFTNGP04.phx.gbl...
> "Is not working" isn't a very good problem description. Restore should
> work, and if you show us some details (the RESTORE command, error message
> etc), we can probably help you out with that. But attach is a good option
> as well. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Alex D." <alexware69@.hotmail.com> wrote in message
> news:uouca9rbGHA.3352@.TK2MSFTNGP03.phx.gbl...
>|||Good catch, Roger. Didn't think about that... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:uvu%23qvsbGHA.1320@.TK2MSFTNGP04.phx.gbl...
> The most likely cause of a failure here would be that the database is over
4GB so it can't run in
> Express. If that's the case, attach won't work either.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
>|||database is not over 4gb. the error message I am getting when trying to
restore is a permissions message. It seems that my new database dont have
permissions to restore the backup. I am using sql authentication and the sa
in both databases have the same password.
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:uvu%23qvsbGHA.1320@.TK2MSFTNGP04.phx.gbl...
> The most likely cause of a failure here would be that the database is over
> 4GB so it can't run in Express. If that's the case, attach won't work
> either.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:ewa1khsbGHA.3504@.TK2MSFTNGP04.phx.gbl...
>

from sql 2005 to sqlexpress 2005

Is there any way to export/import/copy a database from sql 2005 to sql
express2005?
Thanks.Detach/attach and backup/restore are two options.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alex D." <alexware69@.hotmail.com> wrote in message news:emHL1HrbGHA.1960@.TK2MSFTNGP05.phx.gbl...
> Is there any way to export/import/copy a database from sql 2005 to sql
> express2005?
> Thanks.
>|||thanks I'll try dettach/attach cause restore is not working
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23SYgFvrbGHA.4716@.TK2MSFTNGP03.phx.gbl...
> Detach/attach and backup/restore are two options.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Alex D." <alexware69@.hotmail.com> wrote in message
> news:emHL1HrbGHA.1960@.TK2MSFTNGP05.phx.gbl...
>> Is there any way to export/import/copy a database from sql 2005 to sql
>> express2005?
>> Thanks.|||"Is not working" isn't a very good problem description. Restore should work, and if you show us some
details (the RESTORE command, error message etc), we can probably help you out with that. But attach
is a good option as well. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alex D." <alexware69@.hotmail.com> wrote in message news:uouca9rbGHA.3352@.TK2MSFTNGP03.phx.gbl...
> thanks I'll try dettach/attach cause restore is not working
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:%23SYgFvrbGHA.4716@.TK2MSFTNGP03.phx.gbl...
>> Detach/attach and backup/restore are two options.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Alex D." <alexware69@.hotmail.com> wrote in message news:emHL1HrbGHA.1960@.TK2MSFTNGP05.phx.gbl...
>> Is there any way to export/import/copy a database from sql 2005 to sql express2005?
>> Thanks.
>|||The most likely cause of a failure here would be that the database is over
4GB so it can't run in Express. If that's the case, attach won't work
either.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ewa1khsbGHA.3504@.TK2MSFTNGP04.phx.gbl...
> "Is not working" isn't a very good problem description. Restore should
> work, and if you show us some details (the RESTORE command, error message
> etc), we can probably help you out with that. But attach is a good option
> as well. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Alex D." <alexware69@.hotmail.com> wrote in message
> news:uouca9rbGHA.3352@.TK2MSFTNGP03.phx.gbl...
>> thanks I'll try dettach/attach cause restore is not working
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:%23SYgFvrbGHA.4716@.TK2MSFTNGP03.phx.gbl...
>> Detach/attach and backup/restore are two options.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Alex D." <alexware69@.hotmail.com> wrote in message
>> news:emHL1HrbGHA.1960@.TK2MSFTNGP05.phx.gbl...
>> Is there any way to export/import/copy a database from sql 2005 to sql
>> express2005?
>> Thanks.
>>
>|||Good catch, Roger. Didn't think about that... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:uvu%23qvsbGHA.1320@.TK2MSFTNGP04.phx.gbl...
> The most likely cause of a failure here would be that the database is over 4GB so it can't run in
> Express. If that's the case, attach won't work either.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
>|||database is not over 4gb. the error message I am getting when trying to
restore is a permissions message. It seems that my new database dont have
permissions to restore the backup. I am using sql authentication and the sa
in both databases have the same password.
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:uvu%23qvsbGHA.1320@.TK2MSFTNGP04.phx.gbl...
> The most likely cause of a failure here would be that the database is over
> 4GB so it can't run in Express. If that's the case, attach won't work
> either.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:ewa1khsbGHA.3504@.TK2MSFTNGP04.phx.gbl...
>> "Is not working" isn't a very good problem description. Restore should
>> work, and if you show us some details (the RESTORE command, error message
>> etc), we can probably help you out with that. But attach is a good option
>> as well. :-)
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Alex D." <alexware69@.hotmail.com> wrote in message
>> news:uouca9rbGHA.3352@.TK2MSFTNGP03.phx.gbl...
>> thanks I'll try dettach/attach cause restore is not working
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:%23SYgFvrbGHA.4716@.TK2MSFTNGP03.phx.gbl...
>> Detach/attach and backup/restore are two options.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Alex D." <alexware69@.hotmail.com> wrote in message
>> news:emHL1HrbGHA.1960@.TK2MSFTNGP05.phx.gbl...
>> Is there any way to export/import/copy a database from sql 2005 to sql
>> express2005?
>> Thanks.
>>
>

Freeze Panes in Excel

Is there anything I can do so that when my users export their report from
reporting services to Excel that it freezes the columns'
I also need to set a default zoom %
Is there anyway to do this - thanksI also have that problem. Because when it expands, it can be really time
consuming..
"dragon" wrote:
> Is there anything I can do so that when my users export their report from
> reporting services to Excel that it freezes the columns'
> I also need to set a default zoom %
> Is there anyway to do this - thanks
>

Freeze Pane functionality during Export to Excel

Hi All,

I have created a Report using Sql Server 2005 Reporting Services.

When I export the report to excel, I want to freeze the pane of the headers in the exported excel.

Any pointers on how to achieve this will greatly help me.

Thanks,

S Suresh

There is no way to tell the Excel renderer to freeze an arbitrary pane. However, the renderer does set a freeze pane for the page headers (not the table headers), so that might be a possibility for you.|||Will you please tell me how to set a freeze pane fo rthe page headers? We do not want to export the page headers to the excel because it merges servera columns for one actual data column. That makes it impossible to change the sorting columns. Thanks,|||I'm not sure I understand what you mean, but the only thing exported to a freeze pane is the page headers. There is no way to put any other part of the report into a freeze pane.

If you are trying to have the headers not show up in Excel, you can have them export as "simple" headers. This would put them into Excel as actual Excel headers, and not as part of the worksheet. To do this, you need to set the SimplePageHeaders device info setting:
http://msdn2.microsoft.com/en-us/library/ms155069.aspx

Hope that helps...|||

Thanks a lot for your reply.

What I want to do is exactly what you said not to put page headers into the excel. My page header has two textboxes, so I do not know if that working. I will try your suggestion tomorrow. Thanks again!

|||

Geoff,

Thank you so much for your link. Looks like it is for sql sever reporting service 2005. We are using 2003 but I tried anyway.

I used the way change the setting through adding render section in xml code but got error. The question I have is where should I put the render section in the xml code? Below is the code, please help me out. Thank you very much for your help!

<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">

<Render>

<Extension Name="EXCEL" Type="Microsoft.ReportingServices.Rendering.ExcelRenderer.ImageReport,Microsoft.ReportingServices.ExcelRendering">

<Configuration> <DeviceInfo>

<SimplePageHeaders>True</SimplePageHeaders>

</DeviceInfo> </Configuration> </Extension>

</Render>

<PageHeader>

<ReportItems>

<Textbox Name="textbox7">

Error message:

c:\catsreports\catsreports\Contractor.rdl(4): Deserialization failed: The element 'http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition:Report' has invalid child element 'http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition:Render'. An error occurred at , (4, 4).

Freeze Pane functionality during Export to Excel

Hi All,

I have created a Report using Sql Server 2005 Reporting Services.

When I export the report to excel, I want to freeze the pane of the headers in the exported excel.

Any pointers on how to achieve this will greatly help me.

Thanks,

S Suresh

There is no way to tell the Excel renderer to freeze an arbitrary pane. However, the renderer does set a freeze pane for the page headers (not the table headers), so that might be a possibility for you.|||Will you please tell me how to set a freeze pane fo rthe page headers? We do not want to export the page headers to the excel because it merges servera columns for one actual data column. That makes it impossible to change the sorting columns. Thanks,|||I'm not sure I understand what you mean, but the only thing exported to a freeze pane is the page headers. There is no way to put any other part of the report into a freeze pane.

If you are trying to have the headers not show up in Excel, you can have them export as "simple" headers. This would put them into Excel as actual Excel headers, and not as part of the worksheet. To do this, you need to set the SimplePageHeaders device info setting:
http://msdn2.microsoft.com/en-us/library/ms155069.aspx

Hope that helps...|||

Thanks a lot for your reply.

What I want to do is exactly what you said not to put page headers into the excel. My page header has two textboxes, so I do not know if that working. I will try your suggestion tomorrow. Thanks again!

|||

Geoff,

Thank you so much for your link. Looks like it is for sql sever reporting service 2005. We are using 2003 but I tried anyway.

I used the way change the setting through adding render section in xml code but got error. The question I have is where should I put the render section in the xml code? Below is the code, please help me out. Thank you very much for your help!

<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">

<Render>

<Extension Name="EXCEL" Type="Microsoft.ReportingServices.Rendering.ExcelRenderer.ImageReport,Microsoft.ReportingServices.ExcelRendering">

<Configuration> <DeviceInfo>

<SimplePageHeaders>True</SimplePageHeaders>

</DeviceInfo> </Configuration> </Extension>

</Render>

<PageHeader>

<ReportItems>

<Textbox Name="textbox7">

Error message:

c:\catsreports\catsreports\Contractor.rdl(4): Deserialization failed: The element 'http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition:Report' has invalid child element 'http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition:Render'. An error occurred at , (4, 4).

Free Reporting software

Hi,

are there any free module to export an HTML page to PDF ?

Thanks

I forgot to mention, that I also need a module to export a table like gridview or something like that to an excel.