| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| DFDL For CSV with Double quotation Marks and commas inside |
« View previous topic :: View next topic » |
| Author |
Message
|
| KJCB |
 Posted: Thu Jan 25, 2018 7:57 am Post subject: DFDL For CSV with Double quotation Marks and commas inside |
 |
|
Apprentice
Joined: 09 Dec 2017
Posts: 26
|
Hi All,
I need to parse a CSV file which has the following contents, more or less:
"Column1", "Column2", "Column3"
"Value, in column1" , "Value, in column 2", "Value, in column3"
As you can see, the CSV file is using comma as the separator. Values of each column are always inside double quotation marks and they also have commas inside.
I have created a DFDL model for my CSV, and when testing it its crashing because he understands all the commas as separators. I didn't make any configuration change, I just left default values.
Is there any configuration option to specify that the values inside double quotation marks is the whole column, so that we avoid parsing inside it?
Thanks for your help |
|
| Back to top |
|
 |
| Vitor |
| Posted: Thu Jan 25, 2018 8:06 am Post subject: Re: DFDL For CSV with Double quotation Marks and commas insi |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| KJCB wrote: |
| I have created a DFDL model for my CSV, and when testing it its crashing because he understands all the commas as separators. I didn't make any configuration change, I just left default values. |
How did you create it? The IBM CSV wizard sets this up for you.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| KJCB |
| Posted: Fri Jan 26, 2018 12:58 am Post subject: Re: DFDL For CSV with Double quotation Marks and commas insi |
|
|
Apprentice
Joined: 09 Dec 2017
Posts: 26
|
| Vitor wrote: |
| KJCB wrote: |
| I have created a DFDL model for my CSV, and when testing it its crashing because he understands all the commas as separators. I didn't make any configuration change, I just left default values. |
How did you create it? The IBM CSV wizard sets this up for you. |
Hi,
Thanks for your answer.
I did follow the Integration Bus assistant:
1. Create a new message model
2. Select "CSV Text" type
3. Create DFDL Schema using wizard
4. Select following options:
End of record is Carriage Return + Line Feed
The first record is a Header
Escape Scheme: CSV Escape Scheme
Thanks for your help |
|
| Back to top |
|
|
| timber |
| Posted: Fri Jan 26, 2018 1:49 am Post subject: |
|
|
Grand Master
Joined: 25 Aug 2015
Posts: 1292
|
| Quote: |
| its crashing because he understands all the commas as separators |
I find that hard to believe. The CSV escape scheme is specially designed to cope with your (very standard) CSV format.
Please supply evidence. If necessary, cut down your CSV model to three fields, and supply a single line of data. Then quote the input data and the DFDL trace (in [c o d e] tags please). |
|
| Back to top |
|
|
| abhi_thri |
| Posted: Fri Jan 26, 2018 3:48 am Post subject: |
|
|
Knight
Joined: 17 Jul 2017
Posts: 516
Location: UK
|
I tried with the same model def and input msg,
Orig msg:
| Quote: |
"Column1", "Column2", "Column3"
"Value, in column1" , "Value, in column 2", "Value, in column3"
|
and got the below error at DFDL trace,
| Quote: |
| 26 Jan 2018 11:40:56 error: CTDP3062E: An unexpected non-postfix separator ',' occurs in a postfix position at offset '75' in 'sequence' group contained within element 'record', xpath: '/DummyCSV[1]/record[1]'. |
tried removing the spaces after element delimiter , and the next element which worked fine.
Modified msg:
| Quote: |
"Column1", "Column2", "Column3"
"Value, in column1" ,"Value, in column 2","Value, in column3" |
So it looks like 'CSV Escape Scheme' cannot deal with the extra space b/w elements scenario |
|
| Back to top |
|
|
| Vitor |
| Posted: Fri Jan 26, 2018 5:51 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| abhi_thri wrote: |
| So it looks like 'CSV Escape Scheme' cannot deal with the extra space b/w elements scenario |
Yes, because that's a non-standard CSV format.
Try opening your extra-space-between-elements data in Excel and see what happens.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| timber |
| Posted: Fri Jan 26, 2018 2:48 pm Post subject: |
|
|
Grand Master
Joined: 25 Aug 2015
Posts: 1292
|
| Quote: |
| it looks like 'CSV Escape Scheme' cannot deal with the extra space b/w elements scenario |
Good guess, but not quite correct. The escape scheme controls how the parser deals with delimiters ( commas, in this case) that occur within the value of an element.
The property that you need to modify is the Separator property. Your separator is not a comma. It is a comma plus some optional whitespace. So you need to set the separator to %WSP*;,
Or maybe %WSP*,%WSP* if there might be one or more spaces after the comma as well. |
|
| Back to top |
|
|
| KJCB |
| Posted: Thu Feb 15, 2018 8:48 am Post subject: |
|
|
Apprentice
Joined: 09 Dec 2017
Posts: 26
|
Hi All,
Thanks for your help, finally a change was applied in the original system so that it generates the CSV in a more friendly way (it was just a configuration change for them).
Regards & Thanks |
|
| Back to top |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
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
|
|
|
|
