Dealing with CRLF

[deerhunter]

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

[marcus_sommer]

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

[deerhunter · Accepted solution]

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

maybe i missed something

[marcus_sommer]

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

[deerhunter]

I included a test app in the original post

[marcus_sommer]

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

[deerhunter]

Very sorry i just added the data to the post.

Thanks

[marcus_sommer]

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

- Marcus

[deerhunter]

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