Hi,
I have a problem when I wish to format my file. The format that I wish is my format in server :
- Fixed-width columns
- Space within columns, the default value 0
The description of my file is :
NUM_CPT Varchar(50)
NAME_CLT Varchar(20)
SEXE_CLT Char(2)
ACCOUNT_CLT DEC(9.2)
Thank You fir your suggestions,
Pey
How can I do with PX for having the same result than in Server.
Format File
Moderators: chulett, rschirm, roy
Format File
Do you wish to specify a file format for importing or for exporting (i.e. writing)? Check out "Field Properties" for the import/export stage. I think you want to use the property 'padchar'.
-BP
-BP
What is the value that I need to write in the 'Pad Char' ?Format File
Is there a 'null value' at the field level? If so, try setting that to ' ' (a space) and then try running your job again.
- BP
- BP
How to provide fixed format flat file in PX
- A Datastage PX primer by Teej
(Hey, if Ken can do a big primer, I can!)
1. This solution will work as long as you output only integer or characters. You will need to convert your input fields either those two fields as needed.
2. The Format column for 6.0 is not used. I do not know whether 7.0 does use it.
3. You need to have a Transform stage immediately before the Sequential File output.
* * *
For the Sequential File Stage, you need to set the following values to ensure that the file is fixed. Click on the "Format" tab and set the following values for Record Level:
Record Delimiter = "UNIX newline" (if you're on UNIX)
Record length = fixed
For the Field defaults, you need to determine if you need to have a delimiter and/or quotes. If not, set both as "none."
Nothing else should be set up. I have seen use of the Type Default, but with the variations on my fields, I rarely can find a situation where it addresses EVERYTHING.
* * *
If you have an output exclusively set as varchar, the task is really simple, set the transform output for all fields to "CHAR" and the set length for each field. You MUST set all fields as NOT Nullable.
Within each field's code, you will have to determine whether you wish to Right-justify or Left-justify your data. You can use the Left(), Right(), Space(), and PadString() functions to handle the formatting.
Finally, you must handle the nullability possibility:
If IsNull(field) Then Space([size of field]) Else [formatting behavior for field]
* * *
The integers are a bit more tricky. Ensure that the fields are NOT Nullable. If you want the space to be blank, you will need to convert that field to Characters, and handle the null value appropriately as detailed above.
In the Sequential File Stage, click on "Columns" tab, and then double-click the column number next to the field you are fixing (Integers only.) You should see "Edit Column Meta Data" screen. Add two fields to the Integer Type folder under Properties:
Out_Format = %[size]d (for example %10d for a 10 digit field)
Field width = [size]
This will provide a right-justified non-zero-filled integer field.
* * *
Now how do we properly convert Decimal fields to Char? *chuckles* That's a subject that I personally struggled with for a while until I came across this solution.
In this situation, I'm trying to output a properly formatted dollars and cents, so I need to have 2 decimal places at all time. So this is pretty big. This is a field that is 6 character long, so adjust accordingly:
I will leave the background up to you folks as an exercise. The problem with 6.x is that DataStage PX internally use leading and trailing zeros, and there is no functions to trim that available for PX. Trim is for space characters only. I am sure that 7.x will have that function, so obviously the above code will be simplified.
If you have any other complex requests, consulting is available.
-T.J.
- A Datastage PX primer by Teej
(Hey, if Ken can do a big primer, I can!)
1. This solution will work as long as you output only integer or characters. You will need to convert your input fields either those two fields as needed.
2. The Format column for 6.0 is not used. I do not know whether 7.0 does use it.
3. You need to have a Transform stage immediately before the Sequential File output.
* * *
For the Sequential File Stage, you need to set the following values to ensure that the file is fixed. Click on the "Format" tab and set the following values for Record Level:
Record Delimiter = "UNIX newline" (if you're on UNIX)
Record length = fixed
For the Field defaults, you need to determine if you need to have a delimiter and/or quotes. If not, set both as "none."
Nothing else should be set up. I have seen use of the Type Default, but with the variations on my fields, I rarely can find a situation where it addresses EVERYTHING.
* * *
If you have an output exclusively set as varchar, the task is really simple, set the transform output for all fields to "CHAR" and the set length for each field. You MUST set all fields as NOT Nullable.
Within each field's code, you will have to determine whether you wish to Right-justify or Left-justify your data. You can use the Left(), Right(), Space(), and PadString() functions to handle the formatting.
Finally, you must handle the nullability possibility:
If IsNull(field) Then Space([size of field]) Else [formatting behavior for field]
* * *
The integers are a bit more tricky. Ensure that the fields are NOT Nullable. If you want the space to be blank, you will need to convert that field to Characters, and handle the null value appropriately as detailed above.
In the Sequential File Stage, click on "Columns" tab, and then double-click the column number next to the field you are fixing (Integers only.) You should see "Edit Column Meta Data" screen. Add two fields to the Integer Type folder under Properties:
Out_Format = %[size]d (for example %10d for a 10 digit field)
Field width = [size]
This will provide a right-justified non-zero-filled integer field.
* * *
Now how do we properly convert Decimal fields to Char? *chuckles* That's a subject that I personally struggled with for a while until I came across this solution.
In this situation, I'm trying to output a properly formatted dollars and cents, so I need to have 2 decimal places at all time. So this is pretty big. This is a field that is 6 character long, so adjust accordingly:
Code: Select all
If IsNull([field]) Then
Space(6)
Else If ([field] = 0) Then
Space(2) : "0.00"
Else If ([field] < 0) And (Abs([field]) < 0.10) Then
Space(1) : "-0.0" : Right([field], 1)
Else If ([field] < 0) And (Abs([field]) < 1.00) Then
Space(1) : "-0." : Right([field] * 100,2)
Else If ([field] < 0) Then
Right(Space(6) : "-" : (Field(Trim(Trim([field], "-", "L"), "0", "L"), ".",1) : "." : Right([field] * 100,2)), 6)
Else If ([field] < 0.10) Then
Space(2) : "0.0" : Right([field], 1)
Else If ([field] < 1.00) Then
Space(2) : "0." : Right([field] * 100,2)
Else
Right(Space(6) : (Field(Trim(Trim([field]), "0", "L"), ".",1) : "." : Right([field] * 100,2)), 6)
If you have any other complex requests, consulting is available.
-T.J.
Developer of DataStage Parallel Engine (Orchestrate).
Thank You Teej,
I know that I can Pad in my transform my field. That's what I do now but I think that it is a weakness in the code which it does not enable us to format it correctly and directly in the stage option 'format'. It give us the possibility but with version 6, it doesn't work.
But we migrate to version 7 next week and like Carter ask me that with version 7 it's OK, I will be waiting.
Thank,
Pey
I know that I can Pad in my transform my field. That's what I do now but I think that it is a weakness in the code which it does not enable us to format it correctly and directly in the stage option 'format'. It give us the possibility but with version 6, it doesn't work.
But we migrate to version 7 next week and like Carter ask me that with version 7 it's OK, I will be waiting.
Thank,
Pey