Export to text file

I'm exporting data from one table (for now) to a fixed width text file.
However, the first column of the text file doesn't come from the
database. I figured using a constant would be the best way to deal with
this, but being new (thrown in) to T-SQL, I'm not sure how to get
there.
The resulting text file has 4 fields, all with fixed character
placement. The first field is an account #, which is the constant field
not in a table. The 2nd field is a 2 character placeholder that's
supposed to be blank, the third a dollar amount, and the 4th a date.
All are to be displayed as a string with no formatting and leading
zeros:
001234567899 00000050006012006
001234567899 00000004306012006...
I can manage individual statements in query analyzer (for fields 2-4),
but I'm clueless about putting it all together in a t-sql statement/dts
package. Any ideas are appreciated.
Thanks,
Not-for-long Newbie and proud of it.Here are two options for using literal values in your SELECT statements.
First choice is good in stored procedures where you don't know the constant
value in advance.
DECLARE @.ConstantValue
SELECT @.ConstantValue = Column_X
FROM MyTable
WHERE {criteria}
SELECT
@.ConstantValue
, Column2
, Column3
, Column4
FROM MyOtherTable
Second Option: (constant value is known is advance)
SELECT
'ConstantValue'
, Column1
, Column3
, Column4
FROM MyOtherTable
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
<birdbyte@.gmail.com> wrote in message news:1151430908.647285.244450@.m73g2000cwd.googlegroup
s.com...
> I'm exporting data from one table (for now) to a fixed width text file.
> However, the first column of the text file doesn't come from the
> database. I figured using a constant would be the best way to deal with
> this, but being new (thrown in) to T-SQL, I'm not sure how to get
> there.
>
> The resulting text file has 4 fields, all with fixed character
> placement. The first field is an account #, which is the constant field
> not in a table. The 2nd field is a 2 character placeholder that's
> supposed to be blank, the third a dollar amount, and the 4th a date.
> All are to be displayed as a string with no formatting and leading
> zeros:
> 001234567899 00000050006012006
> 001234567899 00000004306012006...
>
> I can manage individual statements in query analyzer (for fields 2-4),
> but I'm clueless about putting it all together in a t-sql statement/dts
> package. Any ideas are appreciated.
>
> Thanks,
> Not-for-long Newbie and proud of it.
>

export to pdf

