| Author |
Message
|
| newbie09 |
 Posted: Tue Aug 11, 2009 3:11 am Post subject: Using CSV Messaging Standard in MB v6.1 |
 |
|
Newbie
Joined: 11 Aug 2009
Posts: 5
|
Hi All,
I am just starting to learn how to use Message Broker V6.1.0.3 on WMQ V7. Im using "CSV - Comma Separated Values" Message Standards available in MB v6.1.
I have to read in and also output a CSV formatted MQ message like the sample below:
"Hello","20 MAY 2010","124.56","Remarks, here","More ""remarks"" here"
When i try to use the CSV message standard provided by MB v6.1, I found that MB does not always output Quote characters for each field.
Sample output by MB:
Hello,20 MAY 2010,124.56,"Remarks, here","More ""remarks"" here"
How can I modify the default setting of the CSV message standard so that it will always output a field value with an opening and closing Quote character " ?
Hope u guys will enlighten me. Thanks in advance!!! 
Best Regards |
|
| Back to top |
|
 |
| Vitor |
| Posted: Tue Aug 11, 2009 6:39 am Post subject: Re: Using CSV Messaging Standard in MB v6.1 |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| newbie09 wrote: |
| How can I modify the default setting of the CSV message standard so that it will always output a field value with an opening and closing Quote character " ? |
One question - why? A CSV file doesn't need to have the values held in quotes and typically doesn't. Save an Excel spreadsheet as a CSV and you'll see what I mean.
From this, it's far better IMHO to leave well enough alone.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| newbie09 |
| Posted: Wed Aug 12, 2009 9:05 am Post subject: Re: Using CSV Messaging Standard in MB v6.1 |
|
|
Newbie
Joined: 11 Aug 2009
Posts: 5
|
Hi Vitor,
Thanks for your reply.
The reason is that I am suppose to transform CVS format provided by some legacy application to XML format and also to transform from XML back to the required CSV format So I have to model the CSV message with quote character for all the fields so that the legacy applications do not need any modification. 
Best Regards |
|
| Back to top |
|
|
| kimbert |
| Posted: Wed Aug 12, 2009 12:49 pm Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
You should be able to get something close to the correct result by
- setting the Group Indicator to " ( quote character )
- setting the Delimiter to "," ( quote comma quote )
- setting the Group terminator to " ( quote character )
You may have to change the messaging standard to 'User Defined Text' instead of 'CSV' to avoid two sets of quote characters on the fields which are already being quoted.
You will also need to escape any quotes which are part of the field data. It should be possible to do that by setting 'Escape character' to " and including " in the reserved characters list. |
|
| Back to top |
|
|
| newbie09 |
| Posted: Thu Aug 13, 2009 10:03 am Post subject: Re: Using CSV Messaging Standard in MB v6.1 |
|
|
Newbie
Joined: 11 Aug 2009
Posts: 5
|
Hi kimbert,
I have created a message set based on your suggested setting:
TDS Message Standard: User Defined Text
Group Indicator: "
Group Terminator: "
Delimiter: ","
Suppress Absent Element Delimiter: Never
Escape char: "
Reserved char: "
I created a message structure as follows:
CSVMsg (CSVMsg_Type)
DES= All Element Delimited GI=" GT=" Delimiter=","
Field1 (string)
Field2 (string)
Field3 (string)
Field4 (string)
Field5 (string)
I created a message flow that takes in the msg set and output the same msg set
ESQL Code in Compute Node
Call CopyMessageHeaders();
Set OutputRoot.MRM.Field1 VALUE = InputBody.Field1;
Set OutputRoot.MRM.Field2 VALUE = InputBody.Field2;
Set OutputRoot.MRM.Field3 VALUE = InputBody.Field3;
Set OutputRoot.MRM.Field4 VALUE = InputBody.Field4;
Set OutputRoot.MRM.Field5 VALUE = InputBody.Field5;
When I put a test msg
"Hello1","Hello2","Hello3","Hello4","Hello5"
I get the following output instead
"Hello1,Hello2,Hello3,Hello4,Hello5","","","",""
Looks like the input msg has been move into Field1..??
Is my msgset set wrongly?
Best Regards |
|
| Back to top |
|
|
| kimbert |
| Posted: Thu Aug 13, 2009 11:01 am Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
I didn't claim that you could *parse* a CSV messge using that message definition. I suggest that you parse an XML document to create the message tree ( use XMLNSC for that, not MRM ).
For parsing, your original message set which uses the CSV messaging standard will work.
btw, you don't need to copy the fields one by one. If you take my advice and use XMLNSC to parse the XML input, you can just do this:
| Code: |
| SET OutputRoot.MRM = InputRoot.XMLNSC.CSVMsg; |
|
|
| Back to top |
|
|
| newbie09 |
| Posted: Sun Aug 16, 2009 10:34 am Post subject: Re: Using CSV Messaging Standard in MB v6.1 |
|
|
Newbie
Joined: 11 Aug 2009
Posts: 5
|
Hi Kimbert,
I guess I have misunderstood your first posting. I thought that the setting can be used for parsing input csv msg as well as outputing csv msg.
So to parse input csv msg, I can still use the built-in CSV message standard provide by MB. The built-in CSV is able to parse csv msg with quote character for each field.
To output cv msg, I have to use the setting that you have specified so that quote character will be outputted for each field.
Is my understanding correct? 
Best Regards |
|
| Back to top |
|
|
| kimbert |
| Posted: Sun Aug 16, 2009 3:05 pm Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
| Yes, your understanding is correct. |
|
| Back to top |
|
|
| neeha_kashyap120 |
| Posted: Mon Aug 17, 2009 12:20 pm Post subject: |
|
|
Apprentice
Joined: 05 Mar 2009
Posts: 31
|
Hi newbie09
The transformation you are trying to do is given in the Samples gallery for Message broker, the CSV_6 is what you want to look at. It give CSV to XML but once you get that vice versa is just the reverse.
You can import the sample to your toolkit for better understanding
Neeha
_________________
Neeharika Kashyap
IBM Certified System Administrator - WebSphere MQ V6.0
Trying to be IBM Certified System Administrator - WebSphere Message Broker V6.1 and IBM Certified Solution Developer - WebSphere Message Broker V6.1 |
|
| Back to top |
|
|
| newbie09 |
| Posted: Tue Aug 18, 2009 10:16 am Post subject: Re: Using CSV Messaging Standard in MB v6.1 |
|
|
Newbie
Joined: 11 Aug 2009
Posts: 5
|
Hi Kimbert,
Using your suggestion, I manage to produce a working model for parsing input CSV msg with quote character for every fields and output CSV msg with quote character.
Input CSV Msg Set
Message Set Domain: MRM
TDS Msg Standard: CSV
As I need all delimiter to be present even for absent element, I change the setting for following option:-
Suppress Absent Element Delimiters: Never
Output CSV Msg Set
Message Set Domain: MRM
TDS Msg Standard: User Defined Text
Group Indicator: "
Group Terminator: "
Delimiter: ","
Suppress Absent Element Delimiters: Never
Escape Character: "
Reserved Character: "
XML Msg Set (Input/Output)
Message Set Domain: XMLNSC
Then I created 2 simple msg flow as follows:-
msgflow1: Input CSV msg --> XML Msg
msgflow2: XML Msg --> Output CSV Msg
I tested using the following Input test msg:-
"","Hel,lo2","Hello3","Hel"",""lo4","He""ll""o5"
Using the output xml as input test msg for msgflow2, the output csv msg produced by msgflow2 is the same as the above Input test msg.
Thanks alot for your help, Kimbert. 
Hi neeha_kashyap120
Thanks for replying my post. kimbert has already provide me with a solution.
Best Regards |
|
| Back to top |
|
|
|
|
