delete the last row

[samp]

Hello,

i would like to delete the last row of my flat file, because it contains a wrong data.
my idea was to use 2 transfo in order to count the number of rows in the first one, and select the good lines (number of row-1) in the second one.

Can you help me? thank you

[raju_chvr]

did ur idea work. U shld pass the number of rows from the Trans1 to Trans2 in ur approach. You can achieve this by writing the number of rows in Trans1 to a flat file and use that flat file in Trans2.

Or why can't you just delete manually urself or use UNIX to do it. U can even do this using UNIX. just find the number of rows of data and delete the last row.

[kcbland]

Hello,

i would like to delete the last row of my flat file, because it contains a wrong data.

Do you mean you want to manipulate the original file to strip off the last row? Or, do you mean while processing this file in a DataStage transformation you wish the last row to be skipped? Please clarify.

If your wish is to simply manipulate an existing file to remove the last row, why use DataStage? Because it's there? You are on Windows, but do you have access to any Unix shells for Windows, such as MKS toolkit? If you did, a simple head command will parse the file and give you want you want.

If your wish is to process the file, but ignore the last row, you should use a stage variable that on initialization goes out and line counts your source file. If you have MKS toolkit, it's just wc -l filename. Your output link constaint should be:

Code: Select all

@INROWNUM < StageVariableOfFileRowCount

[samp]

yes, i want to manipulate the original file to strip off the last row.

And i can't drop the line manually, because each month, a new file is created and the last line is wrong.

i use wc -l whith unix shell in a routine and now i've got the number of row. but when i compare it to @INROWNUM, i've got this pb :

exemple :
i've got 43 lines in the original file and i want to keep, the first 42.

in a constraint i put :"@INROWNUM<stagevariablerowcount"
it doesn'twork because if rownum in 5,6,7,8,9, datastage consider then datastage consider that it's greater than 43 !!!

[kcbland]

For whatever reason it's doing a string comparison instead of numeric.

Try:

Code: Select all

@INROWNUM<NUM(stagevariablerowcount)

[ray.wurlod]

If you can detect the wrong data in the last line, why not simply have your job ignore that line?
For example, change the "missing columns" rule to pad with some data that don't exist elsewhere in the file, then have the Transformer stage output constraint expression filter that pad data out.
Much easier, probably, than tampering with the input file.

[samp]

You're right.
I've yet create a job wich detect the wrong character in a line, but i would like to perform the treatment in order to utilize it in other projects.

That's why, i prefer to drop the last line of a file in general, without testing any character.

So now, i've stored the number of line of my file in a stage variable, but the routine who calculate the number of line give me a non numeric data.
when i compare it to @INROWNUM (numeric data) i've got a problem.

How can i convert a non numeric data to a numeric?
i try NUM(StageVariable) but, systematically, it answer me 0 !!!

[ray.wurlod]

The NUM function only ever returns 1 or 0 (true or false), depending on whether its argument is numeric.
You could use an expression like

Code: Select all

If Num(link.column) Then link.column Else 0

to replace non-numeric values with 0 without generating any warning message.

[samp]

ok, but that's not exactly what i want.

my routine generate a number example "42" which is char. i would like to transform that value in numeric in order to compare it with @INROWNUM.

i try Iconv, Oconv, fmt, but i never succeed in transforming my char in Num .

because, when i compare @INROWNUM < stagevariable, datastage compare the first character, and if it's equal, he compare the second.
exemple :
@INROWNUM=12 and stagevariable=42 --> it works.
but
@INROWNUM=5 and stagevariable=42 --> it doesn't works.

[kcbland]

I'm so stupid. I told you to use NUM. I am sooooo sorry. Just add zero to your value.

Code: Select all

@INROWNUM< (stagevariablerowcount + 0)

[samp]

it doesn't matter.
i try to multiplie by one or make an addition (+0) as you indicates me.
but the variable contains 0 now !!!

i try to understand, so i ask for the length of the variable.

Stagevariable=42 and len(stagevariable)=3 !!!


i try to delete the space which is at the end of the value, so i try
trim(satgevariable,"T") but it doesn't work.

so i ask you the question "which is th fonction to delete space on the right?"

[samp]

OK it's finished.

i found a solution.

thanks for your answers

[kcbland]

Care to share with the class your solution?

[samp]

this my solution :

1 - i create a routine which calculate the number of lines in a file :
Arg1 contains the name of the file (repository include)
but the output value contains spaces before and after the number of line, tht's why i use a trim (for the left spaces) and a variable that plot the length of the output value (long).

Code: Select all

FileName=Arg1
Command="more ": FileName :"| wc -l"
Output = "" 
Code = 0
Call DSExecute("UNIX", Command, Output, Code)
long=len(Trim(Output))
Ans=Trim(Output)[1,long-1]

2 - my job contains a sequential file, a transformer and a sequential file as target for the data.
in the transformer, i create a stage variable (RowNumber) which contains the result of my routine

and as a constraint to select the data i put :

Code: Select all

@INROWNUM< RowNumber

and that's all.

[chulett]

Just a nit, but your UNIX command doesn't need the 'more' piping to 'word count' pieces:

Code: Select all

Command="wc -l " : FileName