exporting pdf file issue in reporting services

I am trying to export a report as a pdf file which may be around 160MB in
size and the report is giving issues, its timing out or gives me out of
memory, can someone let me know if you faced an issue like this and how it
can be fixed.Look at the available resources that you have on the server (IIS, RAM) the
other thing make your IIS session longer, if I am not wrong you can set it in
reporting services configuration if not go to your IIS administration and set
it for the reportserver site.
"jagan" wrote:
> I am trying to export a report as a pdf file which may be around 160MB in
> size and the report is giving issues, its timing out or gives me out of
> memory, can someone let me know if you faced an issue like this and how it
> can be fixed.|||I already tried this and also increased the session time out, but not working.
"Paul.G." wrote:
> Look at the available resources that you have on the server (IIS, RAM) the
> other thing make your IIS session longer, if I am not wrong you can set it in
> reporting services configuration if not go to your IIS administration and set
> it for the reportserver site.
> "jagan" wrote:
> > I am trying to export a report as a pdf file which may be around 160MB in
> > size and the report is giving issues, its timing out or gives me out of
> > memory, can someone let me know if you faced an issue like this and how it
> > can be fixed.|||What version of RS?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"jagan" <jagan@.discussions.microsoft.com> wrote in message
news:9E4AA6AE-DB11-4600-B75D-EFDF49DD5277@.microsoft.com...
>I am trying to export a report as a pdf file which may be around 160MB in
> size and the report is giving issues, its timing out or gives me out of
> memory, can someone let me know if you faced an issue like this and how it
> can be fixed.

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 pdf / wrong orientation

Hello Everybody,

I have a Report created under "Reporting Services 2005". When I want to export to a pdf, it has the wrong orientation and the Size seems to big as well.
I would like to have a A4 Landscape pdf.

Thanks a lot for any kind of Help!

Change the dimensions of the report under the 'Report Properties' dialogue under the 'Report' menu.|||thanks, but I have already tried that|||Have you changed the dimensiosn to width: 11" and height 8.5" and set the margins to 0"?

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 - file size bloat

