export to table fixed filed size

Hello I need to export my table fixed filed size by making numbers 15
characters right alignment and strings 20 characters left alignment.Hi Jim
There may be an easier way but with say:
CREATE TABLE mytemp ( id int NOT NULL, name varchar(10) NULL)
CREATE VIEW MyExport
SELECT RIGHT(SPACE(15) + CAST(id AS varchar(15)),15) AS Id,
LEFT(name+SPACE(20),20) as Name
FROM mytemp
The you could use BCP to export from the view.
John
"JIM.H." wrote:
> Hello I need to export my table fixed filed size by making numbers 15
> characters right alignment and strings 20 characters left alignment.

export to table fixed filed size

Hello I need to export my table fixed filed size by making numbers 15 characters right alignment and strings 20 characters left alignment.

You could either use the BCP utility or a SELECT statement if your table is small. Your SELECT statement would look something like this:

SELECTCONVERT(char(15),col1) ,CONVERT(char15,col2)...FROM yourtable

|||

Thanks for the reply, how do I arrange alignment, numbers to right, strings to left?

|||

Perhaps something like this:

SELECT space(15-len(NumericVal)) +convert(varchar,NumericVal),CONVERT(char(20), StringVal)FROM yourtable

export to table fixed filed size

Hello I need to export my table fixed filed size by making numbers 15
characters right alignment and strings 20 characters left alignment.Hi Jim
There may be an easier way but with say:
CREATE TABLE mytemp ( id int NOT NULL, name varchar(10) NULL)
CREATE VIEW MyExport
SELECT RIGHT(SPACE(15) + CAST(id AS varchar(15)),15) AS Id,
LEFT(name+SPACE(20),20) as Name
FROM mytemp
The you could use BCP to export from the view.
John
"JIM.H." wrote:

> Hello I need to export my table fixed filed size by making numbers 15
> characters right alignment and strings 20 characters left alignment.

export to fixed filed size

Hello I need to export my table fixed filed size by making numbers 15 characters right alignment and strings 20 characters left alignment.

You could define a view or use a query that contains the expressions to produce the character format of the data and use it to export the data. Ex:

bcp "select right(replicate(space(1), 15) + cast(numcol as varchar), 15) as numcol, cast(stringcol as char(20)) as stringcol from tbl" queryout ....

But this is probably not a task for SQL or command-line utilities. You could use DTS/SSIS packages but I am not sure of the performance implications. Ask in the SSIS forum.

Export to Excel: Format cells to "Number"

