Sequential File

martin · Post by **martin** » Mon Oct 24, 2005 3:14 pm

Hi Guys,

I am sourcing data from Sybase and dumping to Sequential File, But end of the data in one field is Poping-up in next Row.
Guys any solution.

7460 D05357 2 INH 20051017 20051021 20051024 WILLN MICHAEL PETERS,ERNIE WESTON CONCEPT AND FEASIBILITY HONG K 04-000
7461 D911241 10 INH 19921216 19921216 19930526 TRANSFER M. PARKER CASTLEAF PILOT
PLANT"
7462 D911251 10 INH 19920114 19920114 19930526 TRANSFER M. PARKER CASTLEAF PILOT
PLANT"
7463 D911287 10 INH 19920114 19920114 19930526 TRANSFER M. PARKER CASTLEAF PILOT PLANT

Thanks
Martin

ray.wurlod · Post by **ray.wurlod** » Mon Oct 24, 2005 3:49 pm

Is the quote character in the Sybase data? Change the Format tab of your Sequential File stage so that " is not the quote character - maybe set the quote character to 000 (no quotes).

bmadhav · Post by **bmadhav** » Mon Oct 24, 2005 6:52 pm

One of your Sybase columns u are pulling is a VarChar field and it has a carriage return in it. U may have to write a small basic routine to convert it.

Bindu Madhav

bmadhav · Post by **bmadhav** » Mon Oct 24, 2005 7:02 pm

U can build a FUNCTION like this and call it in ur transformer:

Code: Select all

i=1
   Len1=Len(Arg1)+1
   loop while(i<Len1)
   str1 = Iconv(Arg1[i,1],"MY")
   if(str1 NE '0D') Then
     Arg1[i,1] = Oconv(str1,"MY")
   End Else
     Arg1[i,1] = Oconv("20","MY")
     i=i+1
     Arg1[i,1] = Oconv("20", "MY")
   End
   i=i+1
   repeat
   Ans = Arg1[code]

[/code]

ray.wurlod · Post by **ray.wurlod** » Mon Oct 24, 2005 7:21 pm

Why are you reinventing the wheel? And why are you trying to use C constructs unnecessarily in DataStage BASIC?

Code: Select all

Convert(Char(13), " ", InLink.TheString)

does what your routine does, but much more efficiently.

If Char(13) were the initial value of a stage variable that remained unchanged, then the even more efficient expression

Code: Select all

Convert(svChar13, " ", InLink.TheString)

could be used.

It might be necessary to specify in the Format tab of the Sequential File stage that particular columns might contain line terminators.

martin · Post by **martin** » Tue Oct 25, 2005 7:25 am

Hi Ray,

I did exactly the way you explained , but all the Carriage Return are NULL.
Any Idea.

Thanks
Martin

kcbland · Post by **kcbland** » Tue Oct 25, 2005 7:30 am

For giggles, put an OCONV(inlink.col, "MCP") derivation on your problematic column to convert all non-printable characters to a ".". That will make sure your issue is not any high-character. Does that change the problem row?

martin · Post by **martin** » Tue Oct 25, 2005 7:41 am

Hi Kcbland,

Perfect.....Its working

Thanks
Martin

kcbland · Post by **kcbland** » Tue Oct 25, 2005 7:46 am

Yeah, well, you've now got a "." in your data. So your issue is high characters, maybe not necessarily a CR/LF, maybe a TAB. You're going to need to write a routine to strip all non-printables and change them to something you desire.

If you just want them removed, the CONVERT statement can serve you well. There's this post going on right now:

viewtopic.php?t=96128

that will serve you well. I posted a functions logic to pick out the high characters of your choice and replace with a "*". In your case, your replacement string should just be "" if you want the characters removed.

martin · Post by **martin** » Tue Oct 25, 2005 8:13 am

Hi Kcbland,

EREPLACE(OCONV(inlink.col, "MCP"),".",' ')

Worked in my Situation.

Thanks
Srini

kcbland · Post by **kcbland** » Tue Oct 25, 2005 8:18 am

Okay, but you do realize any existing "." will also be removed?

martin · Post by **martin** » Tue Oct 25, 2005 8:55 am

Yah...... Its Comments Field, Replacing '.' to ' ' is fine.