topic Re: Dealing with CRLF in App Development

Dealing with CRLF

deerhunter — Tue, 14 Feb 2017 20:34:09 GMT

Were trying to read in a file that looks something like this:

FIELD1 FIELD2 FIELD3

AAA A1 A2 A3 A4 13% 11% 9% 6%

BBB B1 B2 B3 B4 2% 22% 44% 5%

CCC C1 C2 C3 C4 7% 33% 44% 32%

for each row, FIELD2 & FIELD3 are a single field with multiple entries separated by CRLF's, each are consumed as a single row as shown above, there are 3 rows in this example.

Looking to visualize with a table and be able to export to excel the data so that it looks similar to below:

FIELD1,FIELD2,FIELD3

AAA A1 13%

AAA A2 11%

AAA A3 9%

and so on to the EOF....

Any ideas?

Thanks in advance

Message was edited by: buck master

Re: Dealing with CRLF

marcus_sommer — Wed, 15 Feb 2017 11:27:15 GMT

If you could directly read the data from an excel or a database into the above mentioned fields you could split them per subfield() which worked without a third-parameter within a load like a loop or you could use a while-loop with iterno() as third-parameter for subfield. Therefore try something like:

load

FIELD1,

subfield(FIELD2, chr(13) & chr(10), iterno()) as FIELD2

subfield(FIELD3, chr(13) & chr(10), iterno()) as FIELD3

from YourSource while iterno() <= substringcount(FIELD2, chr(13) & chr(10)) + 1;

- Marcus

Re: Dealing with CRLF

deerhunter — Wed, 15 Feb 2017 14:18:31 GMT

did not work, can you update the orig included qvf?

maybe i missed something

Re: Dealing with CRLF

marcus_sommer — Wed, 15 Feb 2017 14:54:06 GMT

What didn't work? Any error-messages? Results are different as expected - if yes how does they look like? What is the source of these data?

Currently I haven't Sense available and couldn't therefore not take a look into it.

- Marcus

Re: Dealing with CRLF

deerhunter — Wed, 15 Feb 2017 22:05:38 GMT

I included a test app in the original post

Re: Dealing with CRLF

marcus_sommer — Thu, 16 Feb 2017 07:43:15 GMT

Your app contained no data. Did you really try my suggestion?

Re: Dealing with CRLF

deerhunter — Sun, 19 Feb 2017 14:13:34 GMT

Very sorry i just added the data to the post.

Thanks

Re: Dealing with CRLF

marcus_sommer — Sun, 19 Feb 2017 16:44:28 GMT

The delimiter within the fields isn't CRLF else only LF but the logic itself worked - see it within the attachment.

- Marcus

Re: Dealing with CRLF

deerhunter — Wed, 22 Feb 2017 16:08:49 GMT

Thanks that's perfect you have been a big help !