Page 4 of 4

Re: Remove Carriage Returns

Posted: Sat Apr 02, 2016 6:52 pm
by richmond62
As far as I recall there are a number of chars that effect a RETURN signal in the unicode lexicon:



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?

Re: Remove Carriage Returns

Posted: Sat Apr 02, 2016 7:04 pm
by bbhank
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.


Re: Remove Carriage Returns

Posted: Sat Apr 02, 2016 7:27 pm
by jacque
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.

Re: Remove Carriage Returns

Posted: Sat Apr 02, 2016 7:58 pm
by bbhank
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:

Re: Remove Carriage Returns

Posted: Sat Apr 02, 2016 8:24 pm
by jacque
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.

Re: Remove Carriage Returns

Posted: Sat Apr 02, 2016 9:44 pm
by bbhank
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.

Re: Remove Carriage Returns

Posted: Sat Apr 09, 2016 8:32 am
by bbhank
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.