hi everyone,
I learnt from one of the treads here that inorder to export
the report to pdf in a single page we need to set the width and height to be
11in and 8.5in.But when i print the report after exporting to pdf,it prints
in the landscape mode.how Can it be made to print in the portrait mode'
Thanks in Advance...Hi,
Another problem for me is when i export to pdf it prints one extra
blank page.
Why is it so'
Eagerly waiting for ur responses,
Chandra.
"Chandra" wrote:
> hi everyone,
> I learnt from one of the treads here that inorder to export
> the report to pdf in a single page we need to set the width and height to be
> 11in and 8.5in.But when i print the report after exporting to pdf,it prints
> in the landscape mode.how Can it be made to print in the portrait mode'
> Thanks in Advance...|||Hi Chandra,
According to your own settings, width and height are 11in and 8.5in, so this
IS landscape...
You should switch the sizes.
As far as I know, there is no setting of portrait/landscape other than
choosing the height and width accordingly.
HTH,
Andrei.
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:B671220F-F220-43D0-B263-965106398D9E@.microsoft.com...
> hi everyone,
> I learnt from one of the treads here that inorder to
export
> the report to pdf in a single page we need to set the width and height to
be
> 11in and 8.5in.But when i print the report after exporting to pdf,it
prints
> in the landscape mode.how Can it be made to print in the portrait mode'
> Thanks in Advance...|||Hi Chandra,
You should try making the width smaller.
Ex. if Report width is 11, try 10.4 or if 8.5 then try 8.
The next blank page is because the report tries to print outside the
printable area on the current page.
HTH,
Andrei.
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:952614A8-A9FF-47B8-A998-BDA956FE18E4@.microsoft.com...
> Hi,
> Another problem for me is when i export to pdf it prints one extra
> blank page.
> Why is it so'
> Eagerly waiting for ur responses,
> Chandra.
> "Chandra" wrote:
> > hi everyone,
> > I learnt from one of the treads here that inorder to
export
> > the report to pdf in a single page we need to set the width and height
to be
> > 11in and 8.5in.But when i print the report after exporting to pdf,it
prints
> > in the landscape mode.how Can it be made to print in the portrait mode'
> >
> > Thanks in Advance...|||When I export to pdf, there is always an extra page at the end. In viewing
in Visual Studio, if there are 11 pages, there will be 12 pages in pdf. Why?
Blank pages after each page points to width and margin settings, but extra
blank page at end is weird?
Any ideas why this is happening?
"andrei" wrote:
> Hi Chandra,
> According to your own settings, width and height are 11in and 8.5in, so this
> IS landscape...
> You should switch the sizes.
> As far as I know, there is no setting of portrait/landscape other than
> choosing the height and width accordingly.
> HTH,
> Andrei.
> "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> news:B671220F-F220-43D0-B263-965106398D9E@.microsoft.com...
> > hi everyone,
> > I learnt from one of the treads here that inorder to
> export
> > the report to pdf in a single page we need to set the width and height to
> be
> > 11in and 8.5in.But when i print the report after exporting to pdf,it
> prints
> > in the landscape mode.how Can it be made to print in the portrait mode'
> >
> > Thanks in Advance...
>
>|||Hi guys,
I have found that body size must be less or equal to the page size. If
your page's width is 11in than body's width should be <= 11in * 2.54
(27.94cm). It works in my case, I do not have an extra page any more.

Export to Fixed width text file

Export to Fixed width text file

I am trying to export a table to a fixed lenght text file, there is only flat file option and that does not put LF/CR at the end of row, is there any solution?

I believe you can use bcp tobulk export with a format file to meet your needs. SeeFormat Files for Importing or Exporting Data for a place to start.|||

if you use SQL 2005 export wizard or custom SSIS package try to select Ragged right format for flat file destination:

Format

Indicate whether to use delimited, fixed width, or ragged right formatting.

ValueDescription

Delimited

Columns are separated by a delimiter, specified on theColumns page.

Fixed width

Columns have a fixed width.

Ragged right

Ragged right files are those in which every column has a fixed width, except for the last column, which is delimited by the row delimiter.

|||thanks, that worked. How can I execute SSIS package in a stored procedure or in a batch file in Windows?|||You can run it as SQL Agent Job which has SSIS step and this is probably best way or you can run it as batch process from command prompt or from inside SQL procedure. To generate command you can use dtsrunui.exe program which will help you a lot.

Export to Fixed width text file

Export to Fixed width text file

I am trying to export a table to a fixed lenght text file, there is only flat file option and that does not put LF/CR at the end of row, is there any solution?

Ragged right works for me. You can used a derived column to pad your string with spaces if necessary.|||Can you be more specific about what the problem is? I didn't have any trouble setting up a fixed width flat file destination with a LF/CR row delimiter. Add a flat file destination, open it and click New to create a new connection manager, select "Fixed width with row delimiters", give it a file name, switch to Advanced and select the "Row delimiter column" to change the ColumnDelimiter to {LF}{CR} if desired.
|||Row delimiter is not elabled in my environment. What would the problem be?|||It isn't there, or it is but its disabled? Can't think of why either would be the case.

If the above doesn't work for some reason, you can create the same configuration as follows. Open the flat file connection manager (or create a new one). On the General tab, select Ragged Right as the format. For starters, let's not not check "column names in the first data row". Switch to the Advanced tab and add your columns with appropriate DataTypes and OutputColumnWidths. The ColumnType should automatically be Fixed Width for each column but the last one. The last one will be Delimited. Create a dummy column to be your last one. Make its DataType String, OutputColumnWidth 0, and ColumnDelimited to be whatever you want for the row delimiter (you had previously said you wanted {LF}{CR}).
|||

