Removing line feeds from data in a field

Panorama X
1

I used to do this with text funnels and the option-7 ¶ character back in the day. Now, I understand with Unix, I would have to search & replace Line Feeds (rather than carriage returns). But, of course, I’m stuck.

Here is a song list - single record in a single field:

We Are The Champions3:30
All My Loving2:15
Last Train to Clarksville3:03

I want to essentially strip away non-alphabetic characters…and replace the line feeds with a comma+space so that I end up with:

We Are The Champions, All My Loving, Last Train to Clarksville

Can anyone point me to a function, or a way to achieve this?

Thanks, Karl

2

Try this:

stripchar(replace(«»,lf(),", "), "azAZ  ,,")
3

In case anyone doesn’t know what the «» in Gary’s example code is, that simply is the current field. If you wanted to specify a particular field, just use that, for example:

stripchar(replace(SongList,lf(),", "),"azAZ  ,,")

If you’re not sure whether the text contains carriage returns or line feeds, it’s easy to make sure they are all linefeeds with the crtolf( function.

stripchar(replace(crtolf(SongList),lf(),", "),"azAZ  ,,")

Or, you could convert them all to carriage returns.

stripchar(replace(lftocr(SongList),cr(),", "),"azAZ  ,,")

Instead of the replace( function you might like what the commalist( function does.

stripchar(commalist(lftocr(SongList),cr()),"azAZ  ,,")

This will produce output like this:

We Are the Champions, All My Loving and Last Train to Clarksville

4

Hi Gary.
Thanks so much. This pretty much did the trick.
I’m still ending up with a space before the comma, however.
I tried a couple of replacement & nested function approaches, but the space remains. I’m thinking now that it’s a tab, rather than a space.

Karl

5

Thanks, Jim.

Didn’t know about commalist(. That’s a very nice solution, too - though the extra “and” is not ideal for my application.

Gary’s approach was 99% there, but I was still ending up with a stray space between the song title and the comma. Thought it might have been a tab, so (after many failed attempts!) I successfully nested the replace( to filter out any tabs.

However, that didn’t solve it either. Turns out (after inspecting in BBEdit) there was a space right before each tab in the original list I was inputting. And because stripchar( was being told to keep spaces (between the words in each song title), that stray space was being kept as well.

So, the solution became:

 stripchar(replace(replace(Tracks," "+tab(),""),lf(),", "),"azAZ  ,,--&&")

The field «Tracks» contains the array of song titles.

  1. Search for any combination of space AND tab - replace with “”. (wipe it out)
  2. Using the output of the innermost replace(, next search for linefeeds and replace them with a comma FOLLOWED BY a space.
  3. Finally, take the output of the outermost replace( and strip any chars from it that are not alphabetic, spaces, hyphens or ampersands (all which occur in song titles).

This works great since I’m primarily trying to eliminate the song length of each track in the list.

HOWEVER, it presents a new problem. “Revolution 9” will now become “Revolution” since numeric values are stripped. Even worse, “8675309/Jenny” becomes “/Jenny”! So, I may have to rework this to replace/remove the time pattern of #:## first.

Got a function for that?

Karl
– and thanks to you & Gary for getting me this far!

6

Try your old text funnel friends in a different context. You wanted to use them on each line of a text array, so use a text funnel within an arrayfilter(:

replace(arrayfilter(crtolf(Tracks), lf(), {strip(import()[1,tab()])}),lf(),", ")

  1. crtolf( regularizes your row separators to just lf()
  2. [1,tab()] chops each row off after the tab(), removing your play time
  3. strip( removes leading and trailing white space (spaces or tabs) from that, leaving your title with whatever characters it had
  4. replace( then converts your separators to comma space, without the ‘and’
  5. the {} were used because the formula within arrayfilter( needs to be quoted
1 Like
7

I was going to jump in with a suggestion to use arrayfilter( but John beat me to it. My work here is done :partying_face:

8

Database Exchange has a database by Gary Yonaites that shows invisible characters: “Reveal Invisible Text”. Very useful.

1 Like
9

One final shot at this. Here is a combination of an arrayfilter( that strips off the ending time info and any trailing spaces or tabs and then uses the replace( function to change the line feeds to a comma and space. This leaves everything before the running time untouched. The cascaded text funnel first removes everything up to the “:” of the time and then the second part removes the “:” and the first number. Finally the strip( function removes any hidden white space characters in the track name. The only problem would be if you have any tracks that run 10 minutes or longer.

replace(arrayfilter(crtolf(theList), lf(),{strip(import()[1,"-:"][1,-3])}),lf(),", ")

This goes a little beyond John’s solution which does not deal with removing the time information.

10

After writing last night, I went to sleep thinking that text funnels was the way to go. (Love them!) But, understanding that they operate on a line of text (rather than a list), I had no clue as to how to employ that solution.

And you show up with the answer! Beautiful. This absolutely works! I’m gonna have to study it a bit to wrap my head around that import() and the array bit. So, thanks for supplying the solution - and especially the explanation. That’s very helpful when one is learning how to function(.

11

Gary,
Karl’s original post doesn’t display them, but he later mentioned his data had space tab between the titles and the times. I’d considered test funnels keying on the : but the possibility of long songs over 10 minutes plus titles ending ending in digits offered potential confusion. Without space tab would Revolution/12:34 be 2:34 long “Revolution/1” or 12:34 long “Revolution/“? Keying on the tabs, once I knew they were there, removes any length times and keeps title digits. Had his data been as first shown, without space tab and with all times reliably 4 characters, m:ss yours would be my solution as well.

12

I’m pretty lazy - do you expect me to read everything? :yawning_face:

1 Like
13

Well thanks all… John, Jim, Cooper & Gary.

Now, with functions you need to use repeatedly, how do you guys access/store them? Doesn’t appear that I can create an Action menu item with it.

Right now, I’m having to do Field>Morph>Fill With Formula… and paste the function in from a text editor each new time I need to use it.

14

People frequently construct a dedicated DB with frequently used procedures in it and access them via a far call. For functions I think you can store them in your own Pan library whence they will be easily accessible all the time.

15

If you are talking about just within that one database, the command is called formulafill

You would write a one line procedure using that statement. The name you give the procedure will appear on the Action menu.

It would be two lines if you also want to program the act of making that field the active field. That step would be done with a field command.

16

You can also have a custom function registered in your .Initialize procedure and it will then be available for all databases in the current session. Add this code to your .Initialize procedure (create this procedure if it does not already exist):

registercustomfunction  "CLEANDATA(",1, |||replace(arrayfilter(crtolf(•1), lf(), {strip(import()[1,tab()])}),lf(),", ")|||

Now you can use cleandata(«») in the Morph dialog to clean the current field or change the chevrons to the name of another field if you want the current field to be filled with these results. You can also use the new function in a procedure with a formulafill:

field myField
formulafill cleandata(«»)

You can use it as a simple formula to clean up a single field cell or a variable. There is also a way to include this function automatically whenever Panorama runs but that is more involved.

17

Could you imagine a system whereby a user could check a box at the top of a procedure window that said ‘Save As Favorite’ and that alone, saved that procedure to a user library whereby it was always available to the user regardless of the database file that they opened, perhaps displaying in a top group of the Procedures listing so that the user could open it up and modify it sometime in the future manipulating it as easily as if it was sitting that that particular database?

18

A feature like this would require a lot of thought. Here are some questions that immediately pop into my mind.

  • What does “always available to the user” mean? I would presume it meant in the Action menu. If so, should these items go in the top of the Action menu, or the bottom?
  • Should these items always be available, or only when this database is open? If the former, does that mean adding an item as a favorite would remove it from the database it was originally created in? It would be confusing if it was in two places. And what if the original database was renamed, moved, or completely deleted? Or what if you made a copy of the database, does the favorite now appear twice? If not, which one is the “real” favorite.
  • If multiple databases contained favorite procedures, how would you decide what the order was in the Action menu? And what if more than one database contained a favorite procedure with the same name? How would you know which one you were triggering.
  • If you started using this feature a lot, quickly you might have 20, 30 or 100 favorite procedures. That is going to be very unwieldly.
  • On the other hand, almost all procedures reference fields in the current database, so they wouldn’t work if used with a different database. So this would really only be useful with procedures that were specially written to reference only the current field, or perhaps the current cell. For example, take the formula that has been developed in this thread. This formula is really quite specialized for a particular unusual data format in one particular field in one particular database. I can’t imagine any reason why you would want this available in all of your databases.

Of course there are various ways these questions could be answered. Probably different users here would have different answers. Before anyone spends a lot of time thinking about the questions I’ve posed, I should warn you that a feature like this is extremely unlikely to be added to Panorama anytime soon.

I’m not saying that a feature for sharing procedures could never happen. I’ve thought of this idea before, and puzzled about how to do it in a useful and understandable way. But sometimes something that looks so simple in theory turns out to have a lot of complications when you look into all of the ramifications.

19

I have a database that contains nothing but my favorite procedures. I can open it anytime, copy a procedure, paste it into my current file and edit it. Maybe all we need is a wizard where you add a favorite procedure with a button at the top of the procedure.

20

“THAT was the equation!” (THAT was the equation 141, Actor Ted Cassidy as "Ruk" - YouTube)

Don’t know why I didn’t realize I could use formulafill apart from the Morph menu. Of course. Doh!

Thanks, Dave!