Hi all
When I export my reports to excel, all cells are set to "General" number
style.
Users wanted to show numbers as "Number", but I didn't found the way to set
the cell output style.
Any idea?
Thanks in advance.On Aug 20, 12:57 pm, "David Rodr=EDguez Rinc=F3n" <d_rin...@.hotmail.com>
wrote:
> Hi all
> When I export my reports to excel, all cells are set to "General" number
> style.
> Users wanted to show numbers as "Number", but I didn't found the way to s=et
> the cell output style.
> Any idea?
> Thanks in advance.
Depending on your needs, you may be able to set the field's format
based on what the numbering format needs to be. Maybe something like
one of the following as an expression in the Format property (select
the control -> select the View drop-down list -> Properties Window ->
to the right of Format, select <Expression...>)
#,0
-or-
#,0.00
etc
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Hi Enrique.
First of all, thank you very much for your quick answer
My issue is not related woth the format in the Report Viewer. I can show
data using the correct format.
The problem is in the excel file exported by MS RS, when all the cells are
set as "General" (right click on cell >> Format Cell >> Number Category)
Our users want to set numbers to "Number" category, to be able to apply
formulas to validate data.
I tried your approach, and it works in the Report Viewer, but unfortunatelly
the excel file is exported with no specific cell format :(
Any other suggestion?
Thanks again.
David Rodríguez
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1187661848.228911.233150@.50g2000hsm.googlegroups.com...
On Aug 20, 12:57 pm, "David Rodríguez Rincón" <d_rin...@.hotmail.com>
wrote:
> Hi all
> When I export my reports to excel, all cells are set to "General" number
> style.
> Users wanted to show numbers as "Number", but I didn't found the way to
> set
> the cell output style.
> Any idea?
> Thanks in advance.
Depending on your needs, you may be able to set the field's format
based on what the numbering format needs to be. Maybe something like
one of the following as an expression in the Format property (select
the control -> select the View drop-down list -> Properties Window ->
to the right of Format, select <Expression...>)
#,0
-or-
#,0.00
etc
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Hi again!
Enrique, your solution works.
My problem was that if you format the value in the .Value expresion, using
funtions like FormatNumber or FormatPercentage, the format entered in the
.Format expresion is not taked into account.
If I only show the Value in the textbox, and use the .Format property using
the MS Excel custom styles[ ="#,##0;(#,##0)"] it works!!!!!
Thanks you very much!!
Regards.
David Rodríguez Rincón
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1187661848.228911.233150@.50g2000hsm.googlegroups.com...
On Aug 20, 12:57 pm, "David Rodríguez Rincón" <d_rin...@.hotmail.com>
wrote:
> Hi all
> When I export my reports to excel, all cells are set to "General" number
> style.
> Users wanted to show numbers as "Number", but I didn't found the way to
> set
> the cell output style.
> Any idea?
> Thanks in advance.
Depending on your needs, you may be able to set the field's format
based on what the numbering format needs to be. Maybe something like
one of the following as an expression in the Format property (select
the control -> select the View drop-down list -> Properties Window ->
to the right of Format, select <Expression...>)
#,0
-or-
#,0.00
etc
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||On Aug 22, 1:11 pm, "David Rodr=EDguez Rinc=F3n" <d_rin...@.hotmail.com>
wrote:
> Hi again!
> Enrique, your solution works.
> My problem was that if you format the value in the .Value expresion, using
> funtions like FormatNumber or FormatPercentage, the format entered in the
> .Format expresion is not taked into account.
> If I only show the Value in the textbox, and use the .Format property usi=ng
> the MS Excel custom styles[ =3D"#,##0;(#,##0)"] it works!!!!!
> Thanks you very much!!
> Regards.
> David Rodr=EDguez Rinc=F3n
> "EMartinez" <emartinez...@.gmail.com> wrote in message
> news:1187661848.228911.233150@.50g2000hsm.googlegroups.com...
> On Aug 20, 12:57 pm, "David Rodr=EDguez Rinc=F3n" <d_rin...@.hotmail.com>
> wrote:
> > Hi all
> > When I export my reports to excel, all cells are set to "General" number
> > style.
> > Users wanted to show numbers as "Number", but I didn't found the way to
> > set
> > the cell output style.
> > Any idea?
> > Thanks in advance.
> Depending on your needs, you may be able to set the field's format
> based on what the numbering format needs to be. Maybe something like
> one of the following as an expression in the Format property (select
> the control -> select the View drop-down list -> Properties Window ->
> to the right of Format, select <Expression...>)
> #,0
> -or-
> #,0.00
> etc
> Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
You're welcome. Glad I could be of assistance.
Regards,
Enrique Martinez
Sr. Software Consultant|||Hi again!
Enrique, your solution works.
My problem was that if you format the value in the .Value expresion, using
funtions like FormatNumber or FormatPercentage, the format entered in the
.Format expresion is not taked into account.
If I only show the Value in the textbox, and use the .Format property using
the MS Excel custom styles[ ="#,##0;(#,##0)"] it works!!!!!
Thanks you very much!!
Regards.
David Rodríguez Rincón
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1187661848.228911.233150@.50g2000hsm.googlegroups.com...
On Aug 20, 12:57 pm, "David Rodríguez Rincón" <d_rin...@.hotmail.com>
wrote:
> Hi all
> When I export my reports to excel, all cells are set to "General" number
> style.
> Users wanted to show numbers as "Number", but I didn't found the way to
> set
> the cell output style.
> Any idea?
> Thanks in advance.
Depending on your needs, you may be able to set the field's format
based on what the numbering format needs to be. Maybe something like
one of the following as an expression in the Format property (select
the control -> select the View drop-down list -> Properties Window ->
to the right of Format, select <Expression...>)
#,0
-or-
#,0.00
etc
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Export to Excel - "Convert from Number to text" to Number

Hi all,

I was trying to export a report which contains a number format. When I do that, all the numbers will have a green small tag beside it saying "Convert from Number to Text". Which in turn restricts the user, to allow any calculations on the cells. Is there anyway that I can change the format to number when I export it to excel so that I can allow users to do any calculations after they export it to excel?

The details for the above problem are below:

I am using Sql Server 2000 SP4, Reporting services. and I am giving an example below, in case if my problem description is not clear.

Say I have a report which has three columns called Name, Sales, Cost, whose data types are set to - Default, N0, C(Currency). When I export this report to Excel, I get the green tag beside both the Sales and Cost column cells. when I hover over the "!" which comes beside the selected cell , it has a note saying "The number in this cell is formatted as text or preceeded by an apostrophe.". The first line when I click on ^ button shows "Convert from Number to Text."

Any help on this problem is greatly appreciated,

Thanks,

-Shireesha

I have the exact same issue and it's quite problematic (for me as well as others, I assume) since the whole value of being able to export to Excel is that users will be able to play with the data. If the data is formatted as text, then the user can't perform calculations or create charts based on the data. I'm new to Reporting Services and Visual Web Developer and I don't know how to approach this problem. Is there anyone who has ideas on how to designate the format of the cells when exporting to Excel?

To see the result of the problem, create a report on this page: http://epdc.org/GuidedSearch.aspx, and export it to Excel. Witness the angry green corners.

Export to Excel - "Convert from Number to text" to Number

Hi all,

I was trying to export a report which contains a number format. When I do that, all the numbers will have a green small tag beside it saying "Convert from Number to Text". Which in turn restricts the user, to allow any calculations on the cells. Is there anyway that I can change the format to number when I export it to excel so that I can allow users to do any calculations after they export it to excel?

The details for the above problem are below:

I am using Sql Server 2000 SP4, Reporting services. and I am giving an example below, in case if my problem description is not clear.

Say I have a report which has three columns called Name, Sales, Cost, whose data types are set to - Default, N0, C(Currency). When I export this report to Excel, I get the green tag beside both the Sales and Cost column cells. when I hover over the "!" which comes beside the selected cell , it has a note saying "The number in this cell is formatted as text or preceeded by an apostrophe.". The first line when I click on ^ button shows "Convert from Number to Text."

Any help on this problem is greatly appreciated,

Thanks,

-Shireesha

I have the exact same issue and it's quite problematic (for me as well as others, I assume) since the whole value of being able to export to Excel is that users will be able to play with the data. If the data is formatted as text, then the user can't perform calculations or create charts based on the data. I'm new to Reporting Services and Visual Web Developer and I don't know how to approach this problem. Is there anyone who has ideas on how to designate the format of the cells when exporting to Excel?

To see the result of the problem, create a report on this page: http://epdc.org/GuidedSearch.aspx, and export it to Excel. Witness the angry green corners.

Export report to excel - format items as numbers

Hi,

I have a trouble when exporting a local report to excel.

The fields are exported to excel as text despite they are numbers and i have explicitly formatted them as such:

=FormatNumber(Fields!someField.Value,0)

or

=FormatNumber(Fields!someField.Value,2)

Tried a second approach to use: "Format" property to "g" or "N" as specified here:

http://msdn2.microsoft.com/en-us/library/ms157406.aspx

but this again does not result in "Number" format in excel but in a custom one. But I'm required to output these fields in number format, not custom one. So the second option does not work for me.

Tried a third approach to do some casts like CDbl, but again this result in custom format.

There should be some way to instruct the report to output these fields in number format.

Does anyone knows it?

Thanks,

Yani

HI,Yani:

What is the meaning of 'Custom '? From what kind item value you want to convert it into a "Number".

Would you please give us a sample for your scenario?

|||

Hi,

Thanks for your reply.

My report contains multiple columns of integer and decimal values, which are shown perfectly in the Report Viewer.

However, that report is mainly used when it's being exported to excel.

However, when the report is exported to excel the integer and decimal values don't remain numbers any more, but are rendered as text.

But I need them in Number format.

Doing, what I've written above results in a custom format - which means, if you open an excel doc and select a cell -> right click on it -> "Format Cells" -> On the first Number Tab, there are multiple categories. So I need my cells to be in category "Number" , instead of "Custom".

Thanks,

Yani

|||

HI, Yani:

Yes, i have gave it a try

1.When i set the 'Format' property of the column to C or N or CInt(Fields!someField.Value), it results to be the 'Custom Format' in the formatting property in the EXCEL

2.When i set FormatNumber(Fields!someField.Value,"N"), it results to be the 'General Format' in the formatting property in the EXCEL.

But i have not found any solution to make it render as the "Number Format" in the Excel. I will get inside into it in the following days and inform you whether i can get the answer ASAP.

If i misunderstand you about your question, please feel free to correct me and i will try to help you with more information.

I hope the above information will be helpful. If you have any issues or concerns, please let me know. It's my pleasure to be of assistance

|||

Hi Rex Lin,

Thanks a lot for you investigation.

You have correctly understood my question.

I would be very grateful in case you manage to find an answer to it :)

