Hopefully Simple Question

[J]

Hello all,

I am new to the software, and I am trying to use it for some data conversions. I have a file right now with only two fields in it delimited by a pipe (|). The first is an ID field, and the second is just a text field, but it sometimes wraps on multiple lines. I want to string it out across one line for each record. I do not care if the id column is included in the field or not, as I can parse them apart later. I have each record separated by at least one blank line.

Here's a sample of the data format:

7 | AAAAABBBBBCCCCCC

8 | AAAAABBBBBCCCCCC
DDDDD


9 | AAAAABBBBBCCCCC
DDDDDEEEEEFFFFFGGG
GGHHHHHIIIII

10 | AAAAA

11 | AAAAABBBBBCCCCC
DDDDDEEEEE

And here is the format I would like to get it in:

7 | AAAAABBBBBCCCCCC

8 | AAAAABBBBBCCCCCCDDDDD


9 | AAAAABBBBBCCCCCDDDDDEEEEEFFFFFGGGGGHHHHHIIIII

10 | AAAAA

11 | AAAAABBBBBCCCCCDDDDDEEEEE

Basically I just want to concatenate any additional lines in to the same field. I have tried a couple different solutions and have not had any luck yet. I am hoping there is a simple solution that I am missing. Any help would be appreciated.

Thanks!

[Grant Perkins]

Hi J,

First question is - is this an ascii text file (perhaps from a unix/linux system?) and do you know if it contains hard coded line feed characters?

If not it may simply be that the 'page format' for the report is wrapping the text in which case you might be able to simply save the report with a different format (a3 Landscape for example or anything rather wide setting - even user defined maybe?) and work around it that way.

On the other hand ...

If the line feeds are embedded in the file you could strip them out/change them to spaces or whatever might be useful using an editor or the MSRP utility.

You could then read the file as a delimited database if you have an appropriate Pro version of Monarch available.

Similarly you could change the | to another character and then use the new character as a floating trap if you have V6 or V7 Pro.

"|" is a special case here. A great character to use for delimited file but just happens to clash with a specific trap definition character when used in a report file. Not a problem if you wanted to (and could) read the file as a delimited input database file.

Or ... (since you may not have a Pro version)

You could do as you suggest and pick all the lines for a record using a trap that requires a non blank line to start the record, make the field a single line the width of the entire report. Then set the "field options" to "End Field On" and pick one of the options that will stop selecting the field at a blank line.

That should give you your single field extract ready for parsing and you can then easily split the ID section from the main field (or should be able to but there may be something in your data that complicates that process as well. On the other hnd life is sometimes reasonable to us sp we can but hope!)

Which way to go is a little dependent on the answers you have for the questions and then deciding which option offers the most effective way forward over all.

If this doesn't mean much to you let us know (and tell us which version you have) and we will come up with some more ideas.

Al the best,

Grant



Originally posted by J:
Hello all,

I am new to the software, and I am trying to use it for some data conversions. I have a file right now with only two fields in it delimited by a pipe (|). The first is an ID field, and the second is just a text field, but it sometimes wraps on multiple lines. I want to string it out across one line for each record. I do not care if the id column is included in the field or not, as I can parse them apart later. I have each record separated by at least one blank line.

Here's a sample of the data format:

7 | AAAAABBBBBCCCCCC

8 | AAAAABBBBBCCCCCC
DDDDD


9 | AAAAABBBBBCCCCC
DDDDDEEEEEFFFFFGGG
GGHHHHHIIIII

10 | AAAAA

11 | AAAAABBBBBCCCCC
DDDDDEEEEE

And here is the format I would like to get it in:

7 | AAAAABBBBBCCCCCC

8 | AAAAABBBBBCCCCCCDDDDD


9 | AAAAABBBBBCCCCCDDDDDEEEEEFFFFFGGGGGHHHHHIIIII

10 | AAAAA

11 | AAAAABBBBBCCCCCDDDDDEEEEE

Basically I just want to concatenate any additional lines in to the same field. I have tried a couple different solutions and have not had any luck yet. I am hoping there is a simple solution that I am missing. Any help would be appreciated.

Thanks!

