newline character in middle of csv column

[Anonymous]

I am fetching data using tSoap component in which i am getting result in XML format as comma separated values. In which columns are separated by comma and rows are separated by '\n'.

After that i am using tExtractXMLField component for extracting data from the response.

But in data i have '\n' within the strings which is treating it as a new row. I tried using tReplace component to remove \n within the quotes using regex but data is too large, result causing StackOverflowError. 

Also I tried using tNomalize component to separate the rows using CSV option, but the problem still persist.

Can you please help me on this. Thanks in advance.

[Anonymous]

Hi,

What does your xml format data look like? Could you please set an example for us?

Best regards

Sabrina

[vharcq]

The only way to make it works is to have "" as Text Enclosure : "a","b\nb,b","c" can then be loaded

If you don't then you will never read the data correctly frrm the begining because the comma will cut your columns not as you want

Create a Metadata / File Delimited

with this options :

Field separator : Comma

Row separator : standard EOL

Escape Char Settings:

   Escape Char : Empty

   Text Enclosure : "\""

[Anonymous]

Hi Sabrina,

Response which i am getting from the soap request is:

<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/">
<env:Header/>
<env:Body>
<ns2:getReportResultCsvResponse xmlns:ns2="http://service.admin.ws.five9.com/">
<return>TIMESTAMP,CALL ID,NOTES
"Mon, 17 Apr 2017 10:05:38",4223519,
"Mon, 17 Apr 2017 10:05:40",4223520,
"Mon, 17 Apr 2017 10:05:41",4223521,"Alexandria..

Monday -- 55 partial
Bal -- 224 May 1

Visa"
"Mon, 17 Apr 2017 10:05:42",4223522,
"Mon, 17 Apr 2017 10:05:43",4223523,
"Mon, 17 Apr 2017 10:11:04",4223524,
"Mon, 17 Apr 2017 10:05:43",4223524,
"Mon, 17 Apr 2017 10:05:45",4223525,</return>
</ns2:getReportResultCsvResponse>
</env:Body>
</env:Envelope>

Here as we can see "notes" column having data which have '\n' in it in between the quotes, and it is causing issue for extracting data. Can you please tell me how can i resolve this issue.

Nikhil 

[Anonymous]

Hello,

I tried creating metadata but the problem still persist.

I am extracting data from XML response which looks like:

<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/">
<env:Header/>
<env:Body>
<ns2:getReportResultCsvResponse xmlns:ns2="http://service.admin.ws.five9.com/">
<return>TIMESTAMP,CALL ID,NOTES
"Mon, 17 Apr 2017 10:05:38",4223519,
"Mon, 17 Apr 2017 10:05:40",4223520,
"Mon, 17 Apr 2017 10:05:41",4223521,"Alexandria..

Monday -- 55 partial
Bal -- 224 May 1

Visa"
"Mon, 17 Apr 2017 10:05:42",4223522,
"Mon, 17 Apr 2017 10:05:43",4223523,
"Mon, 17 Apr 2017 10:11:04",4223524,
"Mon, 17 Apr 2017 10:05:43",4223524,
"Mon, 17 Apr 2017 10:05:45",4223525,</return>
</ns2:getReportResultCsvResponse>
</env:Body>
</env:Envelope>

after that i am fetching required data using string() giving into Loop Xpath query in tExtractXMLField. After that which component i should use to fetch data, can you please help me with that.

Regards

Nikhil