Thanks in advance,

Yani

|||

Hi

I am facing the same problem like the integer/number data rendered into excel is considered as General/Custom format instead of Number.
I tried with Int() in reports and it is too rendered as General format...

Actually I also have some percentage values in cells of a report but those are considered as text when exported to excel. I hae to format them as Percentage only. Here i have used FormatPercent( ) in reports

If you have found any solution for the above issue then could you please share it with me too. Your quick reply will be very helpful as I have to fix this issue ASAP.

Thanks in advance
Sups123

|||

Hi Yani,

Try the below code

=FORMATNUMBER(Fields!LICENSE_FEE_AMOUNT.Value,2,True,True)

where LICENSE_FEE_AMOUNT is the name of the value that is being displayed.

2 is the number of decimal places.

Thanks

Subbu

|||

Hi

I think this can help you out..

Excel will interpret only some set of data types. If the field that is returned is some expression and the return type is not always specific data type then it will interpret as string. Let me explain this.

Consider you have expression like =iif(Fields!txIUnit.Value=1,fltQ1Actuals,'To Be decided'). In this example since the return type might be float or string, it will always interpret as string. that is why when downloaded in excel format it identifes it as text.

Try converting the expression into Decimal or Int or double, then it will be represented as text in the excel. It will be pure number.

I mean..Try as below

=CDbl(iif(Fields!txIUnit.Value=1,fltQ1Actuals,'To Be decided'))

Though the above code returns number if the condition is satisfied(txIUnit.Value=1), in other case it shows '#Error'

Please let me know if it makes sense what i have explained