[Grant Perkins]

J,

I just checked back in the archives (unintentionally!) and remembered your earlier post. So I figure you have Version 7, but not sure if you selected the Standard or Pro version.

Or maybe you don't have 7?

Grant

[J]

Hi Grant,

The text file is actually an export from some dos based system. (I could specify a character other than the pipe as a delimiter if that would matter.) I am not sure if it contains hard coded line feed characters or not.

I would like to be able to read it as a database file, as that is the end goal, but cannot do so because the text wraps and it does not understand it as a delimited file.

I have version 7 pro, which I just bought.

I did create a template specifying a non blank line as a beginning trap and a blank line as the "End field on" option, and it does select the data I want, but does not concatenate the field. It turns out like this:

7 | AAAAABBBBBCCCCCC
8 | AAAAABBBBBCCCCCC
DDDDD
9 | AAAAABBBBBCCCCC
DDDDDEEEEEFFFFFGGG
GGHHHHHIIIII
10 | AAAAA
11 | AAAAABBBBBCCCCC
DDDDDEEEEE

instead of like this:

7 | AAAAABBBBBCCCCCC
8 | AAAAABBBBBCCCCCCDDDDD
9 | AAAAABBBBBCCCCCDDDDDEEEEEFFFFFGGGGGHHHHHIIIII
10 | AAAAA
11 | AAAAABBBBBCCCCCDDDDDEEEEE

Any suggestions?

[Grant Perkins]

J,

If there is any chance you could send me a page of the file that would be great - I'm working in the dark a little at the moment and I really like to see the light in the file! It's just the way I work. Confidentiality guaranteed but if you need to edit the data that's fine as long as core output format is unchanged.

I don't have access to my V7 installation for couple of days but that should not make a lot of difference at this point.

That said, is the ID field always numeric? If so simply trap on the first character(s) of a line being numeric (assuming that any subsequent lines in the record do not start with a numeric character or character combination) and the multi line template should work. (This, rather than non- blank, is what I maent to put in my previous post.)

However I do expect there could be some anomalies with this idea if your second field is likely to have any numeric data in it.

I would be quite happy to use the sample information you posted but fear it may have lost something important to your requirements as part of the posting. It does happen.

Private message with my email address will follow shortly.

Grant



Originally posted by J:
Hi Grant,

The text file is actually an export from some dos based system. (I could specify a character other than the pipe as a delimiter if that would matter.) I am not sure if it contains hard coded line feed characters or not.

I would like to be able to read it as a database file, as that is the end goal, but cannot do so because the text wraps and it does not understand it as a delimited file.

I have version 7 pro, which I just bought.

I did create a template specifying a non blank line as a beginning trap and a blank line as the "End field on" option, and it does select the data I want, but does not concatenate the field. It turns out like this:

7 | AAAAABBBBBCCCCCC
8 | AAAAABBBBBCCCCCC
DDDDD
9 | AAAAABBBBBCCCCC
DDDDDEEEEEFFFFFGGG
GGHHHHHIIIII
10 | AAAAA
11 | AAAAABBBBBCCCCC
DDDDDEEEEE

instead of like this:

7 | AAAAABBBBBCCCCCC
8 | AAAAABBBBBCCCCCCDDDDD
9 | AAAAABBBBBCCCCCDDDDDEEEEEFFFFFGGGGGHHHHHIIIII
10 | AAAAA
11 | AAAAABBBBBCCCCCDDDDDEEEEE

Any suggestions?

[Tom Whiteside]

J, if your specific problem is not being able to concatenate your word-wrapped text fields, take a look at Nick Osdale-Popa's excellent posting (http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=000099#000002) on this same topic that solved my own problem with wrapped fields.

I had set up a calculated field concatenation function that did not work properly until Nick showed that there were two invisible ASCII characters, the carriage return (Cr, ASCII Chr(13)), and the line feed (Lf, ASCII Chr(10)), that needed to be included in the formula. After that, the calculated field function with RTrims and LSplits worked great!

Take a look at my version of your problem and Nick's formulaic solution - - and good luck!

[ May 03, 2006, 03:59 PM: Message edited by: Todd Niemi ]