Split Tab-Delimited Text From Scanned QR code

[Ponch]

I have QR codes on shop floor documents that contains entries for Item Code, Julian Code, Best Before Date and Production Date.

Each of these entries is delimited by a tab (Ascii code 9), for example CODE010 0109 18/04/2021 18/04/2020.

My question is - how do I split out each entry, so that I can put them into their own fields?

I’ve tried playing with the SPLIT function but cannot seem to find out how to identify the tab (for example, in Excel, if the above was in cell A2, I’d use =MID(A2,1,FIND(CHAR(9)-1,A2,1)) to get CODE010.

Many thanks for your time

[Suvrutt_Gurjar]

Hi @Ponch ,

Does the following AppSheet expression help?

INDEX(SPLIT(TEXT(“CODE010 0109 18/04/2021 18/04/2020”)," "),1)

OR

INDEX(SPLIT(TEXT([QR Code])," “),1) where [QR Code] is the field that contains the string"CODE010 0109 18/04/2021 18/04/2020”

[Ponch]

Hi @Suvrutt_Gurjar

Many thanks for taking the time to look at my question, it’s very much appreciated.

The solutions you offered do split out the CODE010 from the QR scan because it’s looking for, and found, an empty string between it and the next entry, being 0109.

However what I’m after is splitting the whole string based on each hidden TAB characters (the Ascii character 9 found in Excel using CHAR(9)) that have been encoded in the QR code between each of the 4 entries, and then putting each of the 4 elements of the encoded string into their own fields.

The actual value in the scanned QR code (copied from Excel) is:

“CODE010 0109 43939 44304”

and I want to split out each of the 4 elements into their own fields. Please accept my apologies for not putting it in my original question in this format.

Just for information there are unfortunately sometimes empty spaces within the Item Codes themselves (ZZZ-REC T909 - 1KG is one example) and so looking for just an empty string would unfortunately split up the Item Code for that type of Item Code.

Thanks

[Suvrutt_Gurjar]

Hi @Ponch,

Thank you for the details. I now got what you are looking for. I would like to mention at the outset that I am unsure if I can suggest a 100% correct solution. However I am willing to work to try a couple of more things with you if you also find it OK.

Could you also please add if in the extracted string “CODE010 0109 43939 44304” the number of digits in the highlighted part always remain constant?

Also if so, do the following two expressions extract the two dates parts?

1. RIGHT(TEXT(“CODE010 0109 43939 44304”),5)
   and
2. INDEX(SPLIT(RIGHT(TEXT(“CODE010 0109 43939 44304”),11)," "),1)

Also as an alternate case scenario, is it possible that you continue to use the spreadsheet based solution and simply display the extracted values in the app?

[Ponch]

Hi @Suvrutt_Gurjar

Thankyou again for your help.

Your 2 formulas do work if the CODE010 0109 18/04/2021 18/04/2020 is just a string separated by a " " but when used against the scanned QR code containing the Chr(9) tab characters they fail.

Yes, the number of digits in the highlighted part always remain constant

The app I’m looking to build will be used for stock takes in our warehouses and the four elements of Item Code, Julian Code, Best Before Date & Production Date provide us with traceability information for use in other applications, and the Item Code will also be used to lookup the Description and expected quantity of the item. So in short, they’ll scan a label/document attached to the physical product in the warehouse using their phone and this will populate the 4 fields mentioned above on their screen, along with the related description and expected quantity, so I do need to try to make the app work.

I’m extremely grateful for the help you’ve provided and do understand that what I wish to achieve may not be possible with AppSheet.

The actual QR code is reproduced below:

Many thanks

[Suvrutt_Gurjar]

Hi @Ponch

Thank you. I was able to download the string from the QR code shared by you. Then in the back end data base ,I copied the tab equivalent space from the string and pasted it in the split expressions.

My testing went through correctly. I tested with virtual columns each of the below expressions though.

So , I suggest, please copy each of the expression below as it is and paste it in your app, one for each different split column. Only please replace the [ScanQR] column name each expression that I used in my testing with your column name that has scanned QR code string.

For Code :
INDEX(SPLIT(TEXT([ScanQR])," "),1)

For Julian Code
INDEX(SPLIT(TEXT([ScanQR])," "),2)

For Best Before Date

INDEX(SPLIT(TEXT([ScanQR])," "),3)

Production Date
INDEX(SPLIT(TEXT([ScanQR])," "),4)

Please ensure the column that store the scaneed QR string in the backend is “plain text” type.

My test results in the app display proper splits. Please refer app table view image below.

In case the copied expressions as above do not work for you, I suggest, please copy the tab equivalent space from the bak end database save string and put it in expressions.

Hope this helps.

[Ronald_Arturo_V]

Hola @Suvrutt_Gurjar 

Muy buena respuesta, yo también tengo la necesidad de @Steve  pero con tu respuesta me quedo totalmente claro y puedo continuar con mi APP sin problemas 

[Steve]

SPLIT([QR Code], " ") , where the apparent space between the quotes is actually a tab character, seems to work for me. I suspect your problem isn’t so much AppSheet as it is your keyboard.

[Suvrutt_Gurjar]

Hi @Steve,

You are right. All this while , I was testing the string in the app and typing the above response, so did not see your response.

My testing also showed tab space works with SPLIT() expression.

[Ponch]

Hi @Suvrutt_Gurjar, @Steve

Thankyou both very much for your time, help & patience with this here AppSheet newbie.

It’s working perfectly and I’m very grateful indeed.

Once again, thanks!