ASG
IBM
Zystems
Cressida
Icon
Netflexity
 
  MQSeries.net
Search  Search       Tech Exchange      Education      Certifications      Library      Info Center      SupportPacs      LinkedIn  Search  Search                                                                   FAQ  FAQ   Usergroups  Usergroups
 
Register  ::  Log in Log in to check your private messages
 
RSS Feed - WebSphere MQ Support RSS Feed - Message Broker Support

MQSeries.net Forum IndexWebSphere Message Broker SupportDFDL For CSV with Double quotation Marks and commas inside

Post new topicReply to topic
DFDL For CSV with Double quotation Marks and commas inside View previous topic :: View next topic
Author Message
KJCB
PostPosted: Thu Jan 25, 2018 7:57 am Post subject: DFDL For CSV with Double quotation Marks and commas inside Reply with quote

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
View user's profile Send private message
Vitor
PostPosted: Thu Jan 25, 2018 8:06 am Post subject: Re: DFDL For CSV with Double quotation Marks and commas insi Reply with quote

Grand High Poobah

Joined: 11 Nov 2005
Posts: 25361
Location: Ohio, 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
View user's profile Send private message
KJCB
PostPosted: Fri Jan 26, 2018 12:58 am Post subject: Re: DFDL For CSV with Double quotation Marks and commas insi Reply with quote

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
View user's profile Send private message
timber
PostPosted: Fri Jan 26, 2018 1:49 am Post subject: Reply with quote

Sentinel

Joined: 25 Aug 2015
Posts: 889

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
View user's profile Send private message
abhi_thri
PostPosted: Fri Jan 26, 2018 3:48 am Post subject: Reply with quote

Master

Joined: 17 Jul 2017
Posts: 236
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
View user's profile Send private message
Vitor
PostPosted: Fri Jan 26, 2018 5:51 am Post subject: Reply with quote

Grand High Poobah

Joined: 11 Nov 2005
Posts: 25361
Location: Ohio, 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
View user's profile Send private message
timber
PostPosted: Fri Jan 26, 2018 2:48 pm Post subject: Reply with quote

Sentinel

Joined: 25 Aug 2015
Posts: 889

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
View user's profile Send private message
KJCB
PostPosted: Thu Feb 15, 2018 8:48 am Post subject: Reply with quote

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
View user's profile Send private message
Display posts from previous:
Post new topicReply to topic Page 1 of 1

MQSeries.net Forum IndexWebSphere Message Broker SupportDFDL For CSV with Double quotation Marks and commas inside
Jump to:



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
Protected by Anti-Spam ACP


Theme by Dustin Baccetti
Powered by phpBB 2001, 2002 phpBB Group

Copyright MQSeries.net. All rights reserved.