Export fixed length field to text file 1

[PauloMongo]

I am trying to export a report to a character separated file that the fields need to be set at a fixed length. I export the page header and also the group header information. The PH is the header record of the export file, the GH shows the main information.

When I export report the PH information appears on every line of the GH information, is there anyway in which this can stop.

 

[kenhamady]

If you want the PH to only appear once, you can't use character or comma separated format. You will have to use regular text format, and then you have to put the commas in yourself.

To make all values a fixed length you add extra spaces (too many) and then trim back down to the correct length:

right ( " " + {field} , 8 )

will always return 8 characters regardless of the length of the field. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[kenhamady]

If you want an 8 character string, you probably want it padded on the left:

left ( {field} + " " , 8 ) Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[PauloMongo]

I want the report to export data like this:-

A EXTAD400FMR------200224y200223120602C**D10181

The characters upto the C are my header record, I only want these to appear once, I export using CHR format and remove the separator and delimiter, I have tried to add the PH to the GH section and surpress if duplicated but this still does not work. Any other ideas as this has to be in this format.


regards,



Paul Valentine

 

[kenhamady]

Any comma separated or delimited export will repeat header objects on every record, that is a fixed behavior of CR. There is no workaround.

The only way to have header objects appear once is to use a document export, like text. You would use formulas to get the data to look the way you want it to. I have done several of these files for things like EDI transmissions and they are a pain but can be done. The usual limitation is the length, because you are involving a printer driver and the margins make for limitations. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[Marichele]

Ken,

I took your advice on how to make a fixed length field in a text file. But I am having difficulty in that sometimes my field comes out the 10 char desired length and some times 11 char. Exact same data in the field for 10 records in a row, 3 of the 10 records show 11.

My formula:
RIGHT((SPACE(10)+TRIM({FEAD_DETAIL.ALIQUOT_SIZE})),10)

Aliquot_size is varchar2(24), the value in all ten records is '1.5'.

I have tried ' '; replicatestring(' ',10); subscript [1 to 10].

It formats correctly when I user replicatestring('*',10) but I cannot have '*' in the field.

Any suggestions would be welcome!

CR 8.5

Thank you

Thanks,
Mary

 

[kenhamady]

It is odd that the right function is allowing an extra character.

Where is the 11th character, on the left or the right?
What is the extra character?
What happens if you change the 10 to 8 as a test in your original formula?


Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides for Formulas, Parameters, Subreports, VB, .NET, Tips and Tricks

http://www.kenhamady.com/

 

[chelseatech]

I've have a client using CR as part of their EDI processing, Some of the file formats require fixed length fields.

So they use my disk output UFL to build the files from within a CR formula. Works well and you can do anything you like in there.

One of the things we do with the EDI data is to add extra lines to the export for things like freight, and promotional offers (Buy a Coffee Maker and get some mugs). With UFL formulas you can create the file any format you like. It doesn't even have to look like the report, it just has to use the data in the report.

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[Marichele]

Ken,

I solved it (I think)!!!
I had my charperinch reg setting at 15hex for textexport. On a whim I changed it to an even number 16hex and it worked! I experimented with different settings and the odds all added a space occasionally, the even (so far) has not. So, I will recommend to my client to use an even number in the reg setting. Their EDDs are long records, in order to keep the data from wrapping we use a large charperinch.

Thank you for responding I have always found your advice most helpfull. Keep up the good work.

The info you requested:
The 11th character is appearing on the left, I did try with a different length and the same thing happened. The thing is it isn't always on the same record. Out of 10 records the 3rd and 5th will have the extra one time and the next time maybe the 2nd and the 7th. The extra character only happens with a space, if I use any other character it is the correct length. But, of course, I need the space. I even tried creating the length with '*' and replacing with a ' '.



Thanks,
Mary