Complex CSV file reading

[Former Member]

Hi Experts,

I am trying to read a csv file in zip.So I used File adapter module "PayloadZipBean" to unzip and read csv with MTB(Message Transform Bean) and its working fine.

Now I am getting error in case if my file has multi lines for a field value.

Eg::  

field1,field2,field3,filed4

"val1","val2","val3and

desription of line details","val4"

MTB parameters are : is it possible to read multi line values of single field with MTB ,if not what is other best alternate simple approach.

Please let me know the approach not just links as I cant understand the way to go ahead.

zip.mode		unzip
Transform.Class		com.sap.aii.messaging.adapter.Conversion
Transform.ContentType		text/xml;charset=utf-8
xml.conversionType		SimplePlain2XML
xml.documentName		MT_test_SND
xml.documentNamespace		http:\\xyz
xml.documentNamespaceScope	root
xml.enclosureConversion		NO
xml.enclosureSign		"
xml.encoding		UTF-8
xml.endSeparator		"'nl'
xml.fieldNames		f1,f2,f3,f4...f10
xml.fieldSeparator		,
xml.processFieldNames		fromConfiguration
xml.structureTitle		Row

Regards

Siri

[RaghuVamseedhar · Accepted solution]

Siri,

Please follow this blog

[Former Member · Accepted solution]

Hi Raghu,

Thanks a lot for blog.. I have tried this approach and now I can able to overlook "nl" in data.

I still have 2 queries.

1) If I am having "," in between any data its treating it as separate field.

eg:

"Name2","345",he

is CEO,from now onward","IND"

In such case I am missing "IND"

2) Is UDF remains same if I map it to a RFC, I need to map onlt to relevant segments in target from UDF?

Eg: instead Records. do I need to take my relevant segment?

Records.addValue(""); 

Thanks in advance...

Regards

Siri

[RaghuVamseedhar · Accepted solution]

Siri,

1. I have updated the blog to handle comma, please check it.

2. Yes UDF will be same. Please update it according to target structure.

[Former Member]

Hi Raghu/all

I observed other issue with this approach..if data is coming like below, its throwing error.

If there is no data coming in any field, its creating just commas ,, with out any quotes ..(check for Name2 row values)

UTF8

Name,ID,Description,Location

"Name1","123","He is developer","IND"

"Name2",,"He is

CEO","IND"

"Name3","567","He is CIO, from today","USA"

How to add same variation to below code

input = input.replace("\",\"", "__________").replace(",", "").replace("__________", "\",\"") 

                 .replace("\"\r\n\"", "__________").replace("\r\n", " ").replace("__________", "\"\r\n\"");

Thanks in advance.

Regards

Siri

[RaghuVamseedhar]

Siri,

Updated the UDF in blog. Please check it.

[Former Member]

Hi Varun,

Thanks for your reply..

In this case what should be the alternate way to go ahead.

could you please explain more on custom module or mapping to get this..

Regards

Siri

[Former Member]

Hi Sunitha,

Thanks for links ..

1 is about "" which we already handled,

2 is about exactly of my problem ,,but it does not working ..

May I know what could be the solution for this..looks simple but not working with given all options.

Regards

Siri.

[Former Member]

Hi Experts,

Good day..

Any inputs for me on this please ...

Regards

Siri

[former_member192892]

Hi Siri,

Unfortunately I don't see this happening without custom module/custom mapping.

Regards

Varun

[Former Member]

Hi all,

Any inputs on this for me to read CSV file with multiple lines.

Eg:: 

field1,field2,field3,filed4

"val1","val2","val3and

desription of line details","val4"

Regards,

Siri

[sahithi_moparthi]

Hi Siri,

<<Any inputs on this for me to read CSV file with multiple lines

You mean that,you have multiple rows in the files ??

If that is the case just mention Row,*.

Can you please provide the sample data for refernce.

[Former Member]

Hi Sahiti,

Thanks for reply..

If any one filed value is having line break in it I am getting error,do we have any option to escape "nl" in between filed values.

Eg::  Failing case:

field1,field2,field3,filed4

"val1","val2","val3and

desription of line details","val4"

"val1","val2","val3and

desription of line details","val4"

"val1","val2","val3and

desription of line details","val4"

its processing if data come like this,in single line:

field1,field2,field3,filed4

"val1","val2","val3anddesription of line details","val4"

"val1","val2","val3anddesription of line details","val4"

"val1","val2","val3anddesription of line details","val4"

Regards

Siri.

[sahithi_moparthi]

Hi Siri,

Please refer below link.

https://scn.sap.com/thread/1725047

http://scn.sap.com/thread/1893483