Parse a memo field into multiple detail row fields

[pbsibex]

I have a memo field of indeterminate length (paragraph to pages of text) that I need to break out into 80 characters/ field in each Detail row until the whole field has been parsed out.

I'm using Crystal 2008 on an SQL database. Thanks in advance for any ideas.

 

[lbass]

Try SQL expressions like:

[{%1st80}]
{fn substring(`table`.`memo`,1,80)}

[{%2nd80}]
{fn substring(`table`.`memo`,81,80)}

Depending upon your datasource, you may be able to use:

substr(`table`.`memo`,81,80)

You could place each one in a different detail section.

-LB

 

[pbsibex]

I only want to create one Detail section and have it loop through and create the new Detail rows at they are needed. There could literaly be 100's of rows needed and so creating another Detail row for each different value wouldn't work to well.

Any ideas on how to create a Group that is based on a For/Do loop type thing?

 

[lbass]

You can't create new detail rows using a loop. You could, of course, use a formula to create a return after every 80 characters, but this still appear in one detail row, just in separate lines within that detail section. Would that work for you?

-LB

 

[pbsibex]

I didn't think there was going to be a way to make an variable number of Detail rows. OH well ...

Pulling in separate 80 character fields will work becuase it needs to then be exported and then imported into another system. Exporting it into a text or .csv file should work fine.

I'm new with looping formulas. Like French, I can read, but can't write. Any help is appreciated.

 

[lbass]

If you want separate fields, then you need to use my first suggestion. Using a loop, you would just get one field with returns after every 80 characters--and it would still export into one cell in Excel for example.

I don't use CSV, so using my first suggestion, I would test this using just a few SQL expressions before taking it too far. You could do the same thing with formulas:

mid({table.memo},81,80) //second 80

...but SQL expressions are more efficient.

-LB

 

[pbsibex]

I'd like to try and experiment with what the different export formats will end up with. How would a loop be structured? Great advice .. Thanks a bunch

 

[lbass]

stringvar x := {table.memo};
numbervar i;
numbervar j := len({table.memo});
stringvar y := "";
for i := 1 to j step 80 do (
y := y + mid(x,i,80)+chr(13)
);
y

Be sure to format this formula to "can grow". It will also appear more aligned if you format it to a non-proportional font like Courier New.

-LB

 

[pbsibex]

Great stuff. The text is in HTML format on the server so I now need to work some formulas to pull the HTML coding out and only use the text in Crystal as the HTML characters throws off the count. I have some previous formulas I used to do that and will try to convert them into an SQL Command Object and pull it that way.

Thanks a ton!

 

[pbsibex]

Just realized that the text will really need to break on a word break. Any ideas on that?

 

[lbass]

Well, but how would that work with your requirement of 80 characters per line?

-LB

 

[pbsibex]

I didn't mention that this is a patient's diagnostic report and the new system it's being migrated to has line breaks at 80 characters. When a new report is created in that system it can automatically wrap the words perfectly, but since we're migrating outside data to it, it really needs to have the text read without having words break in the middle of them.

How about a loop in a loop with the inner loop that keeps going out and finding the next ' ' and counting the # of caharacters so far and comparing that to 80. Apparently the new system (we think) can handle it if it's not exactly an 80 character row as well - so no padding at the end of a row would be necessary either.

I need to take a course in some advanced loop type stuff it seems. Thanks again for any advice or help. I'm getting a lot out of all this. Funny thing is I can do this easily in Excel, different commands though. Trying to duplicate in Crystal:
=LEFT(A2,FIND(CHAR(1),SUBSTITUTE(A2,",",CHAR(1),2) ) )
This will look for the ',' makr and find the 2 one. By checking the length I can then compare it to 80 and build from there ...