Remove Carriage Returns
Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller
Re: Remove Carriage Returns
Yes, I understand how fields are delimited. The data coming from the database is fine except for the Description field. This is the only field that needs changing.
Re: Remove Carriage Returns
Seems like this should work:
put pDataArray["label 11"] into dText
replace CRLF with "---" in dText
set the text of field "Description" of me to dText
It doesn't.
Here's possibly, why. LiveCode can't see the line feed and carriage return characters in the field - Only in the record itself.
If these characters could be made visible and usable to LiveCode the field might be able to be cleaned. Been looking all over for some info on making all characters in a single field visible in LiveCode.
put pDataArray["label 11"] into dText
replace CRLF with "---" in dText
set the text of field "Description" of me to dText
It doesn't.
Here's possibly, why. LiveCode can't see the line feed and carriage return characters in the field - Only in the record itself.
If these characters could be made visible and usable to LiveCode the field might be able to be cleaned. Been looking all over for some info on making all characters in a single field visible in LiveCode.
Last edited by bbhank on Fri Apr 01, 2016 4:00 pm, edited 4 times in total.
Re: Remove Carriage Returns
REPLACE(event_desc, char(13) + char(10), '---')
This is posted as a way to remove these characters in several different SQL forums and is said to consistently work. There were a lot of forums using this who say it works. Tried to get this to work in LiveCode. The "apply" button says there are no errors but when run it returns errors no matter where it's used. LiveCode forums don't mention it or show any to do this in a field instead of in a whole record. A method to clean a single field of these characters is needed.
This is posted as a way to remove these characters in several different SQL forums and is said to consistently work. There were a lot of forums using this who say it works. Tried to get this to work in LiveCode. The "apply" button says there are no errors but when run it returns errors no matter where it's used. LiveCode forums don't mention it or show any to do this in a field instead of in a whole record. A method to clean a single field of these characters is needed.
Re: Remove Carriage Returns
Hi bbhank,
You need to prevent this much earlier, this already happens when you convert the database data to an array!
Looks like you did not really understand your problem so far, please read up my long answer again.
If in doubt, always consult the dictionary!
Best
Klaus
Nope! At this point there is no more CR in that field or array key!bbhank wrote:Seems like this should work:
put pDataArray["label 11"] into dText
replace CRLF with "---" in dText
set the text of field "Description" of me to dText
It doesn't.
Here's possibly, why. LiveCode can't see the line feed and carriage return characters in the field - Only in the record itself.
You need to prevent this much earlier, this already happens when you convert the database data to an array!
Looks like you did not really understand your problem so far, please read up my long answer again.
Is this a SQL command? At least this is no valid LC syntax.bbhank wrote: REPLACE(event_desc, char(13) + char(10), '---')
If in doubt, always consult the dictionary!
Best
Klaus
Re: Remove Carriage Returns
Looks like you did not really understand your problem so far, please read up my long answer again.
I DO UNDERSTAND!!
You all keep telling me how to change the query and I'm asking how to change the data in one of the fields returned from the query. The original question specifically asked about the Description field, not the whole result.
With that posted query, I know where the query came from and that it's SQL. I'm looking for solutions on this end too.
Please re-read the original question.
I DO UNDERSTAND!!
You all keep telling me how to change the query and I'm asking how to change the data in one of the fields returned from the query. The original question specifically asked about the Description field, not the whole result.
With that posted query, I know where the query came from and that it's SQL. I'm looking for solutions on this end too.
Please re-read the original question.
Re: Remove Carriage Returns
No, you don't!bbhank wrote:Looks like you did not really understand your problem so far, please read up my long answer again.
I DO UNDERSTAND!
It is a matter of ORDER here!bbhank wrote:You all keep telling me how to change the query and I'm asking how to change the data in one of the fields returned from the query. The original question specifically asked about the Description field, not the whole result.
1. you need to FIRST fetch the data from the database and
2. THEN you can change the content of the query and then
3. convert the data to an array for use in a datagrid.
But for getting the correct data back, especially for your "Description" field, you need to adjust the SQL query,
by using an other RECORD delimiter than CR!
You query presumes that all of the content contain ONE-liners, which your "problem" field does not!
At this point:
Code: Select all
...
put pDataArray["label 11"] into dText
...
so you need to take care of this earlier.
And earlier means a combination of an adjusted query and conversion of the db data to datagrid array.
Best
Klaus
Re: Remove Carriage Returns
That's what I've been asking all along. pDataArray["label 11"] already contains "corrupted" data (only the first LINE of the actual db field "Description") - I was already aware of how this works and this is why I asked the question about that specific field and not about the whole query in the original post.
so you need to take care of this earlier. Where? How?
There's no good, solid information on how to do this readily available on the web. This is a common problem and I found several solutions that work on other systems. So far, none of the instructions found work in LiveCode, and LiveCode, so far, doesn't have an answer.
so you need to take care of this earlier. Where? How?
There's no good, solid information on how to do this readily available on the web. This is a common problem and I found several solutions that work on other systems. So far, none of the instructions found work in LiveCode, and LiveCode, so far, doesn't have an answer.
Re: Remove Carriage Returns
Hi bbhank,
One query is valid for all fetched data in this query.
Use the query that AxWald suggested and take the differnt FIELD and RECORD delimiters into account when you convert
the data from the quety to an array for us in the datagrid.
If in doubt, please post your appropriate handler here and we will adjust it to your needs.
There are actually 2 ways how to deal with/solve this problem:
1. Make sure that the data in the database are entered correctly!
The ideal case, but we probably do not have any influence on that.
2. Take this possible inconvenience into account and act/query accordingly, e.g. as described here.
Best
Klaus
and we have been trying to explain this to you all alongbbhank wrote:That's what I've been asking all along.
If you want to use only ONE (universal) query, then it is not possible to fetch the data in different ways!bbhank wrote:pDataArray["label 11"] already contains "corrupted" data (only the first LINE of the actual db field "Description") -
I was already aware of how this works and this is why I asked the question about that specific field and not about the whole query in the original post.
One query is valid for all fetched data in this query.
Already (tried to) explained this a couple of times:bbhank wrote:so you need to take care of this earlier. Where? How?
Use the query that AxWald suggested and take the differnt FIELD and RECORD delimiters into account when you convert
the data from the quety to an array for us in the datagrid.
If in doubt, please post your appropriate handler here and we will adjust it to your needs.
Maybe, but that is neither my nor LCs faultbbhank wrote:There's no good, solid information on how to do this readily available on the web.
There are actually 2 ways how to deal with/solve this problem:
1. Make sure that the data in the database are entered correctly!
The ideal case, but we probably do not have any influence on that.
2. Take this possible inconvenience into account and act/query accordingly, e.g. as described here.
Best
Klaus
Re: Remove Carriage Returns
You got it!
Here's what I'm thinking about. Export the database. Clean the Description field. Re-import. That'll fix what's there. All new entries have to go through the admin process anyway for approval. Now,thanks to you folks, that I know way more about what's happening, I can manually clean that field on each entry until the main program can be modified to do that. It's given us fits in other uses too.
And thank you all for putting up with my succinctness.
Tried it and it worked.
I exported the table. The carriage returns and line feeds were taken out on export.
Then the table truncated - All the records taken out.
The table was re-imported without carriage returns and line feeds.
Here's what I'm thinking about. Export the database. Clean the Description field. Re-import. That'll fix what's there. All new entries have to go through the admin process anyway for approval. Now,thanks to you folks, that I know way more about what's happening, I can manually clean that field on each entry until the main program can be modified to do that. It's given us fits in other uses too.
And thank you all for putting up with my succinctness.
Tried it and it worked.
I exported the table. The carriage returns and line feeds were taken out on export.
Then the table truncated - All the records taken out.
The table was re-imported without carriage returns and line feeds.
-
- VIP Livecode Opensource Backer
- Posts: 3901
- Joined: Sat Mar 24, 2007 2:54 am
- Location: Palo Alto
Re: Remove Carriage Returns
PHEW!!!!
This was like a bad romance story.
Simon
This was like a bad romance story.
Simon
I used to be a newbie but then I learned how to spell teh correctly and now I'm a noob!
-
- Livecode Opensource Backer
- Posts: 9287
- Joined: Fri Feb 19, 2010 10:17 am
- Location: Bulgaria
Re: Remove Carriage Returns
No need to get stroppy: the contributors to this forum do it for LOVE, so a little bit of patience might not be a bad thing.Still waiting for my last question to be answered, so much for "Succinct answers...".
Special "Moronic" method.
- Attachments
-
- C Ret remover.livecode.zip
- Have fun with the stack.
- (9.52 KiB) Downloaded 199 times
Re: Remove Carriage Returns
Hi Richmond,
Best
Klaus
you obviously did not read the whole thread.richmond62 wrote:No need to get stroppy: the contributors to this forum do it for LOVE, so a little bit of patience might not be a bad thing.Still waiting for my last question to be answered, so much for "Succinct answers...".
And you also completely missed the point of it!richmond62 wrote:Special "Moronic" method.
Best
Klaus
-
- Livecode Opensource Backer
- Posts: 9287
- Joined: Fri Feb 19, 2010 10:17 am
- Location: Bulgaria
Re: Remove Carriage Returns
Surely, as in the method I outlined, if the textString is stripped of its carriage returns (and replaced with a SPACE if you want to), the things shouldn't start choppingThe problem is how to keep the whole contents in one field so that the returns don't cause it to go to the next Description field.
itself up, as the RETURNS have been removed?
Re: Remove Carriage Returns
The real problem here was NOT to remove CRs from a field in Livecode!