| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
| Parsing Text to CSV within ESQL |
« View previous topic :: View next topic » |
| Author |
Message
|
| jacquesvdm |
 Posted: Fri Feb 24, 2012 2:35 am Post subject: Parsing Text to CSV within ESQL |
 |
|
Apprentice
Joined: 18 Jan 2012
Posts: 29
|
Hi
I have a strange CSV format from a client looking like this, when parsing this as CSV the MRM parser complains about the fact that ITEM: ... do not start with appostrophy and is not valid. I then read the file in as a BLOB, removed the TAG "ITEM: " and then I want to parse the remainder of the row "field1","field2","field3" (CRLF) as CSV. The question is how can I now get the MRM to parse the remainder text. I tried to RCD, I also set the following properties
OutputRoot.Properties.MessageSet ,
OutputRoot.Properties.MessageType,
OutputRoot.Properties.MessageFormat
with no luck
header data line 1(CRLF)
header data line 2 (CRLF)
header data line 3 (CRLF)
ITEM: "field1","field2","field3" (CRLF)
ITEM: "field1","field2","field3" (CRLF)
ITEM: "field1","field2","field3" (CRLF)
ITEM: "field1","field2","field3" (CRLF)
ITEM: "field1","field2","field3" (CRLF)
ITEM: "field1","field2","field3" (CRLF)
footer data line 1(CRLF)
footer data line 1(CRLF)
footer data line 1(CRLF) |
|
| Back to top |
|
 |