When exporting to Excel from Report Manager, we are finding that the file
size is bloated. If I open the exported file, then do a Save As, the file
size drops by at least 50%.
Any thoughts?Make sure your on SP2 - we made some big improvements over RTM in Excel file
size.
There are some optimizations the Excel format is capable of that we do not
currently support. 50% difference seems high but is theoretically possible
even with SP2.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kristen" <Kristen@.discussions.microsoft.com> wrote in message
news:40F258B6-7744-4625-9CDB-B71603C2E225@.microsoft.com...
> When exporting to Excel from Report Manager, we are finding that the file
> size is bloated. If I open the exported file, then do a Save As, the file
> size drops by at least 50%.
> Any thoughts?|||Already on SP2.
"Donovan Smith [MSFT]" wrote:
> Make sure your on SP2 - we made some big improvements over RTM in Excel file
> size.
> There are some optimizations the Excel format is capable of that we do not
> currently support. 50% difference seems high but is theoretically possible
> even with SP2.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Kristen" <Kristen@.discussions.microsoft.com> wrote in message
> news:40F258B6-7744-4625-9CDB-B71603C2E225@.microsoft.com...
> > When exporting to Excel from Report Manager, we are finding that the file
> > size is bloated. If I open the exported file, then do a Save As, the file
> > size drops by at least 50%.
> >
> > Any thoughts?
>
>|||I also noticed the same thing with all service packs.
"Kristen" <Kristen@.discussions.microsoft.com> wrote in message
news:414B02C5-4F60-4D70-AA2D-22B95CD210F5@.microsoft.com...
> Already on SP2.
> "Donovan Smith [MSFT]" wrote:
>> Make sure your on SP2 - we made some big improvements over RTM in Excel
>> file
>> size.
>> There are some optimizations the Excel format is capable of that we do
>> not
>> currently support. 50% difference seems high but is theoretically
>> possible
>> even with SP2.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Kristen" <Kristen@.discussions.microsoft.com> wrote in message
>> news:40F258B6-7744-4625-9CDB-B71603C2E225@.microsoft.com...
>> > When exporting to Excel from Report Manager, we are finding that the
>> > file
>> > size is bloated. If I open the exported file, then do a Save As, the
>> > file
>> > size drops by at least 50%.
>> >
>> > Any thoughts?
>>|||I am having the same problem. Not only the huge size frezes the exporting also.
"Amila Liyanaarchchi" wrote:
> I also noticed the same thing with all service packs.
>
> "Kristen" <Kristen@.discussions.microsoft.com> wrote in message
> news:414B02C5-4F60-4D70-AA2D-22B95CD210F5@.microsoft.com...
> > Already on SP2.
> >
> > "Donovan Smith [MSFT]" wrote:
> >
> >> Make sure your on SP2 - we made some big improvements over RTM in Excel
> >> file
> >> size.
> >>
> >> There are some optimizations the Excel format is capable of that we do
> >> not
> >> currently support. 50% difference seems high but is theoretically
> >> possible
> >> even with SP2.
> >>
> >> --
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >> "Kristen" <Kristen@.discussions.microsoft.com> wrote in message
> >> news:40F258B6-7744-4625-9CDB-B71603C2E225@.microsoft.com...
> >> > When exporting to Excel from Report Manager, we are finding that the
> >> > file
> >> > size is bloated. If I open the exported file, then do a Save As, the
> >> > file
> >> > size drops by at least 50%.
> >> >
> >> > Any thoughts?
> >>
> >>
> >>
>
>|||If you have a lot of data, try exporting as CSV ASCII. In 2005 you can have
Report Manager default to this when exporting from there.
Depending on how you design your reports you can do the following to export
to Excel. Or, what I do sometimes is make a copy of the report and clean it
up for data export and then hide it in list view. If you export from Report
Manager it puts CSV data in unicode which Excel puts all in one column. If
you export in ASCII then Excel does just as you want. To prevent a problem
with cells (Excel will object to sorting the data) you need to remove any
textboxes you have (for instance with a title, showing the parameters run
etc) and instead add additional header rows, merge the cells and put your
text in there instead. I add a link at the top of the report that says
Export Data. With RS 2005 you will be able to configure it to use ASCII
instead of Unicode.
Here is an example of a Jump to URL link I use. This causes Excel to come up
with the data in a separate window:
="javascript:void(window.open('" & Globals!ReportServerUrl &
"?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
"&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
If you don't want to have it appear in a new window then do this in jump to
URL:
=Globals!ReportServerUrl & "?/SomeFolder/SomeReport&ParamName=" &
Parameters!ParamName.Value & "&rs:Format=CSV&rc:Encoding=ASCII"
Very nice and very fast.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"sun" <sun@.discussions.microsoft.com> wrote in message
news:CB2B8A34-3994-444C-9059-E5FAB6CFE764@.microsoft.com...
>I am having the same problem. Not only the huge size frezes the exporting
>also.
> "Amila Liyanaarchchi" wrote:
>> I also noticed the same thing with all service packs.
>>
>> "Kristen" <Kristen@.discussions.microsoft.com> wrote in message
>> news:414B02C5-4F60-4D70-AA2D-22B95CD210F5@.microsoft.com...
>> > Already on SP2.
>> >
>> > "Donovan Smith [MSFT]" wrote:
>> >
>> >> Make sure your on SP2 - we made some big improvements over RTM in
>> >> Excel
>> >> file
>> >> size.
>> >>
>> >> There are some optimizations the Excel format is capable of that we do
>> >> not
>> >> currently support. 50% difference seems high but is theoretically
>> >> possible
>> >> even with SP2.
>> >>
>> >> --
>> >> This posting is provided "AS IS" with no warranties, and confers no
>> >> rights.
>> >>
>> >>
>> >> "Kristen" <Kristen@.discussions.microsoft.com> wrote in message
>> >> news:40F258B6-7744-4625-9CDB-B71603C2E225@.microsoft.com...
>> >> > When exporting to Excel from Report Manager, we are finding that the
>> >> > file
>> >> > size is bloated. If I open the exported file, then do a Save As,
>> >> > the
>> >> > file
>> >> > size drops by at least 50%.
>> >> >
>> >> > Any thoughts?
>> >>
>> >>
>> >>
>>|||HI there,
we have the same problem. I have a report with 389 records having 23
columns. The file size is 2.7 MByte!!! (124 kByte as CSV) The problem
(feature) is, that RS saves the file as Excel XML. That's progress to you!!!
If you open the file in Excel and save it as normal Excel format, you will
get a much smaller fle size!
Honestly, I am not impressed. I get files with around 7700 records which is
80 MByte!!! It is not possible to open such a file! If I save this file as
CSV it is just over 12 MByte! But if you think you can just double click on
the CSV you'll get another disappointment. EXCEL opens it with one line per
record. Because it is Unicode! If I transform it to ASCII it is 6 MByte!
Don't get me wrong, I love RS! But the export function just sucks! Sorry!
Cheers
Peter
"Kristen" wrote:
> When exporting to Excel from Report Manager, we are finding that the file
> size is bloated. If I open the exported file, then do a Save As, the file
> size drops by at least 50%.
> Any thoughts?|||You are running RS 2000 with no service packs. Install either SP1 or SP2.
The Excel export was changed with service pack one to use native format.
If RS 2005 you can set up Report Manager to export CSV in ASCII format. In
RS 2000 I added a link to export to CSV using ASCII.
At a minimum I suggest install Service Pack 2. Also, RS 2005 has been a
problem free upgrade for me and has some very good new features: faster,
multi-select parameters, date picker, end user sorting, etc.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"PeterNZ" <PeterNZ@.discussions.microsoft.com> wrote in message
news:2AC2CDF4-A73E-4B65-BFE1-E2A037223806@.microsoft.com...
> HI there,
> we have the same problem. I have a report with 389 records having 23
> columns. The file size is 2.7 MByte!!! (124 kByte as CSV) The problem
> (feature) is, that RS saves the file as Excel XML. That's progress to
> you!!!
> If you open the file in Excel and save it as normal Excel format, you will
> get a much smaller fle size!
> Honestly, I am not impressed. I get files with around 7700 records which
> is
> 80 MByte!!! It is not possible to open such a file! If I save this file as
> CSV it is just over 12 MByte! But if you think you can just double click
> on
> the CSV you'll get another disappointment. EXCEL opens it with one line
> per
> record. Because it is Unicode! If I transform it to ASCII it is 6 MByte!
> Don't get me wrong, I love RS! But the export function just sucks! Sorry!
> Cheers
> Peter
> "Kristen" wrote:
>> When exporting to Excel from Report Manager, we are finding that the file
>> size is bloated. If I open the exported file, then do a Save As, the
>> file
>> size drops by at least 50%.
>> Any thoughts?|||Hiya Bruce,
first of all, I am sorry that I wrote this post yesterday. Too many emotions
because I had all this trouble with the Excel Export. You are absolutely
right, SP2 fixes the problem. I now have to convince the people to update a
production server with SP2.
I know that the upgrade to 2005 improves a lot. But I guess you are in IT
for quite a while. Big organisations usually have a problem with upgrading.
We techos can't understand this. I, for example work in a world wide IT
company. Can you imagine that we still run on XP without SP2?
I am also quite concerned about Excel and XML based on the experiences I
made with the RS Excel file size. If over 7000 rows in a spreadsheet have a
filesize of over 80 MByte, then there is definitely a problem. But that's of
course a different story.
Anyway, I get into waffling.
Thank you for your help with this. Very much appreciated!
Cheers
Peter
"Bruce L-C [MVP]" wrote:
> You are running RS 2000 with no service packs. Install either SP1 or SP2.
> The Excel export was changed with service pack one to use native format.
> If RS 2005 you can set up Report Manager to export CSV in ASCII format. In
> RS 2000 I added a link to export to CSV using ASCII.
> At a minimum I suggest install Service Pack 2. Also, RS 2005 has been a
> problem free upgrade for me and has some very good new features: faster,
> multi-select parameters, date picker, end user sorting, etc.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "PeterNZ" <PeterNZ@.discussions.microsoft.com> wrote in message
> news:2AC2CDF4-A73E-4B65-BFE1-E2A037223806@.microsoft.com...
> > HI there,
> >
> > we have the same problem. I have a report with 389 records having 23
> > columns. The file size is 2.7 MByte!!! (124 kByte as CSV) The problem
> > (feature) is, that RS saves the file as Excel XML. That's progress to
> > you!!!
> > If you open the file in Excel and save it as normal Excel format, you will
> > get a much smaller fle size!
> >
> > Honestly, I am not impressed. I get files with around 7700 records which
> > is
> > 80 MByte!!! It is not possible to open such a file! If I save this file as
> > CSV it is just over 12 MByte! But if you think you can just double click
> > on
> > the CSV you'll get another disappointment. EXCEL opens it with one line
> > per
> > record. Because it is Unicode! If I transform it to ASCII it is 6 MByte!
> >
> > Don't get me wrong, I love RS! But the export function just sucks! Sorry!
> >
> > Cheers
> >
> > Peter
> >
> > "Kristen" wrote:
> >
> >> When exporting to Excel from Report Manager, we are finding that the file
> >> size is bloated. If I open the exported file, then do a Save As, the
> >> file
> >> size drops by at least 50%.
> >>
> >> Any thoughts?
>
>|||One thing to remember when it comes to RS 2005. You can upgrade to RS 2005
without upgrade SQL Server to 2005. That might be easier to do since in
large organizations, database versions are changed slowly (I work for a
fortune 100 company, maybe fortune 50). You do need a SQL Server 2005
license.
By the way, my company is also on XP with SP1, not SP2. However, for RS you
are only affecting reporting services, not the database, not the users.
Another biggie with going to SP1 or SP2 is you get client side printing.
Just based on that it is worthwhile to install the service pack.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"PeterNZ" <PeterNZ@.discussions.microsoft.com> wrote in message
news:BFC3D54D-6F05-42E7-B4B6-1B75FFAFC95A@.microsoft.com...
> Hiya Bruce,
> first of all, I am sorry that I wrote this post yesterday. Too many
> emotions
> because I had all this trouble with the Excel Export. You are absolutely
> right, SP2 fixes the problem. I now have to convince the people to update
> a
> production server with SP2.
> I know that the upgrade to 2005 improves a lot. But I guess you are in IT
> for quite a while. Big organisations usually have a problem with
> upgrading.
> We techos can't understand this. I, for example work in a world wide IT
> company. Can you imagine that we still run on XP without SP2?
> I am also quite concerned about Excel and XML based on the experiences I
> made with the RS Excel file size. If over 7000 rows in a spreadsheet have
> a
> filesize of over 80 MByte, then there is definitely a problem. But that's
> of
> course a different story.
> Anyway, I get into waffling.
> Thank you for your help with this. Very much appreciated!
> Cheers
> Peter
> "Bruce L-C [MVP]" wrote:
>> You are running RS 2000 with no service packs. Install either SP1 or SP2.
>> The Excel export was changed with service pack one to use native format.
>> If RS 2005 you can set up Report Manager to export CSV in ASCII format.
>> In
>> RS 2000 I added a link to export to CSV using ASCII.
>> At a minimum I suggest install Service Pack 2. Also, RS 2005 has been a
>> problem free upgrade for me and has some very good new features: faster,
>> multi-select parameters, date picker, end user sorting, etc.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "PeterNZ" <PeterNZ@.discussions.microsoft.com> wrote in message
>> news:2AC2CDF4-A73E-4B65-BFE1-E2A037223806@.microsoft.com...
>> > HI there,
>> >
>> > we have the same problem. I have a report with 389 records having 23
>> > columns. The file size is 2.7 MByte!!! (124 kByte as CSV) The problem
>> > (feature) is, that RS saves the file as Excel XML. That's progress to
>> > you!!!
>> > If you open the file in Excel and save it as normal Excel format, you
>> > will
>> > get a much smaller fle size!
>> >
>> > Honestly, I am not impressed. I get files with around 7700 records
>> > which
>> > is
>> > 80 MByte!!! It is not possible to open such a file! If I save this file
>> > as
>> > CSV it is just over 12 MByte! But if you think you can just double
>> > click
>> > on
>> > the CSV you'll get another disappointment. EXCEL opens it with one line
>> > per
>> > record. Because it is Unicode! If I transform it to ASCII it is 6
>> > MByte!
>> >
>> > Don't get me wrong, I love RS! But the export function just sucks!
>> > Sorry!
>> >
>> > Cheers
>> >
>> > Peter
>> >
>> > "Kristen" wrote:
>> >
>> >> When exporting to Excel from Report Manager, we are finding that the
>> >> file
>> >> size is bloated. If I open the exported file, then do a Save As, the
>> >> file
>> >> size drops by at least 50%.
>> >>
>> >> Any thoughts?
>>

