Match data between excel file and text file

datatable
excel
uiautomation
text
activities
#1

hello,

please I need your help ?

I have an excel file that contain postal code - adress - city
the goal is to search into a text file if there is a match between postal code in excel file and postal code in the text file and after display all the row (postal code selected - adress - city)
N.B : the text file look like this :

departement informatique ENVOI Mail
Pour le compte de :
abc COMMANDE DF 904300 LT 23/05/2018
Pointe des Sables
97200 FORT DE FRANCE Martinique
EXP URGENTE Av le 28/05/2018
FACTURATION : departement

97200 is the postal code

#2

@ifranity Read the text file, store the o/p into string variable. get the value from excel file if check whether string variable of text file contains postal code value.

TextStringVariable.Contains(excelpostalcodevariable)

if it is true
display all the excel rows which contains that value.

#3

Hi,
Thank you for your response, I’m gonna test this and I let you know.

#4

Hi @manjuNatha i’ve tested but no result

#5

@ifranity can u share ur workflow?

#6

@ifranity Is it possible to share postal code excel file?

#7

fichier.xlsx (8.1 KB)
Thanks to find attach the excel file.

#8

the robot have to look into the text file
if he find the value coressponding to the column postal code,
display the row contains the value.

thank you,
@Madhavi

#9

Any Help please :slight_smile:

@rpasolution @RPA @excelunlocke @rpadev

#10

@ifranity can u share ur workflow?

#11

thank you for your response, can I give you the text file and the excel file and try to help me to do it

because when i’ve folowed ur steps I found that TextStringVariable.Contains(excelpostalcodevariable) got error because the :
TextStringVariable is a string
excelpostalcodevariable is a dataTable
and the error there is no contains in the datatable.

please find attach the excel and txt files :
fichier.xlsx (8.1 KB)

and the text file contain :

departement informatique ENVOI Mail
Pour le compte de :
abc COMMANDE DF 904300 LT 23/05/2018
Pointe des Sables
97200 FORT DE FRANCE Martinique
EXP URGENTE Av le 28/05/2018
FACTURATION : departement

#12

@ifranity Check workflow below.

exp4.xaml (15.0 KB)

#13

Hello,
I would like to thank @Manjuts90 for his kindness, and answers given.
We can easily find what we are looking for. I hope I can do the same for others.
Congratulations for this forum.

#14

@Manjuts90 please how can I display the result in an excel file in orther to get the value of cells into SAP

#15

@ifranity Below file write data in excel File.

exp4.xaml (15.1 KB)

#16

Great job, that work , thanks a lot :slight_smile: :slight_smile: :slight_smile: :slight_smile: :slight_smile: :slight_smile:

#17

Hi @Manjuts90 ,
Please I need ur Help again , this time I have a file contains numbers and don’t able to filter data in orther to display postal code : please find below the text file :

NOUVEAU FAX RECU 4
DATE HEURE RECEPTION IDENTIFIANT DISTANT DUREE PAGES ETAT
03 Mars 2017 14:42:01 UTC+01 :00 CERP SIPR 005 4 Nouveau
2017/07/12 14:13 :14 1 /4
CONFRATERNELLE D’EXP‘LOITA’HON DE INFORMATIQUEDE LA REUNION
… 1 - Société par Actions Simpli?ée au capital de 407 000 Euros
1‘ . Siége Social : 12, rue de l’arc- BF23- 97200 CEDEX
the postal code is : 97200
thanks again,

#18

Hi @ifranity

Could you provide more examples of the bit with the postal code?
You can use Regex to extract it from the above string, but it really depends on the amount of variations.

See this example Regex:
\d{5}
The full formula would look like this:
System.Text.RegularExpressions.Regex.Match(inputString,"\d{5}").ToString

It will find any 5 digits next to each other. For the string you provided, it only finds your postal code. However, if it happens that the string contains other 5 digits, it will return them as well.
You could naturally make the regex more robust by checking what is in front and after :slight_smile:

#19

Hi, @loginerror

#20

The scenario that Ihave is to extract from Invoice.pdf an adress of the client and after use the postal code or the city to type this into SAP,

I have a excel file wich contain the postal code - city - adress of client
the main problem is that I have different invoice so I have to extract that into text file and after search for postal code or city and look if it match with the excel file (clients.xlsx) and display the row in another excel file in orther to extract cells for SAP.

please find below the excel file clientsClasseur1.xlsx (23.0 KB)