Remove Carriage Returns

Got a LiveCode personal license? Are you a beginner, hobbyist or educator that's new to LiveCode? This forum is the place to go for help getting started. Welcome!

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller

richmond62
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 9285
Joined: Fri Feb 19, 2010 10:17 am
Location: Bulgaria

Re: Remove Carriage Returns

Post by richmond62 » Sat Apr 02, 2016 6:52 pm

As far as I recall there are a number of chars that effect a RETURN signal in the unicode lexicon:

U+240D

U+000D

well, there's 2 at least . . .

After a bit of messing around I found that the average keyboard delivers a "10" = U+000A

So why not change all those return characters to U+240D [9229] ones?

bbhank
Posts: 116
Joined: Thu Mar 17, 2016 6:04 pm

Re: Remove Carriage Returns

Post by bbhank » Sat Apr 02, 2016 7:04 pm

After all the searching and especially the great work and perseverance by Klaus and the LiveCode folks, the easiest solution turned for me out to be just to export the data, having the database admin tool, in this case phpmyadmin, take out these characters. That turned out to take just 3 steps: The export where the cleaning is done, the truncation - emptying the data from the table, and the re-import. Lots easier than trying to out play UTF-8 or changing to another format. The field itself is being changed to clean out those characters on input. This also helps alleviate problems caused by these in the main program to which the database belongs.

8)

jacque
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 7214
Joined: Sat Apr 08, 2006 8:31 pm
Location: Minneapolis MN
Contact:

Re: Remove Carriage Returns

Post by jacque » Sat Apr 02, 2016 7:27 pm

Okay, I'll give it a try. I used to be an interpreter in another life. @bbhank:

Using "revDataFromQuery(tab, cr, gConnectionID, tSQL)" tells LC to fetch the database data and when it receives that data, LC should put a carriage return after every row and tabs between each column. The characters you've chosen for the query will decide how LC sets up the data. Now imagine you have this data in the database:

Row 1 <tab> name 1 <tab> address 1
Row 2 <tab> name 2 <tab> address 2
Row 3 <tab> name 3 <tab> address line1 <cr>address line2<cr>address line3

The last row has a multiline cell. When LC receives that data, you've told it that carriage returns should separate rows, so it adds those for you:

Row 1 <tab> name 1 <tab> address 1 <cr>
Row 2 <tab> name 2 <tab> address 2<cr>
Row 3 <tab> name 3 <tab> address line1 <cr>address line2<cr>address line3<cr>

And when you make an array out of it, using a carriage return as the primary delimiter, you get five rows because that's how many carriage returns are in the text.

To avoid that, you need to tell LC to use something other than a carriage return when it sends the data back to your handler. You can use any character, but the ones axwald suggested are good because they can't be typed from the keyboard so you can be pretty sure the text itself won't contain those characters. But for display here, I'll use a pipe character:

Code: Select all

revDataFromQuery(tab, "|", gConnectionID, tSQL)
Now LC will give you back data with rows separated by pipes and with all CRs retained as part of the cell. Now you need to make an array from the data using pipe as the primary delimiter:

Code: Select all

split tData by "|" and tab
This retains the carriage returns if any exist in any cells. There is no harm doing the entire set of data this way. It will work whether the cells have returns in them or not. It also means you don't have to do any special processing when you fill the datagrid because your array will hold the correct values.

Note: if your data ever contains tabs, you'll run into the same problem except that it won't be rows, it'll be columns. Any record with tabs in it will create extra columns in that row. You should probably change the SQL request to use something other than tabs for the column delimiters too.
Jacqueline Landman Gay | jacque at hyperactivesw dot com
HyperActive Software | http://www.hyperactivesw.com

bbhank
Posts: 116
Joined: Thu Mar 17, 2016 6:04 pm

Re: Remove Carriage Returns

Post by bbhank » Sat Apr 02, 2016 7:58 pm

Look at solution taken though. First the existing table was cleaned. That, as far as we can tell so far, did away totally, with the CRs and LFs. Then the description is modified to clean any new entries.
Lots easier than finagling with code. I thought it might be easier to do with code until the group helped me learn what time it was with LiveCode. The solution I took eliminates the issue. The table only has to be cleaned once but can be re-cleaned at any time. Takes under 5 minutes and is quite simple - See above. Involves NO programming after the Description field is changed - this has to be done only once - Problem permanently solved.

There are times when code is not the most efficient and easiest answer to a problem. :wink:

jacque
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 7214
Joined: Sat Apr 08, 2006 8:31 pm
Location: Minneapolis MN
Contact:

Re: Remove Carriage Returns

Post by jacque » Sat Apr 02, 2016 8:24 pm

Sure, you should do whatever is easiest for you. Our suggested method only requires changing two characters in your existing code, and then you never have to fiddle with database exports ever again. I'd change those two characters, but if you understand your way better, go with what works.
Jacqueline Landman Gay | jacque at hyperactivesw dot com
HyperActive Software | http://www.hyperactivesw.com

bbhank
Posts: 116
Joined: Thu Mar 17, 2016 6:04 pm

Re: Remove Carriage Returns

Post by bbhank » Sat Apr 02, 2016 9:44 pm

This way there is no code to write except for the Description field cleaner and that's in javascript. The back end fix is quick, clean, and solves the issue for the main program also. It had a different but related problem with this field due to same the CRs and LFs. This way both are solved, whereas putting the code in only solves the LiveCode side.

Thank you.

bbhank
Posts: 116
Joined: Thu Mar 17, 2016 6:04 pm

Re: Remove Carriage Returns

Post by bbhank » Sat Apr 09, 2016 8:32 am

Add Tab to that list of characters. Once I did the step in the previous post there were still problems. The exported CSV file had to be opened in a word processor and using Find-Replace (Replace All), have the Tabs removed.
Result was less problems yet.

Post Reply

Return to “Getting Started with LiveCode - Complete Beginners”