i am trying to parse csv data with multiple records(3 records). data looks something like this -
77,878,success
f82,f92,f64,f19
83,bcd,77,98
the max repeat for first two lines is 1, and for third line is unlimited.
this is what i have tried till now -
Record parser = newline
field = ,
record identifier = Nth Field - 0
i am using nth field options - field1 starts at 0, field2 starts at 1…
I am getting this error - [FFP.0012.0004] Found no valid records
i looked into some of previous posts, and tried to use ‘recordWithNoID’, but how will this work with multiple records?
I think, i got answer for this one. Here’s an explanation that might help someone.
The records that I have are csv data and do not have a record identifier. In absence of record identifier, only way to parse data is through default record option in the right hand side settings for schema. When we use this default record option, only one record can be selected from dictionary. So there is no way to parse csv data with multiple records.
I have decided to separate the first two lines from incoming data and then parse the remaining data.
I converted the incoming stream of data to string. Then using index and substring services in wm:public and line seperator services in PSUtilities, i seperated the first 2 lines from remaining data.
pls let us know yr data format and schema error, in order to understand the problem.
Thanks for the response. My issue was posted yesterday, but noone has responded to it as of yet. Here is what I wrote. I’m curious if the issue you experienced and the problem I’m facing is similar. Here’s what I wrote:
Hi Everyone. I cannot figure out how to do this and how this works. See the below data (very simplified, but I believe mirrors my issue):
|JOE|ROBERTS|3 <== Header (1 only), the last field “3” represents how many line items will follow
|4555|1.50|CHAINS|4556|1.55|NUTS|4557|1.60|BOLTS <== represents the 3 line items (could have 3 details, could have 200, it is unlimited)
|3.65|END| <=== Footer (1 only)
I cannot figure out how to properly set this up. I think I’m having the most issues trying to figure out how to handle the line item situation. How do you set up the schema so that in that one continuous pipe delimited line, the parser will know that there are 3 line items in it and where each detail line starts and stops.
I’ve set up a flat file dictionary whereas there are 3 different sections: one for the Header, one for the Detail, and one for the footer.
The schema was set up so that the detail line’s max repeat is unlimited. In Flat File definition → delimiter was selected; record is setup as newline and subfield is setup as |. Yet I continue to get “No Valid Record” errors.
The issue you are facing is very similar to what i had. since there is no record identifier in the records, this is treated as special case. have a look at paragraph below from schema developers guide -
"Note: If your flat file does not contain record identifiers, you must select a default record. By selecting a default record, a CSV (comma separated values) file can be parsed as a special case of record with no record identifier, but with fixed field and record delimiters" coming back to yr problem, you need to seperate Header and Footer lines from rest of data. my suggestion is to convert all data to string first and then use index service and substring service available in wm:public to seperate data. Create schema using the ‘set’ option in default record properties (in right hand side) and then select only one record(middle one). in yr convertToValues service pass input string data for middle record only. if you need to validate Header and Footer records, then you might have to create seperate schema’s and perform same thing again. Hope this helps. let me know, if you need more info. Merry Christmas!
Thanks again for the response. Okay, I sort of understand what you’re saying, but I can’t figure out what you’re indexing on in order to separate the header and footer from the detail. I wish I had something identifying for the detail record and if so, I would like do an index up until I see a certain word, but I can’t do that. I know that after the 4th pipe delimiter, the detail starts up, but I don’t know how to do an index up to that.
Is it possible that you could tell me what you’re doing in the code to separate this information out, as I have never done this before? I see that I also do not have PSUtilities. What’s the name of the service you’re using from PSUtilities? Can I get this accomplished without it or will I need to definitely download it from webMethods?
i can suggest you one way of getting data out of this file.
first convert yr data in string. Once everything is in string, create a loop in yr flow service. inside this loop use wm:indexOf service to get the index of first occurence of ‘|’ and the second occurence of ‘|’ and get the substring out of the original string using ‘wm:substring’ service. once you get this data use ‘wm:replace’ service to remove this data from original service. loop over this service 3 times to get and remove data from 1st line. in this way 1st line is done.
for 3rd line use service ‘lastindexof’ instead of indexof and perform same steps again. i can send you code for this service if u want.
once 1st and 3rd lines are removed, perform same steps with unlimited loop over remaining data to map the data in a document list. the document list shall have 3 fields.
Thank you for offering to send over the code and if you don’t mind, it would be great if you were to send it over, so that I can make sure I’m doing everything right. I plan on working on this tonight and I have to get this done before Friday.
I really appreciate the assistance that you are providing.
hi,
It is not advisable to manipulate the incoming data when there are built in services to do it… The assumed flatfile structure data is wrong… When there is error, it is difficult to identify where the problem is from- if the problem exists with Actual data or somewhere else…
Consider, the line items missed some 3 fields randomly in a total of 12 fields accross the file…
Expected: 4 records with 3 fields each. Since data is wrong, it should be errored
But whats happening is…
Actual: 3 records with 3 fields each and processing success
i totally agree with you Senthil. In usual cases, it is not advisable to manipulate incoming data. this is an unusual case because there are no record identifiers in the data. so inorder to get data out of incoming bytes/stream, we have to seperate header/footer from remaining data. pls feel free to mention other inbuilt service or other methods that could help our scenario.
Also i agree with you that the record structure for the 2nd line doesnt seem to be correct. that is what makes this case so more different. I based my suggestions based on given test data.
The test case you mentioned should definately be taken care of inside the flow code. this can be done by simple comparison of number of expected records mentioned in Header to actual records in document list produced by 2nd record. there can be other validations as well, for example, Kendrix can add validations using ‘isNumeric’ service to check his numeric fields and so on.
I had similar issue in my privies project. I think, we cont do any thing by using flat file package. I wrote one jave service. That service will converts one header and multiple detail recs based on the header field. Pls let me know if you want that jave code
I’m also facing the same issue. I’ve a different structure though.
Header
–Batch Header
------Document Header x
----------Document Detail x
----------Document Detail x
…
…
------Document Header x
----------Document Detail x
----------Document Detail x
…
…
…
…
–Batch Header
------Document Header x
----------Document Detail x
----------Document Detail x
…
…
------Document Header x
----------Document Detail x
----------Document Detail x
…
…
…
…
…
Trailer
Here, Except Header & Trailer, every other record appears multiple times in the file. Is there any other work around or do i need to use 3 separate schemas for each multiple record?
Any help is appreciated.