Text separation

expressions
(TalkNet Apps) #1

I need to separate a string of scanned text, in several columns.
Specifically, the barcode of the identity document.
When scanning it, it is presented in the following format:

Procedure N Β° @ Surname @ Names @ Sex @ DNI @ Exemplary @ Date Nac @ Date Emission

and I want to separate it in the corresponding fields. Find() only finds me the first match. Is there an expression that fulfills this function?
Thank you very much already

(Levent Kulacoglu) #2

@TalkNet_TechnoStore
You can try this expression:

SPLIT([_THISROW].[ColumnName],"@")
1 Like
(TalkNet Apps) #3

That expression results in β€œnull”, but gave me the idea to do the following:

Index (List (SUBSTITUTE ([DNI], β€œ@”, β€œ,”)), 1)
Index (List (SUBSTITUTE ([DNI], β€œ@”, β€œ,”)), 2) …

That is, I replace the @ with the comma and create a list of values, and in each field I point to the index in the list.

The expression assistant takes the expression as valid, but it does not work. Even the field disappears in the form.

Some help?

(Levent Kulacoglu) #4

@TalkNet_TechnoStore
You don’t need to substitute the β€œ@” char with comma and use the LIST expression either. SPLIT expression will already convert it into a list and you can read the values with INDEX.

INDEX(SPLIT([_THISROW].[ColumnName],"@"),1)

should work. I have tested and it works as expected:

(TalkNet Apps) #5

This is the scanned text
00355196384@POU@ALFREDO FABIAN@M@17825572@A@25/08/1967@30/03/2015@200

and this is the expression that I put in App Formula
INDEX(SPLIT([_THISROW].[DNI],"@"),4)

The result is null and it does not show me the form fields, what can I be doing wrong? The type of field is Text

(Levent Kulacoglu) #6

@TalkNet_TechnoStore
Can you try with setting your column type as LongText and try with removing the [_THISROW]. and use only [DNI]

1 Like
(Alfredo Pou) #7

It worked! Taking out [_THISROW]
Thank you

1 Like
(Alfredo Pou) #8

I have another problem
If I point to the date of birth:
INDEX (SPLIT ([DNI], β€œ@”), 8)
It correctly returns the date in text mode. If I want to calculate the age I do:
HOUR (TODAY () - DATE (INDEX (SPLIT ([DNI], β€œ@”), 8)) / (365.24 * 24))
The problem is that DATE (INDEX (SPLIT ([DNI], β€œ@”), 8))
It returns empty.
I understand that DATE () converts text to date format.
What am I doing wrong?

(Steve Coile) #9

The textual date must be in a form AppSheet recognizes. A date like 25/08/1967 (DD/MM/YYYY) likely needs to be expressed as 08/25/1967 (MM/DD/YYYY).

(Alfredo Pou) #10

It is true!
Is there any way to convert it or should I cut the texts (LEFT, RIGHT, MID) or INDEX() and sort them?

(Steve Coile) #11

I’m not aware of a way to convert automatically.

(Alfredo Pou) #12

It works now!
This is how the expression used to obtain the date of birth in date format:

Date (INDEX (SPLIT (IF (COUNT (SPLIT ([ScanDNI], β€œ@”)) <10,
INDEX (SPLIT ([ScanDNI], β€œ@”), 7),
INDEX (SPLIT ([ScanDNI], β€œ@”), 8)), β€œ/”), 2) & β€œ/” & INDEX (SPLIT (IF (COUNT (SPLIT ([ScanDNI], β€œ@”)) <10,
INDEX (SPLIT ([ScanDNI], β€œ@”), 7),
INDEX (SPLIT ([ScanDNI], β€œ@”), 8)), β€œ/”), 1) & β€œ/” & INDEX (SPLIT (IF (COUNT (SPLIT ([ScanDNI], β€œ@”)) <10,
INDEX (SPLIT ([ScanDNI], β€œ@”), 7),
INDEX (SPLIT ([ScanDNI], β€œ@”), 8)), β€œ/”), 3))

(Steve Coile) #13

BTW, performing a SPLIT() repeatedly on the same data to extract individual fields is incredibly inefficient. In your particular use case, the inefficiency likely won’t affect you at all, but it’s generally a good idea to make efficient design a habit and pursue it whether strictly needed it not.

It would be more efficient to perform the SPLIT() once, capture the results in a column, and use INDEX() against that column where you need the individual values.

(TalkNet Apps) #14

@Steve
Thank you very much for the advice, I did it according to your instructions.
Now I have a new string, and the delimiter is the line feed, instead of the @.
What would ASCII be?
I tried Alt 10 and Alt 13, but it does not work.
The string is the following:

DNI

95141384
M
NESTOR RODRIGO
ABAN VARGAS
19-10-1990
BOLIVIA
BLANCO ENCALADA
2570

1

VILLA CELINA
1772
6-4-2016
4-2-2020
A.2.1,B.1
A -
552735428

(Steve Coile) #15

Try:

SPLIT([DNI], "
")

Note the separator is a newline, typed as "-Enter-".

(TalkNet Apps) #16

It does not work. Any other suggestions?

(Steve Coile) #17

Hmm… It works fine for me. Please cut and paste the exact expression you used.

(TalkNet Apps) #18

This is the string:

DNI
95141384
M
NESTOR RODRIGO
ABAN VARGAS
19-10-1990
BOLIVIA
BLANCO ENCALADA
2570

1

VILLA CELINA
1772
6-4-2016
4-2-2020
A.2.1,B.1
A -
552735428

(Steve Coile) #19

The expression, the formula.

(TalkNet Apps) #20

Sorry

INDEX(SPLIT([ScanDNI],"
"),4)