Export to a Text file in SQL Server Express

I have a table in a database has the same filed with a text file:

RBW_AR_BOT_REC

Size

Comment

Justification

Padding

record_id

1

'R'

None

None

date

6

MMDDYY

None

None

time

4

HHMM

(24 hour time)

None

None

cust_num

8

1

- 9999999

Right

Zero

odometer

6

0

- 999999

Right

Zero

receipt

8

1

- 99999999

Right

Zero

exception_customer

1

'0' = No

'1' = Yes

None

None

valid_void

1

'0' = valid

'v' = void

None

None

Justification

Padding

None

None

So that, after export data from the table, the file file must has that struct
Tell me, how can i do?

None

None

None

None

Right

Zero

Right

Zero

Right

Zero

None

None

None

None

Justification

Padding

None

None

None

None

None

None

Right

Zero

Right

Zero

Right

Zero

None

None

None

None

I am going to move this to the sql express group at it is more a problem with using the software.

On another note, you can use the management studio for the express systems (Free Download) and have the query saved or displayed as text, or you could format up the sql script and then use the sqlcmd command line tool to execute that command and save the output as text. But with the information you supplied we are going to need more information, for exable an definition of the table and if you could format the output sample better in the forums message so it is easily read.

|||

hi,

there's no direct way to "export" to file in a defined format like old style random access files...