| kimbert |
| Posted: Fri Feb 24, 2012 2:44 am Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
You need WMB v8 with the DFDL parser.
However...since you are probably going to say 'not possible', you need WMB v6.1 or later with the CSV quoting support. Plus a proper understanding of how to model CSV data using a message set.
Please note : you do *not* need to pre-process your data using ESQL. The MRM parser is perfectly capable of parsing this format. Hint: the 'ITEM:' is a tag for the lines that do not belong to the header/footer.
If you need more help then I am happy to provide it, as long as you
a) describe your current message model, in detail
b) list any errors that you are getting, IN FULL. That probably means taking a debug-level user trace to get the full text of the error(s). |
|
| Back to top |
|
|
| jacquesvdm |
| Posted: Fri Feb 24, 2012 2:54 am Post subject: |
|
|
Apprentice
Joined: 18 Jan 2012
Posts: 29
|
Hi
Not sure what more do you need in terms of the message model, I unfortunely cannot paste the actual file here but will email to you if you will risk giving me your email on this forum. I use the standard CSV message set.
Here is the error message
( broker.default ) The quote character ('"') at byte offset '7' is invalid.
A quote character is not allowed at this position in the message.
If quoting is to be used, a quote character must occur at the start and end of a variable length field.
Ensure the message being processed conforms to the message definition.
Check the TDS physical format property 'quote character' is specified correctly.
If you want to include a quote character within a quoted field, include
it twice, consecutively, in the bit stream. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Fri Feb 24, 2012 3:03 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
So you should have identified ITEM: as a tag in a Tagged format.
Then you wouldn't have needed to parse as a BLOB and manually remove.
Or you could have set it as a group indicator.
What kimbert would like to see regarding your message model is either a quote of the xsd content, in code tags, or a very detailed structural description that describes what the relevant groups and elements in your model are and what MRM-specific properties they have.
And, again, a User Trace would be helpful. |
|
| Back to top |
|
|
| jacquesvdm |
| Posted: Fri Feb 24, 2012 3:27 am Post subject: |
|
|
Apprentice
Joined: 18 Jan 2012
Posts: 29
|
Hi
I tried both adding tag and using group indicator to no avail.
How would you like me to send the trace files.
thanks |
|
| Back to top |
|
|
| mqjeff |
| Posted: Fri Feb 24, 2012 3:58 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
mqsiformattrace produces text contents.
Find the relevant section, and post it here, in [ c o d e ] tags for readability.
Or just open a PMR and send the traces to them. |
|
| Back to top |
|
|
| kimbert |
| Posted: Fri Feb 24, 2012 4:34 am Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
| Quote: |
| I use the standard CSV message set. |
There is no such thing. Where did you get this message set? From the samples gallery? |
|
| Back to top |
|
|
| jacquesvdm |
| Posted: Mon Feb 27, 2012 8:57 am Post subject: |
|
|
Apprentice
Joined: 18 Jan 2012
Posts: 29
|
Ok, so I finally got the TAGging to work looking at the CSV sample 5. Thanks for all your help so far.
to summarise what I did
I added a group and configured the group to be tag delimited. I then add a field in that group and configure the tag attribute to correspond to my tag in the CSV record. In my case it was "Event: ". Now the MRM parser parse the rest of the message correctly for one instance.
My next challenge is that the message format differ depending on a field within the structure. I saw the CSV sample 5 use the choice group to achieve this but the example is easy as the choice string is the first 3 characters in the message, mine is not. it is the 5th field. I defined a structure like this
field1
field2
field3
field4
choice, use tag delimited
- key field , tag value = 3
- rest of the field mapping for type 3
- keyfield2, tag value = 4
-rest of field mapping for type 4
The result is it finds no matches within my message, I know it is there.
I also cant make much sense from the user trace.
Anyone who have done this before. Help appreciated. |
|
| Back to top |
|
|
| kimbert |
| Posted: Mon Feb 27, 2012 4:20 pm Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
| Quote: |
| Ok, so I finally got the TAGging to work looking at the CSV sample 5. |
Good news!
| Quote: |
| I saw the CSV sample 5 use the choice group to achieve this but the example is easy as the choice string is the first 3 characters in the message, mine is not. it is the 5th field. |
You're right - this is not a simple format. It is also not the format that you described in your first post. No problem with that - you were asking about how to use tags.
For this new problem, please post a new example message. Include all relevant details and take time to make your description readable. Code tags help with that... |
|
| Back to top |
|
|
| jacquesvdm |
| Posted: Mon Feb 27, 2012 8:39 pm Post subject: |
|
|
Apprentice
Joined: 18 Jan 2012
Posts: 29
|
Hopefully I have removed all identification information from the file.
This is a sample received from the vendor
| Code: |
File: dispatch_event_file
File Creation DTM: 20041201 15001400
Version: 1
Event: "ACC-10001","37555",,,"6",,"20041201 03354800","20041130
22385100","1.06",,,,,"Mobile Subscriber","WBM","ROMMO","Charge-based Default",,"Default","0","27829129","T22",,,,"DEUD20595800001756","20
04/11/30-23-38-51.00","DEUD2","Roaming MO SMS","27111111111","4112120",,,,,,,"27111111111",,,,,,,,,,,,,,,,,,,,,,,,
Event: "ACC-10001","501080",,,"3",,"20060228 16361200","20060228
10563000","0.04",,,,,"Mobile Subscriber","MES","GP0","640",,"Peak",
"389","18985","ips-vlive","19375",,,"26424825_655.01.196.46.162.102",
,,,,,"27222222222","00:00:20","26113927","2","C2",,"27829822015",,,,"0",
,,,"0",,,,,,,,,,,,,,,,
Event: "ACC-10001","501080",,,"3",,"20060228 16361200","20060228 10435200",
"0.20",,,,,"Mobile Subscriber","MES","GP0","600",,"Peak",
"19587","98963","internet","118550",,,"542782873_655.01.196.6.254.20",
,"3519890081125128",,,,"27222222222","00:37:28","26113927",,"internet",
,"27828656784",,,,"0",,,,"0",,,,,,,,,,,,,,,,
Event: "ACC-10001","500787",,,"4",,"20060228 17330900","20060218 19554100",
"0.00",,,,,"Mobile Subscriber","5BS","5BS","820","5","Weekend",
"143","27333333333",,,,,"0220000533969920051123135541027444444444",
"27333333333","T11",,,"5001081102955700","27444444444",,"22617482",
"01","0",,"27444444444",,,,"1","1.88",,,"0","49",,,,,,,,,,,,,,,"8200"
Event: "ACC-10001","37555",,,"7",,"20041201 03352100","20041130 17214600",
"4.78",,,,,"Mobile Subscriber","5BS","5BSRMT","H2",,"Default","68",
"49","T11",,,,"DEUD20595800000482","2004/11/30-18-21-46.00",
"DEUD2","Roaming MT Voice","27111122222",
"7090097",,,,,,,"27111122222",,,,,,,,,,,,,,,,,,,,,,,,
Event: "ACC-10001","500787",,,"5",,"20060228 17330900","20060218 19483100",
"0.00",,,,,"Mobile Subscriber","WBM","V01","821","4","Weekend",
,"0799942220",,,,,"0220000530601520051123134831027111133333","27555555555",
"T22",,,,"27111133333","27829129","8497426","1F",,,"27111133333",,,,
"1","0.30",,,"0",,,,,,,,,,,,,,,,"8200"
Event: "ACC-10001","37555",,,"15",,"20041201 03352300","20041130 17443400",
"6.25",,,,,"Mobile Subscriber","5BS","ROMGPR","Charge-based Default",
,"Default","1733","26740","mms.vodacom.net","28473",,,"DEUD20595800000568",
"2004/11/30-18-44-34.00","DEUD2","Roaming GPRS IP Data Total",
"10.15.251.245","1083831302",,"01:00:00","27666666666","638565",,,"27666666666",
,,,,,,,,,,,,,,,,,,,,,,,
Event: "ACC-10001","2717128",,,"34",,"20080818 10585500",
"20080730 15371100","25.44",,,,,"Mobile Subscriber",
"WBM","AIR",,,,"300",,"Airtime Transfer",,,"Vodacom",
"11260I88TAO73F",,,,,,"27777777777",,"000010008066717",,,"27796055718",
"27777777777",,,,,,,,,,,,,,,,,,,,,,,,
Event: "ACC-10005","1097371",,,"37",,"20080818 18060500","20080815 23025800","0.18",,,,,"Mobile Subscriber",
"WBS","USSDB",,,,"203",,"MyServices",,,"USSD","0016150599086594",,,,
,,"27888888888","19","000050046973200",,,,"27888888888",,,,,,,,,,,,,,,,,
,,,,,,,
Event: "ACC-10001","2717136",,,"36",,"20080818 16404900",
"20080815 23340500","1.28",,,,,"Mobile Subscriber",
"WBS","USSDC",,,,"200",,"MyServices",,,"Smartphone1","0016140275587597",
,,,,,"27999999999","1","000010015854023",,,,"27999999999",,,,,,,,,,,,,,,,
,,,,,,,,
Event: "ACC-10006","1161816",,,"29",,"20080818 11454300",
"20080801 18310100","0.70",,,,,"Mobile Subscriber",
"2BM","MMS","720",,"Default","202","1",,"MM1","1","MMS","1870000028774072",
,,,"mms.vodacom4me.co.za",,"27111156789","30598","000060003740348",
"820027721830948",,,"27111156789",,,,,,,,,,,,,,,,,,,,,,,,"8200"
Footer: dispatch_event_file
AuditValue_1: 11
AuditValue_2: 39.93
End: dispatch_event_file
End_of_file: |
You will notice the 5th field is a number, on the first record it is a 6. This will dictate the format of the rest of the record. |
|
| Back to top |
|
|
| kimbert |
| Posted: Tue Feb 28, 2012 2:08 am Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
Thanks for the example message - that makes it fairly clear. But I still need quite a lot more info.
- Are the mid-field line breaks really there in the data? ( I assume 'yes' )
- In your message definition file, have you adjusted the Delimiter so take account of the double line break between records?
| Quote: |
| The result is it finds no matches within my message, I know it is there. |
That is a fairly meaningless statement. Please explain in more detail.
| Quote: |
| I also cant make much sense from the user trace. |
Well, I don't think I can suggest any better approach. How about posting the relevant section of the user trace?
I'm expecting answers to all of those questions, btw. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Tue Feb 28, 2012 6:58 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
So what you probably need to do is use the 5th field as another tag.
So you would construct an outer CSV record as having only five fields. The last field would be an inner CSV field choice structure that uses the first field as a tag or group identifier. This would then be used to resolve the choice. |
|
| Back to top |
|
|
| jacquesvdm |
| Posted: Tue Feb 28, 2012 8:28 am Post subject: |
|
|
Apprentice
Joined: 18 Jan 2012
Posts: 29
|
Hi MQJeff
I did just that what you proposed but for some or other reason the parsing stop just before the choice field. I will give it another try tomorrow as I am already at home.
Kimbert, there should not be midfield line breaks and could only have been as result of pasting the actual message from notepad.
I will also post the user traceextract to you tomorrow morning.
Thanks for the interest and help so far. At least I am now convinced that this can be done and only need to find the sweetspot in this configuration.  |
|
| Back to top |
|
|
| kimbert |
| Posted: Tue Feb 28, 2012 12:23 pm Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
Please post the exact message, and this time check that it is displayed correctly on the forum. To put it another way, please take up a little more of your own time to save our time.
Before you post that trace extract, please read it line by line and see if you can make some sense of it. Then explain ( clearly ) what it is that you do not understand. Otherwise you are just asking for a solution on a plate. |
|
| Back to top |
|
|
| jacquesvdm |
| Posted: Tue Feb 28, 2012 10:14 pm Post subject: |
|
|
Apprentice
Joined: 18 Jan 2012
Posts: 29
|
Hi all who actualy assisted
I found the solution to my problem
you have to use a choice group with tag delimeted, Delimeter <CR><LF> and tag seperator = , (comma).
Then define new elements underneeth this group with tags identify the group. This is exactly the same as the CSV example 5 provided with the WMB toolkit. The problem with my data was that it had quoted fields. I used the unquoted values in the tags. After changing the tags to the quoted equivalent the match was successfull.  |
|
| Back to top |
|
|
|
|
|
  |
Goto page 1, 2 Next |
Page 1 of 2 |
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
|
|
