| Author |
Message
|
| MQ_user1 |
 Posted: Wed Jul 23, 2014 6:05 am Post subject: Date format conversion from Input CSV file and output to csv |
 |
|
Novice
Joined: 09 Jun 2010
Posts: 19
|
Hi
I need help on converting "DateTime" field in a csv file to different Date format as one field and Time format in another field to a csv file.
Input CSV file contains:
xxx,123,456,23-07-2014 15.00.00
Output CSV file format should be:
xxx,123,456,20140723,1500
I have prepared 2 XSD Message model files and validated taking 4th column as String.
Note: Input CSV files reside on remote server and output csv files should be placed on another remote server. I was able to pick the files and place in remote server but missing the logic here.
Now, I'm struck on how to go forward......whether to write an ESQL or use Message Map.
I'm new to Message Broker development.
Thanks
ARaj |
|
| Back to top |
|
 |
| mqjeff |
| Posted: Wed Jul 23, 2014 6:30 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
it doesn't matter how you map between the fields, as long as the model indicates that they are timestamps and each model has the appopriate format information.
This kind of formatting is "physical" information. inside your message flow, you only ever deal with "logical" information. |
|
| Back to top |
|
|
| MQ_user1 |
| Posted: Wed Jul 23, 2014 6:37 am Post subject: |
|
|
Novice
Joined: 09 Jun 2010
Posts: 19
|
Hi Mqjeff,
I didn't understand what you said.
In both Message models I have used it as String....If I use substring function to map to 4th and 5th column on output csv.....then how can Field4 which is actually Date in Text format can be converted to different format?
Here, we have 2 things:
1. To Split Column4 to 2 columns
2. Convert 1st part of Column4 to different Date format.
Please tell me in detail.
Thanks
ARaj |
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed Jul 23, 2014 6:42 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
No, you should model the CSV files using the DFDL csv templates.
Then you don't worry about "columns", you worry about logical fields.
Then model the input and output fields as datetime, and then you set the first output field to output only the date and the second output field to output only the time. |
|
| Back to top |
|
|
| MQ_user1 |
| Posted: Wed Jul 23, 2014 7:53 am Post subject: |
|
|
Novice
Joined: 09 Jun 2010
Posts: 19
|
I'm struggling to Model Input CSV where I can able to validate when column4 is defined as String but when I set it as DateTime then I have below errors.
23 Jul 2014 17:52:21 info: Offset: 15. Found delimited value: '23-07-2014 15.00.00' for element 'field4'. The delimiter was '%CR;%LF;%WSP*;'.
[dfdl = /MessageModellingLibrary/Kasse.xsd, scd = #xscd(/schemaElement::Kasse/type::0/model::sequence/schemaElement::record/type::0/model::sequence/schemaElement::field4), 123]
23 Jul 2014 17:52:21 error: CTDP3029E: Invalid calendar value '23-07-2014 15.00.00' for element 'field4'. Parsing failed at offset 13.
23 Jul 2014 17:52:21 fatal: CTDP3029E: Invalid calendar value '23-07-2014 15.00.00' for element 'field4'. Parsing failed at offset 13.
Thanks
ARaj |
|
| Back to top |
|
|
| Vitor |
| Posted: Wed Jul 23, 2014 8:00 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| MQ_user1 wrote: |
| I'm struggling to Model Input CSV where I can able to validate when column4 is defined as String but when I set it as DateTime then I have below errors. |
Is that string an out of the box date time format?
Is there anything you could do to provide DFDL with the information it needs to interpret that as a date time?
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| MQ_user1 |
| Posted: Wed Jul 23, 2014 8:04 am Post subject: |
|
|
Novice
Joined: 09 Jun 2010
Posts: 19
|
Yes Vitor,
Its a String from Input CSV file as below
Input CSV file contains:
xxx,123,456,23-07-2014 15.00.00
Output CSV file format should be:
xxx,123,456,20140723,1500
In Input CSV message model I'm defining 4th Column as "dateTime".
Thanks
ARaj |
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed Jul 23, 2014 8:15 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
So it found the value, and it extracted the piece it needed.
It then tried to parse it using the physical format information contained in the model.
This information *did not* match the field. That is, it tried to parse "23-07-2014 15.00.00" using the wrong format.
So alter the format of the datetime. In the Model. |
|
| Back to top |
|
|
| Vitor |
| Posted: Wed Jul 23, 2014 9:20 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| MQ_user1 wrote: |
| Its a String from Input CSV file as below |
Yes, I understand that.
| MQ_user1 wrote: |
| In Input CSV message model I'm defining 4th Column as "dateTime". |
My point is:
Does that string format match what the DFDL parser expects when you say "dateTime"? Or is it just a string that, to the human eye, is a date time but to the parser might as well be the opening chapter of Romeo & Juliet? Such that you might have to explain to the DFDL parser that it is, in fact a dateTime and what makes it so?
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| kimbert |
| Posted: Wed Jul 23, 2014 2:39 pm Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
Open the DFDL xsd in the DFDL editor.
Select the dateTime element
In the DFDL properties, find the 'Calendar Pattern' property and alter it.
_________________
Before you criticize someone, walk a mile in their shoes. That way you're a mile away, and you have their shoes too. |
|
| Back to top |
|
|
| MQ_user1 |
| Posted: Thu Jul 24, 2014 8:39 am Post subject: |
|
|
Novice
Joined: 09 Jun 2010
Posts: 19
|
Hi All,
It worked after making changes said by kimbert and also updating file input node properties as DFDL parser as I'm using File input node to fetch the files from remote server.
Thanks All.
ARaj |
|
| Back to top |
|
|
|
|