you can query a table object to return what you are looking for, but the export features are usually performed a client level (thus via an external application) and not at the server level..

you can perhaps use SqlCMD.exe (or oSql.exe), the command line (not graphics tools) provided with SQLExpress, providing the "-oc:\myfile.txt" parameter (-o indicates to output the result to the specified file) (more info and synopsis about SQLCmd at http://msdn2.microsoft.com/en-us/library/ms162773.aspx)... and play with some Transact-SQL function to obtain something similar to the required result..

say you are querying the AdventureWorks database for Person.Address table and you want to return the

[AddressID] (int) formatted as right padded 6 chars and the [ModifiedDate] (datetime) formatted with the yyyy-mm-dd (without time dimension) 8 chars values...

the typical query could be

SET NOCOUNT ON;

USE AdventureWorks;

GO

SELECT RIGHT(REPLICATE(' ' , 6) + CONVERT(varchar, AddressID), 6 ) AS AddressID,

CONVERT(varchar(10), ModifiedDate, 121) AS ModifiedDate

FROM Person.Address

ORDER BY ModifiedDate;

--<--

AddressID ModifiedDate
61 1996-07-24

234 1997-02-19

224 1997-12-05

11387 1997-12-29

1997-12-29

( result abridged )

and want the results to be output to c:\myRes.txt..

create a file to store your query like

<c:\myQuery>
SELECT RIGHT(REPLICATE(' ' , 6) + CONVERT(varchar, AddressID), 6 ) AS AddressID,
CONVERT(varchar(10), ModifiedDate, 121) AS ModifiedDate
FROM Person.Address
ORDER BY ModifiedDate;
</c:\myQuery>

and execute, from the command line, the SQLCmd utility like

c:\..\>sqlcmd -E -S(Local)\SQLExpress -dAdventureWorks -iC:\myQuery -oC:\myRes.txt

this will output the result as

AddressID ModifiedDate
61 1996-07-24

234 1997-02-19

224 1997-12-05

11387 1997-12-29

1 1998-01-04

( result abridged )

you can play with the other SQLCmd parameters as wel, like -h for repeted headers, -s for col separator and the like...

regards

Export table in comma delimited format - how to eliminate empty spaces

Hello:
When I export a table in comma delimited format, it works OK, but there is
one problem. If the field is varchar (or char) and has the size, for
instance, 50, there are empty spaces in the text file, so it looks like
this:
"CBAS ","Bass Gambling Supplies ",2
"CBW ","Ben's Wholesale ",3
"CTDI ","3 Diamond ",4
"NONE ","NON-CUSTOM ",1
I need to elimitate these free spaces somehow, so the text file would look
like this:
"CBAS","Bass Gambling Supplies",2
"CBW","Ben's Wholesale",3
"CTDI","3 Diamond",4
"NONE","NON-CUSTOM",1
How would I do it, either manually or programmatically?
I would appreciate your help.
Thank you,
Peter AfoninDid you try RTRIM() to remove the trailing spaces from the Stirngs
HTH
Satish Balusa
Corillian Corp.
"Peter Afonin" <pafo@.specialtypulltabs.com> wrote in message
news:efJOp115DHA.360@.TK2MSFTNGP12.phx.gbl...

quote:

---

> Hello:
> When I export a table in comma delimited format, it works OK, but there is
> one problem. If the field is varchar (or char) and has the size, for
> instance, 50, there are empty spaces in the text file, so it looks like
> this:
> "CBAS ","Bass Gambling Supplies ",2
> "CBW ","Ben's Wholesale ",3
> "CTDI ","3 Diamond ",4
> "NONE ","NON-CUSTOM ",1
> I need to elimitate these free spaces somehow, so the text file would look
> like this:
> "CBAS","Bass Gambling Supplies",2
> "CBW","Ben's Wholesale",3
> "CTDI","3 Diamond",4
> "NONE","NON-CUSTOM",1
> How would I do it, either manually or programmatically?
> I would appreciate your help.
> Thank you,
> --
> Peter Afonin
>

---

|||Peter,

quote:

---

> When I export a table in comma delimited format, it works OK, but
> there is one problem. If the field is varchar (or char) and has
> the size, for instance, 50, there are empty spaces in the text
> file, so it looks like this:
> I need to elimitate these free spaces somehow, so the text file
> would look like this:
> "CBAS","Bass Gambling Supplies",2
> "CBW","Ben's Wholesale",3
> "CTDI","3 Diamond",4
> "NONE","NON-CUSTOM",1
> How would I do it, either manually or programmatically?

---

You need to use a format file for this. Using the pubs..authors
tables
as an example, we'll bcp out of a view that looks like this:
use pubs
go
create view authors_csv as
select null first_quote, * from authors
Note that we are including a dummy column called first_quote that
just
returns NULL. It's just a little trick to get the leading quote on
the first
column.
The format file looks like this:
8.0
10
1 SQLCHAR 0 0 "\"" 1 first_quote ""
2 SQLCHAR 0 11 "\",\"" 2 au_id ""
3 SQLCHAR 0 40 "\",\"" 3 au_lname ""
4 SQLCHAR 0 20 "\",\"" 4 au_fname ""
5 SQLCHAR 0 12 "\",\"" 5 phone ""
6 SQLCHAR 0 40 "\",\"" 6 address ""
7 SQLCHAR 0 20 "\",\"" 7 city ""
8 SQLCHAR 0 2 "\",\"" 8 state ""
9 SQLCHAR 0 5 "\",\"" 9 zip ""
10 SQLCHAR 0 1 "\"\r\n" 10 contract ""
That dummy column is also in the format file to get the leading
quote on au_id.
Here's the command line:
bcp pubs..authors_csv out
authors_csv.dat -fauthors_csv.bcp -Slindaw\ddbt -T
Linda|||Thank you everyone for your help, I'll try this.
Peter
"lindawie" <lindawie@.my-deja.com> wrote in message
news:#f4ibO25DHA.488@.TK2MSFTNGP12.phx.gbl...

quote:

---

> Peter,
>
> You need to use a format file for this. Using the pubs..authors
> tables
> as an example, we'll bcp out of a view that looks like this:
> use pubs
> go
> create view authors_csv as
> select null first_quote, * from authors
>
> Note that we are including a dummy column called first_quote that
> just
> returns NULL. It's just a little trick to get the leading quote on
> the first
> column.
> The format file looks like this:
> 8.0
> 10
> 1 SQLCHAR 0 0 "\"" 1 first_quote ""
> 2 SQLCHAR 0 11 "\",\"" 2 au_id ""
> 3 SQLCHAR 0 40 "\",\"" 3 au_lname ""
> 4 SQLCHAR 0 20 "\",\"" 4 au_fname ""
> 5 SQLCHAR 0 12 "\",\"" 5 phone ""
> 6 SQLCHAR 0 40 "\",\"" 6 address ""
> 7 SQLCHAR 0 20 "\",\"" 7 city ""
> 8 SQLCHAR 0 2 "\",\"" 8 state ""
> 9 SQLCHAR 0 5 "\",\"" 9 zip ""
> 10 SQLCHAR 0 1 "\"\r\n" 10 contract ""
> That dummy column is also in the format file to get the leading
> quote on au_id.
> Here's the command line:
> bcp pubs..authors_csv out
> authors_csv.dat -fauthors_csv.bcp -Slindaw\ddbt -T
> Linda
>

---

Export table in comma delimited format - how to eliminate empty spaces

Hello:
When I export a table in comma delimited format, it works OK, but there is
one problem. If the field is varchar (or char) and has the size, for
instance, 50, there are empty spaces in the text file, so it looks like
this:
"CBAS ","Bass Gambling Supplies ",2
"CBW ","Ben's Wholesale ",3
"CTDI ","3 Diamond ",4
"NONE ","NON-CUSTOM ",1
I need to elimitate these free spaces somehow, so the text file would look
like this:
"CBAS","Bass Gambling Supplies",2
"CBW","Ben's Wholesale",3
"CTDI","3 Diamond",4
"NONE","NON-CUSTOM",1
How would I do it, either manually or programmatically?
I would appreciate your help.
Thank you,
--
Peter AfoninDid you try RTRIM() to remove the trailing spaces from the Stirngs
--
HTH
Satish Balusa
Corillian Corp.
"Peter Afonin" <pafo@.specialtypulltabs.com> wrote in message
news:efJOp115DHA.360@.TK2MSFTNGP12.phx.gbl...
> Hello:
> When I export a table in comma delimited format, it works OK, but there is
> one problem. If the field is varchar (or char) and has the size, for
> instance, 50, there are empty spaces in the text file, so it looks like
> this:
> "CBAS ","Bass Gambling Supplies ",2
> "CBW ","Ben's Wholesale ",3
> "CTDI ","3 Diamond ",4
> "NONE ","NON-CUSTOM ",1
> I need to elimitate these free spaces somehow, so the text file would look
> like this:
> "CBAS","Bass Gambling Supplies",2
> "CBW","Ben's Wholesale",3
> "CTDI","3 Diamond",4
> "NONE","NON-CUSTOM",1
> How would I do it, either manually or programmatically?
> I would appreciate your help.
> Thank you,
> --
> Peter Afonin
>|||Peter,
> When I export a table in comma delimited format, it works OK, but
> there is one problem. If the field is varchar (or char) and has
> the size, for instance, 50, there are empty spaces in the text
> file, so it looks like this:
> I need to elimitate these free spaces somehow, so the text file
> would look like this:
> "CBAS","Bass Gambling Supplies",2
> "CBW","Ben's Wholesale",3
> "CTDI","3 Diamond",4
> "NONE","NON-CUSTOM",1
> How would I do it, either manually or programmatically?
You need to use a format file for this. Using the pubs..authors
tables
as an example, we'll bcp out of a view that looks like this:
use pubs
go
create view authors_csv as
select null first_quote, * from authors
Note that we are including a dummy column called first_quote that
just
returns NULL. It's just a little trick to get the leading quote on
the first
column.
The format file looks like this:
8.0
10
1 SQLCHAR 0 0 "\"" 1 first_quote ""
2 SQLCHAR 0 11 "\",\"" 2 au_id ""
3 SQLCHAR 0 40 "\",\"" 3 au_lname ""
4 SQLCHAR 0 20 "\",\"" 4 au_fname ""
5 SQLCHAR 0 12 "\",\"" 5 phone ""
6 SQLCHAR 0 40 "\",\"" 6 address ""
7 SQLCHAR 0 20 "\",\"" 7 city ""
8 SQLCHAR 0 2 "\",\"" 8 state ""
9 SQLCHAR 0 5 "\",\"" 9 zip ""
10 SQLCHAR 0 1 "\"\r\n" 10 contract ""
That dummy column is also in the format file to get the leading
quote on au_id.
Here's the command line:
bcp pubs..authors_csv out
authors_csv.dat -fauthors_csv.bcp -Slindaw\ddbt -T
Linda|||Thank you everyone for your help, I'll try this.
Peter
"lindawie" <lindawie@.my-deja.com> wrote in message
news:#f4ibO25DHA.488@.TK2MSFTNGP12.phx.gbl...
> Peter,
> > When I export a table in comma delimited format, it works OK, but
> > there is one problem. If the field is varchar (or char) and has
> > the size, for instance, 50, there are empty spaces in the text
> > file, so it looks like this:
> >
> > I need to elimitate these free spaces somehow, so the text file
> > would look like this:
> >
> > "CBAS","Bass Gambling Supplies",2
> > "CBW","Ben's Wholesale",3
> > "CTDI","3 Diamond",4
> > "NONE","NON-CUSTOM",1
> >
> > How would I do it, either manually or programmatically?
> You need to use a format file for this. Using the pubs..authors
> tables
> as an example, we'll bcp out of a view that looks like this:
> use pubs
> go
> create view authors_csv as
> select null first_quote, * from authors
>
> Note that we are including a dummy column called first_quote that
> just
> returns NULL. It's just a little trick to get the leading quote on
> the first
> column.
> The format file looks like this:
> 8.0
> 10
> 1 SQLCHAR 0 0 "\"" 1 first_quote ""
> 2 SQLCHAR 0 11 "\",\"" 2 au_id ""
> 3 SQLCHAR 0 40 "\",\"" 3 au_lname ""
> 4 SQLCHAR 0 20 "\",\"" 4 au_fname ""
> 5 SQLCHAR 0 12 "\",\"" 5 phone ""
> 6 SQLCHAR 0 40 "\",\"" 6 address ""
> 7 SQLCHAR 0 20 "\",\"" 7 city ""
> 8 SQLCHAR 0 2 "\",\"" 8 state ""
> 9 SQLCHAR 0 5 "\",\"" 9 zip ""
> 10 SQLCHAR 0 1 "\"\r\n" 10 contract ""
> That dummy column is also in the format file to get the leading
> quote on au_id.
> Here's the command line:
> bcp pubs..authors_csv out
> authors_csv.dat -fauthors_csv.bcp -Slindaw\ddbt -T
> Linda
>