Originally posted by: vijji_WTX

Hi All,

Could you pls sugget how to handle below CSV file structure.

E.g |"test1"|1234|"test2"|3333|44444|5555|"test3"|

Here delimitor is | ( pipe )
for text value there will be " " double quotes and for numeric value there will NO double quotes.
I handled this in TT by defining quotes in item level. Now i have to handle a text item with more than one quotes like.. |"test1"test2"|. Generally Map will fali due to wrong format.

But here we have to consider this as valid and value is test1test2.

Could you please suggest how can i handle this.

Thnaks in advance !!!

Regards
Vijji
#DataExchange
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender

Originally posted by: repanzer

CSV is a comma delimited file (comma-seperated-values), right? You are not delimiting using a comma, so this a just a pipe delimited file. (if I am wrong on the terminolgy, PLEASE correct me, but wikipedia says it's only comma delimited, and from what I hear, any thing I read on the internet is true).

When you take a spreadsheet and "save as" csv, it will put quotes around the text data just in case the value is "02", and you want to keep the leading zero, because maybe it's a reference number and not a dollar amount or something.

How you got this .. |"test1"test2"|, I do not know.
The thing is; you using WTX do not care about the quotes. If I were you, I would just ignore them, delimit my file on the pipe, then when I go and map the data to the output, do;

=SUBSTITUTE(Field:csvfile, '"', "")

which will remove the quote and map the data.

I don't know what this is;

.. |"test1"test2"|.
#DataExchange
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender

Originally posted by: vijji_WTX

Hi Repanzer,

First of all Thanks a lot for your time and question. I am very happy about your comments.
I am sorry i have put wrong subject. Here my requirement is handling this ...|"test1"test2"|. i should get test1test2 as value.I have nearly 20 fields in my TT and dont know in which filed i occur this problem.What i am doing is...i have put delimter as | pipe (record level) and in TT level i added initiator as " and terminator as ". Let me explian this with example..

1234

"test1"

34556

"test2"

"test3"

4555

6666

77

777

here for first field i do not mainitain initiator and termintor becoz i know it is Numeric data.
in second field i know it is String and it will come with quotes. So i put initiator and terminator as Quote in TT field level. I do not know it is bad practice or not. I hope you undetstood up to this.

if input record consists like this(4th field) Map/TT will fail due to invalid Format

1234

"test1"

34556

"test2"check"

"test3"

Now i have to ignore this error and pick up only test2check as value for 4th field.
I hope you are able catch my doubt/question.

Thanks in advance !!

regards
vijji
#IBMSterlingTransformationExtender
#DataExchange
#IBM-Websphere-Transformation-Extender

Originally posted by: repanzer

"I hope you undetstood up to this."

I do understand, and using the initiator and terminator of " is exactly what I would have suggested you do, but as you mentioned, this data will fail; "test2"check".

That is why I would suggest removing the terminator and initiator, then using the SUBSTITUTE in the map rule, to remove all the quotes when you map. Is that not possible?

If it is possible, it means that regardless of quotes in the data, every pipe means a new field. Let's just say your output is comma delimilted, then using the SUBSTITUTE.. ...

This;

1234 "test1" 34556 "test2"check" "test3"

Would map as;
1234,test1,34556,test2check,test3

And from what I gather, that is what you want, right?

There is no way that you can say the " is an initiator and terminator in most cases ("test1"), but sometimes it's part of the data ("test2"check"), in the same field.

The only other thing I can think of is going to the people who create this data and tell them that this "test2"check", is unacceptable.
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender
#DataExchange

Originally posted by: vijji_WTX

Thanks a lot repanzer. I completely agree with you reg. you suggestions. Problem here is ....We always say do not send INVALID files from Source...bt always they send and ask for feedback :-). What i feel is it would be better if i log an error record in error table..when ever i receive a invalid file.
Also planning to use Restart attribute in TT. Need to check this.

What's your suggestion.

regards
Vijji
#DataExchange
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender

Originally posted by: TarunB

I think Rep's covered this pretty well, under the assumption that your 4th field data is expected to sometimes contain a double-quote. This is the requirement that you are trying to cover - or so I thought.

However, now you imply that this data is in fact invalid and needs to be reported back to source data teams. So it appears that you are not clear on the requirements.

Using a REJECT/RESTART mechanism will allow you to filter out invalid records from the file, which you can then report back to the source data team. You can even email these records to the team automatically, or create a report and highlight the error. In fact there is an awful lot you can do with TX, but it would help to have some idea of what you're actually trying to achieve.
#IBM-Websphere-Transformation-Extender
#DataExchange
#IBMSterlingTransformationExtender

