OCRTEXT() vs comma ( , )

Swoopy · 02-06-2020 05:07 AM

I noticed that every text returned by OCRTEXT always has a comma (, ) at the end of it (such as "Hello, ").

I have a column (named OCR) for receiving OCRTEXT([Photo]). I also have another column to get the text from the OCR column by putting expression in the INITIAL VALUE.

CASE 1: This expression is working fine:: UPPER([OCR]) ::returns HELLO,

CASE 2: This expression is working fine:: FIND(",", "12, ") ::returns 3

CASE 3: This expression is not working as expected:: FIND(",", [OCR]) ::returns 0 (zero) instead of 6

AppSheet does not recognize the comma in the CASE 3, why?

Regards,

MultiTech

Perhaps wrap the [OCR] with TEXT()?

FIND(",", TEXT([OCR]))

Swoopy

Still returns 0, any other ideas please.

MultiTech

If you just want to remove it:

But as to why it’s not giving the expected result… @Aleksi might be able to shed some light.

(PS Aleksi: You’re my go-to person when I’m not sure who to tag, I figure you’d know the team better and who to tag. )

Swoopy

SUBSTITUTE([OCR], “,”, “”) returns Hello,

Same as the original text.

Steve

Sounds like a bug to me. Consider contacting support@appsheet.com directly for help.

Aleksi

@tony Any thoughts?

tony1

@Swoopy Does your issue reproduce in this sample app? I just tried a few images and didn’t see any commas. https://www.appsheet.com/samples/How-to-grab-the-text-from-an-image-using-OCR?appGuidString=e6b13b43...

Swoopy

The above picture is from the OCRTEXT Demo emulator. The below is mine. There is a comma (followed by a space…to be precise). This happens on both emulator view (Chrome@Windows 10) and on Android phones (at least 2 phones tested).

Note that I am using Thailand locale.

Swoopy

I think I have found another clue !

1.I have made copy of “OCRTEXT Demo”.
2.I tested on my Chrome@Windows10, there is NO comma at the end of the returned text.
3.I change type to Text and locale of its data spreadsheet file to Thailand (refresh the AppSheet editor afterward). Also, the Show option is ON.
4.Repeat step 2, there is a COMMA at the end of the returned text. Additionally, there are commas added to every space between words (or rows).

The more interesting thing is that AppSheet can not deal with this additional comma { FIND(), SUBSTITUTE(), etc. are not working with the comma in this case }.

tony1

@Swoopy Very strange. I’d like to do a controlled experiment to figure out what’s wrong here. Would you please send an email to support@appsheet.com with the following information:

The exact image file that is showing different behavior
The name of the app where you’re having the problems
Exact steps for me to recreate the problem on my end

Also, if you haven’t done this already, please go to https://www.appsheet.com/account/account#_tab_acctConfig and check the option that enables support access.

Swoopy

app name is Smart Yard

I just simply put OCRTEXT([Photo]) in INITIAL VALUE of a column named Object (which is Key column). It is not different from the sample app.

1.It adds comma and space (, ) to every text returned by OCRTEXT.

2.This comma and space are not recognized by AppSheet. They are not respond to FIND() SUBSTITUTE() but do respond to LEN(). AppSheet knows they exist but do not know what they are (the comma).

Regards,

Swoopy

For more information, the OCRTEXT([Photo]) returns

anitech, アニテック,

There are 2 commas and a space at the end in this text. But SUBSTITUTE(OCRTEXT([Photo]), “,”, “xxxxx”) returns nothing (blank).

Regards,

Fabian_Weller

@tony
Perhaps you remember that I reported this behavior to support@appsheet.com on 12 Nov 2019:

Just found a strange thing with OCRTEXT().
I have a TEXT column with initial value OCRTEXT() expression. When SHOW is enabled, it will create TEXT with comma separated values like:
1513.56.343, 1513.56.342, 1513.56.345, …
But when SHOW? is disabled, it will create LONGTEXT with newlines like:
1513.56.343
1513.56.342
1513.56.345
…
This is the same result as if I would change the column Type to LongText and enable SHOW.

@Swoopy Please try to disbale the SHOW? in your OCRTEXT column, or set it to FALSE.

Swoopy

Thanks. Also, I think I have found another clue !

1.I have made copy of “OCRTEXT Demo”.
2.I tested on my Chrome@Windows10, there is NO comma at the end of the returned text.
3.I change type to Text and locale of its data spreadsheet file to Thailand (refresh the AppSheet editor afterward). Also, the Show option is ON.
4.Repeat step 2, there is a COMMA at the end of the returned text. Additionally, there are commas added to every space between words (or rows).

The more interesting thing is that AppSheet can not deal with this additional comma { FIND(), SUBSTITUTE(), etc. are not working with the comma in this case }.

Is this a bug or something? For sure, this is not a solution to me.

Swoopy

I am about to use this feature for my Key column. So, no choice but to show it.

Fabian_Weller

I also see that you cannot use FIND() or SUBSTITUTE().
But you can use
INDEX(SPLIT([OCRTEXT],","),2)

When your [OCRTEXT] column gives you
anitech, アニテック,

Then the result of the above expression will be
アニテック

Swoopy

Unfortunately, SPLIT() in my app is not working like yours. There is no change at all.

anitech, アニテック,

However, LEN() works fine to count the extra comma. This expression

LEFT(TRIM([OCR]), LEN(TRIM([OCR]))-1)

returns

anitech, アニテック

There is no comma at the end, but the comma between 2 words is still not recognized by AppSheet.

tony1

@Swoopy Thanks, I was able to reproduce it. Seems like it has to do with the Text vs. LongText issue. I didn’t have to change the sheet locale to Thialand. Let me get back to you with a fix.

tony1

So the issue is that our Text-type input fields are replacing newlines with commas. This only affects the display of the value—the underlying value itself has the original newlines. LongText types do not do this. I’ll try to figure out why there’s this behavior difference, but for now, you can use LongText.

tony1

Actually, my bad. It will save the comma to your sheet. It’s more than just a display issue.

tony1

It looks like Text columns replace newlines with spaces in most cases. Text inputs are supposed to be a short, single line of text. However, when updated from a formula, Text inputs would replace commas with spaces—I’ve put in a change to make it so they replace commas with spaces to be consistent. If you want to preserve newlines from your OCRTEXT, you should use LongText.

Fabian_Weller

Hi @tony. I found another “bug”:

The OCRTEXT() Column is with Type “Text”.
When SHOW is enabled, it produces Text without NewLines.
When SHOW is disabled, it produces LongText with NewLines.

tony1

Thanks for raising that point. Does the problem still happen if you use LongText?

Fabian_Weller

If I use LongText, it creates LongText with NewLines. That’s okay.
But in the case I’ve described, I use Text that is handeled as LongText.