Part of the confusion here may be that you have to pick the Ragged Right option for the flat file connector, not the Fixed Width option. It seems a little counter-intuitive.

Interestingly, if you create your connection manager as Jay suggested originally (by adding the flat file destination, then choosing the New... button beside the connection manager drop-down, the resulting dialog asks if you want a fixed width with row delimiters, then creates a ragged right as Jay's described above.

Export to Excel changes page orientation

I have report page height defined as 11 in. and width as 8.5 in. When I
export the report to pdf, it comes fine in portrait format. But when the
report is exported to Excel, the orientation is changed to Landscape. Even
the page margins are not preserved.
Is it that Excel overrides the report page setup or RS changes the page
settings by itself when the report is exported? I changed the default printer
settings to have "portrait" as the default print format. But still exporting
the report to Excel changes this.
Please let me know if you have found any solution to such a problem.
Thanks in advance,
MilinAccording to the RS help, when you export a report to Excel, this doesn't
take to account the format of the page. There are, in field properties, two
options that allow the field is able to enlarge and shrink. Have you tried
not selecting them?
"Milu" wrote:
> I have report page height defined as 11 in. and width as 8.5 in. When I
> export the report to pdf, it comes fine in portrait format. But when the
> report is exported to Excel, the orientation is changed to Landscape. Even
> the page margins are not preserved.
> Is it that Excel overrides the report page setup or RS changes the page
> settings by itself when the report is exported? I changed the default printer
> settings to have "portrait" as the default print format. But still exporting
> the report to Excel changes this.
> Please let me know if you have found any solution to such a problem.
> Thanks in advance,
> Milin|||Thanks for ur reply.
If u r talking about the "CanGrow" and "CanShrink" properties, I tried
setting them both, true and false. But none of this helped. Is this related
to report orientation changing to landscape?
If you have got positive result trying something, please do let me know.
Thanks,
Milin
"PSM" wrote:
> According to the RS help, when you export a report to Excel, this doesn't
> take to account the format of the page. There are, in field properties, two
> options that allow the field is able to enlarge and shrink. Have you tried
> not selecting them?
> "Milu" wrote:
> > I have report page height defined as 11 in. and width as 8.5 in. When I
> > export the report to pdf, it comes fine in portrait format. But when the
> > report is exported to Excel, the orientation is changed to Landscape. Even
> > the page margins are not preserved.
> >
> > Is it that Excel overrides the report page setup or RS changes the page
> > settings by itself when the report is exported? I changed the default printer
> > settings to have "portrait" as the default print format. But still exporting
> > the report to Excel changes this.
> >
> > Please let me know if you have found any solution to such a problem.
> >
> > Thanks in advance,
> > Milin|||Yes, I was talking about the CanGrow and CanShrink properties. I have never
tried to select a report orientation when I have exported to Excel but, I
have found something I hope you find it useful.
When you export a report to Excel, the width and the height of the page are
looked up in a internal table where there are the most used sizes and
orientations. If they exist in the table, the page format is chosen. If not
so, orientation is determined by comparing height with width. If height is
smaller than width, landscape is chosen.
"Milu" wrote:
> Thanks for ur reply.
> If u r talking about the "CanGrow" and "CanShrink" properties, I tried
> setting them both, true and false. But none of this helped. Is this related
> to report orientation changing to landscape?
> If you have got positive result trying something, please do let me know.
> Thanks,
> Milin
> "PSM" wrote:
> > According to the RS help, when you export a report to Excel, this doesn't
> > take to account the format of the page. There are, in field properties, two
> > options that allow the field is able to enlarge and shrink. Have you tried
> > not selecting them?
> >
> > "Milu" wrote:
> >
> > > I have report page height defined as 11 in. and width as 8.5 in. When I
> > > export the report to pdf, it comes fine in portrait format. But when the
> > > report is exported to Excel, the orientation is changed to Landscape. Even
> > > the page margins are not preserved.
> > >
> > > Is it that Excel overrides the report page setup or RS changes the page
> > > settings by itself when the report is exported? I changed the default printer
> > > settings to have "portrait" as the default print format. But still exporting
> > > the report to Excel changes this.
> > >
> > > Please let me know if you have found any solution to such a problem.
> > >
> > > Thanks in advance,
> > > Milin|||Yes, I came across that internal table stuff in RS documentation. But as I
mentioned earlier, height is 11 in. and width is 8.5 in. and this is standard
A4 portrait size. Still in Excel, the orientation appears as Landscape.
Is it possible you can try a simple report with this page setting and see
the results in Excel. If Excel has its landscape, then it seems like a bug in
RS Excel export functionality.
Please let me know about it.
Thanks,
Milin
"PSM" wrote:
> Yes, I was talking about the CanGrow and CanShrink properties. I have never
> tried to select a report orientation when I have exported to Excel but, I
> have found something I hope you find it useful.
> When you export a report to Excel, the width and the height of the page are
> looked up in a internal table where there are the most used sizes and
> orientations. If they exist in the table, the page format is chosen. If not
> so, orientation is determined by comparing height with width. If height is
> smaller than width, landscape is chosen.
> "Milu" wrote:
> > Thanks for ur reply.
> >
> > If u r talking about the "CanGrow" and "CanShrink" properties, I tried
> > setting them both, true and false. But none of this helped. Is this related
> > to report orientation changing to landscape?
> >
> > If you have got positive result trying something, please do let me know.
> >
> > Thanks,
> > Milin
> >
> > "PSM" wrote:
> >
> > > According to the RS help, when you export a report to Excel, this doesn't
> > > take to account the format of the page. There are, in field properties, two
> > > options that allow the field is able to enlarge and shrink. Have you tried
> > > not selecting them?
> > >
> > > "Milu" wrote:
> > >
> > > > I have report page height defined as 11 in. and width as 8.5 in. When I
> > > > export the report to pdf, it comes fine in portrait format. But when the
> > > > report is exported to Excel, the orientation is changed to Landscape. Even
> > > > the page margins are not preserved.
> > > >
> > > > Is it that Excel overrides the report page setup or RS changes the page
> > > > settings by itself when the report is exported? I changed the default printer
> > > > settings to have "portrait" as the default print format. But still exporting
> > > > the report to Excel changes this.
> > > >
> > > > Please let me know if you have found any solution to such a problem.
> > > >
> > > > Thanks in advance,
> > > > Milin|||I have been checking a report and it works like you say. Excel always use
landscape orientation. I think you are right. It seems like a bug in RS Excel
export functionality.
I am sorry I have been of no help.
"Milu" wrote:
> Yes, I came across that internal table stuff in RS documentation. But as I
> mentioned earlier, height is 11 in. and width is 8.5 in. and this is standard
> A4 portrait size. Still in Excel, the orientation appears as Landscape.
> Is it possible you can try a simple report with this page setting and see
> the results in Excel. If Excel has its landscape, then it seems like a bug in
> RS Excel export functionality.
> Please let me know about it.
> Thanks,
> Milin
>
> "PSM" wrote:
> > Yes, I was talking about the CanGrow and CanShrink properties. I have never
> > tried to select a report orientation when I have exported to Excel but, I
> > have found something I hope you find it useful.
> > When you export a report to Excel, the width and the height of the page are
> > looked up in a internal table where there are the most used sizes and
> > orientations. If they exist in the table, the page format is chosen. If not
> > so, orientation is determined by comparing height with width. If height is
> > smaller than width, landscape is chosen.
> >
> > "Milu" wrote:
> >
> > > Thanks for ur reply.
> > >
> > > If u r talking about the "CanGrow" and "CanShrink" properties, I tried
> > > setting them both, true and false. But none of this helped. Is this related
> > > to report orientation changing to landscape?
> > >
> > > If you have got positive result trying something, please do let me know.
> > >
> > > Thanks,
> > > Milin
> > >
> > > "PSM" wrote:
> > >
> > > > According to the RS help, when you export a report to Excel, this doesn't
> > > > take to account the format of the page. There are, in field properties, two
> > > > options that allow the field is able to enlarge and shrink. Have you tried
> > > > not selecting them?
> > > >
> > > > "Milu" wrote:
> > > >
> > > > > I have report page height defined as 11 in. and width as 8.5 in. When I
> > > > > export the report to pdf, it comes fine in portrait format. But when the
> > > > > report is exported to Excel, the orientation is changed to Landscape. Even
> > > > > the page margins are not preserved.
> > > > >
> > > > > Is it that Excel overrides the report page setup or RS changes the page
> > > > > settings by itself when the report is exported? I changed the default printer
> > > > > settings to have "portrait" as the default print format. But still exporting
> > > > > the report to Excel changes this.
> > > > >
> > > > > Please let me know if you have found any solution to such a problem.
> > > > >
> > > > > Thanks in advance,
> > > > > Milin|||I've also experienced this problem. It appears that Excel uses the
ratio of body height to body width to determine orientation instead of
page size. If you set the body height to be greater than the body
width, it will be portrait orientation when exported to Excel.
PSM wrote:
> I have been checking a report and it works like you say. Excel always use
> landscape orientation. I think you are right. It seems like a bug in RS Excel
> export functionality.
> I am sorry I have been of no help.
> "Milu" wrote:
> > Yes, I came across that internal table stuff in RS documentation. But as I
> > mentioned earlier, height is 11 in. and width is 8.5 in. and this is standard
> > A4 portrait size. Still in Excel, the orientation appears as Landscape.
> >
> > Is it possible you can try a simple report with this page setting and see
> > the results in Excel. If Excel has its landscape, then it seems like a bug in
> > RS Excel export functionality.
> >
> > Please let me know about it.
> >
> > Thanks,
> > Milin
> >
> >
> > "PSM" wrote:
> >
> > > Yes, I was talking about the CanGrow and CanShrink properties. I have never
> > > tried to select a report orientation when I have exported to Excel but, I
> > > have found something I hope you find it useful.
> > > When you export a report to Excel, the width and the height of the page are
> > > looked up in a internal table where there are the most used sizes and
> > > orientations. If they exist in the table, the page format is chosen. If not
> > > so, orientation is determined by comparing height with width. If height is
> > > smaller than width, landscape is chosen.
> > >
> > > "Milu" wrote:
> > >
> > > > Thanks for ur reply.
> > > >
> > > > If u r talking about the "CanGrow" and "CanShrink" properties, I tried
> > > > setting them both, true and false. But none of this helped. Is this related
> > > > to report orientation changing to landscape?
> > > >
> > > > If you have got positive result trying something, please do let me know.
> > > >
> > > > Thanks,
> > > > Milin
> > > >
> > > > "PSM" wrote:
> > > >
> > > > > According to the RS help, when you export a report to Excel, this doesn't
> > > > > take to account the format of the page. There are, in field properties, two
> > > > > options that allow the field is able to enlarge and shrink. Have you tried
> > > > > not selecting them?
> > > > >
> > > > > "Milu" wrote:
> > > > >
> > > > > > I have report page height defined as 11 in. and width as 8.5 in. When I
> > > > > > export the report to pdf, it comes fine in portrait format. But when the
> > > > > > report is exported to Excel, the orientation is changed to Landscape. Even
> > > > > > the page margins are not preserved.
> > > > > >
> > > > > > Is it that Excel overrides the report page setup or RS changes the page
> > > > > > settings by itself when the report is exported? I changed the default printer
> > > > > > settings to have "portrait" as the default print format. But still exporting
> > > > > > the report to Excel changes this.
> > > > > >
> > > > > > Please let me know if you have found any solution to such a problem.
> > > > > >
> > > > > > Thanks in advance,
> > > > > > Milin