Originally posted by: vijji_WTX

Hi Tarun,

nice to see your valuable feedback. i tried using Restart attribute in Record level and what i am getting not as expected. I need your suggestion here.

suppose i have 5 records where 2 and 4 are invalid. My map is ending at 2nd record by saying error occured and ignores rest of the records. I want to create log file like 1 valid , 2 INVALID ,3 VALID etc...

Can you guide me how to do this .i guess you have good idea about this. it would be better if you provide an example.

Thanks in advance !!!
#DataExchange
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender

Originally posted by: TarunB

Are you using REJECT along with the Restart attribute?

Assuming you are using 8.x, read the help document 1005.pdf ('Map Designer'), page 149. You'll learn more if you do it yourself.

If you're still stuck by tomorrow post your map/trees/data and we'll get round to helping you.
#IBM-Websphere-Transformation-Extender
#DataExchange
#IBMSterlingTransformationExtender

Originally posted by: repanzer

Turan, while you're here, and in reference to Restart/Reject....
I'm using data break by value; when the value of the invoice number field changes, it's a new invoice.

I got it to work great, but when attempting to add error logic using restart/reject, the output is bad and I think I know why, but not how to handle it.

In the event I'm reading the...4th record of an invoice where the number is the same as the previous three record, but there's an error in the 4th record and it doesn't validate as a record (not found of type), it doesn't "close" the invoice correctly, because it never got to the record where the value changed and closed it. If I do =count(invoices) and there are 3 invoices with valid multiple records, meeting the type definition (invoice>records 1:s) , even though there are valid records for each invoice, the map can't "see it" because of the one error record.

I'm still hitting this issue hard, but if you happen to have any quick ideas, I could probably follow through with any suggestions. If something works ou, I'll put the info in a new thread to maintain the knowledge base here. Thanks!
#IBM-Websphere-Transformation-Extender
#DataExchange
#IBMSterlingTransformationExtender

Originally posted by: TarunB

Rep - Have you tried changing the Map Settings (--> Warnings --> Custom) to ignore this error? Would that help in this situation?
#IBMSterlingTransformationExtender
#DataExchange
#IBM-Websphere-Transformation-Extender

Originally posted by: vijji_WTX

Hi repanzer , hi Tarun,

I too tried of using restart by refereing 1005.pdf (149 page). In documentation they give it will will, bt when i use in my Map... Map is ending at Invalid record. Not getting the output like below.

1-valid
2-valid
3-invalid
4-invalid
5-valid
etc..

Request you to post here..if you find anything reg. this.
#DataExchange
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender

Originally posted by: TarunB

Try adding an extra (somewhat apparently superfluous) group and put the Restart there, something like

File
GroupSet1 (s) <-- Restart here
GroupSet2 (s) <-- Control break rule here
Record (1:1)
Sorry no time have to run but will check back later.
#DataExchange
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender

Originally posted by: DianeC

rep - try using a seperate output card to handle the rejected data and do that before processing the good data.
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender
#DataExchange

Originally posted by: TarunB

Rep

Did you make any progress on this issue?

I set up a similar test to replicate the problem and I've hit upon something that looks like a bug.

My input data is :-

a
a
aaaaaa
b
b
c
c
c

where aaaaaa is the invalid field. The expected output is :-

a
a
b
b
c
c
c

but the actual output created is :-

a
a
c
c
c
In fact, the REJECT file correctly contains only 'aaaaaa' correctly, so it would appear that the 'b' rows have been lost. Running the same test with only valid data yields the correct result.

Very odd :-/
#DataExchange
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender

Originally posted by: TarunB

Fixed it.

File
GroupSet1 (s)
GroupSet2 (s) <-- Control break rule here and Restart here
Record (1:1)
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender
#DataExchange

Originally posted by: vijji_WTX

Hi Tarun,

Thanks for posting here with your test details. It means we should have 2 group level objects in TT and one Record. My map getting stopped at Invalid record even after disable/enbale some confiditions in Warnings section.

If possible could u please send me the TT and other Map settings. I am still hitting error message.

Thanks in advance !!!
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender
#DataExchange

Originally posted by: TarunB

Sample attached. I've kept it bare so that it's easy to understand.
#IBM-Websphere-Transformation-Extender
#DataExchange
#IBMSterlingTransformationExtender

Originally posted by: vijji_WTX

Thanks a ton !!! Tarun for your attachment. Unfortunatly i am using WTX 8.1 and unable to open both Map and TT. Is there any way i can see these files using WTX 8.1.

Please suggest !!
#DataExchange
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender