Cleaning up soft returns in database field content

[Russell B]

I often convert database content moving from one type of database to the other. I always hit an issue with the soft returns in data within fields, where line breaks were entered in the application - they convert into weird characters when exported to text. Is there any way of cleaning these up in BBEdit without interfering with the hard returns that delineate a new record? I usually work in Excel or Filemaker pro.

[Chris]

Russell

Have you tried the command Text: Zap Gremlins ?

--Chris(topher)?

On Sep 24, 2021, at 5:32 AM, Russell B <rus...@bastock.net> wrote:



I often convert database content moving from one type of database to the other. I always hit an issue with the soft returns in data within fields, where line breaks were entered in the application - they convert into weird characters when exported to text. Is there any way of cleaning these up in BBEdit without interfering with the hard returns that delineate a new record? I usually work in Excel or Filemaker pro.

--
This is the BBEdit Talk public discussion group. If you have a feature request or need technical support, please email "sup...@barebones.com" rather than posting here. Follow @bbedit on Twitter: <https://twitter.com/bbedit>
---
You received this message because you are subscribed to the Google Groups "BBEdit Talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email to bbedit+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/bbedit/c2a16db0-85f6-4716-a147-46a94fc90d7bn%40googlegroups.com.

[David Kelly]

On Sep 24, 2021, at 2:33 PM, Chris <ch...@rustydogink.com> wrote:

> Have you tried the command Text: Zap Gremlins ?

But would one really wish to destroy soft returns in a data field? Presumably somebody put them there for a reason? And the question arises because the intake database isn’t recognizing them as soft returns?

When transposing data from one database to another using text as an intermediary I don’t know enough about his process to know whether the hard returns (which he likes) are record separators or field separators. The soft returns would seem to be inside fields. So I’d say global search/replace with whatever the intake database recognizes as a soft return. Might not accept soft returns inside a field.

--
David Kelly N4HHE, dke...@HiWAAY.net
============================================================
Whom computers would destroy, they must first drive mad.

[ThePorgie]

I do this all the time, but doing it in BBedit can mess up where the data goes related to the column. I keep the data in Excel and use

=SUBSTITUTE(B2,CHAR(10),"|")

Sub out B2 with your column. I try and replace with something very distinctive. I used a pipe in the above.

NOTE: A hard return would be CHAR(13) instead of CHAR(10)

After I run that I copy and paste the values. Lastly Data->Text to Columns and use the pipe to delimit.

One final note: Excel will leave the pipe in the delimit option until you change it back to use a tab. That can be unexpected the next time you go to paste tab delimited copy.

I am also going to attach my Excel ASCII Chart for reference.

[Media Mouth]

Hi Russell,

Does your workflow involve round trips between Excel and FileMaker or between FMP/Excel and other applications?

Excel & FMP play nice with each.  Line feeds in either one will import/export to the other with no strange characters.

FMP has an arsenal of tools that pretty much guarantees you can export to just about software of any kind, handling any and all character encoding / translation issues.   You could even extend that to importing from Excel to FMP then exporting to the 3rd part app.

On Sep 23, 2021, at 12:33 AM, Russell B <rus...@bastock.net> wrote:

I often convert database content moving from one type of database to the other. I always hit an issue with the soft returns in data within fields, where line breaks were entered in the application - they convert into weird characters when exported to text. Is there any way of cleaning these up in BBEdit without interfering with the hard returns that delineate a new record? I usually work in Excel or Filemaker pro.

[Patrick Woolsey]

Can't be sure without an example but if you could please post a
link to a (Zipped) instance, I'd be happy to take a look.

Meanwhile, please note you can use the Character Inspector
palette (via Window -> Palettes) to identify any individual
character (or string), and depending on the desired outcome, you
can use Zap Gremlins and/or search & replace with characters in
hex escape form (as detailed in Chapter 7 of the PDF manual).


Regards

Patrick Woolsey
==
Bare Bones Software, Inc. <https://www.barebones.com/>



[PS: You all didn't really think I'd post without making at
least one manual reference, did you? ;-) ]

[Russell B]

Thanks Harvey - that gave me an idea and I successfully used the substitute function within FileMaker Substitute ( Field ; "¬" ; ¶ ) to restore the returns. The ultimate destination is a LastPass vault but I'll need more time to see what that accepts so I'll see how I go in that arena before I post here again.

[MediaMouth]

Re: LastPass

Cool.  Looking forward to hearing how it goes

Depending on your use case FMP has built in encryption/decryption, so if it's only for yourself it can serve as your keychain.

On Sep 24, 2021, at 19:39, Russell B <rus...@bastock.net> wrote:

Thanks Harvey - that gave me an idea and I successfully used the substitute function within FileMaker Substitute ( Field ; "¬" ; ¶ ) to restore the returns. The ultimate destination is a LastPass vault but I'll need more time to see what that accepts so I'll see how I go in that arena before I post here again.

To view this discussion on the web visit https://groups.google.com/d/msgid/bbedit/31b82c2a-3131-4c0b-8980-62dfaf2bf6b9n%40googlegroups.com.

[Russell B]

After a bit of testing I got the file out of a very old password manager in txt format, into FileMaker to clean and cull and then out to Excel via csv that LastPass was happy with. There were 500 entries in the pw manager so that's why it was worth the effort. I've been in IT too long! Even though Excel was not wrapping the comments field contents, a copy and paste of the comments field into BBE confirmed they were still there and LastPass displayed the returns.