Read digits in PDF + Cross reference in Excel + Copy & Rename file

Help Studio
1

Hi All - I am a beginner so thank you for being patient. I attached for you two documents to make things clearer of what I need to make this work.

I need the automation to find the digits (in the form of: 8digits_4digits) in the SamplePDF.pdf (203.8 KB) and then cross reference the excel ExcelData.xlsx (24.4 KB) by using the number obtained which will be located in column name “P-ID”, to get the number from column name “X-ID”. Then it should create a new copy of the file SamplePDF in a new folder and rename it with that number.

To clarify: In this example, the SamplePDF shows the number 20220101_1234

image
image825×241 15.1 KB

From there we should get the number 12345 under column “X-ID” by cross referencing from column “P-ID”

image

Then copy the file into a new folder with the name of what was extracted from the excel which will be “12345” in this case.

I would much appreciate it if you walk me through all the steps to reach my desired output please by showing me how to actually create the workflow if that is possible

Thanks in advance

2

1.Read SamplePDF.pdf and get the output as text

2.In matches activity Use the below regex expression to identify P-ID

image
image859×478 47.7 KB

3.Read ExcelData to convert it to datatable and filter the datatable (use filter datatable acivity) with the output from step2 to get X-ID

4.use copy file activity and mention the X-ID obtained from step3 in the destination path.

3

Hi Lak,

I get the error that an expression is expected when I paste that code in matches. What am I doing wrong? Thank you

image
image1497×730 37.6 KB

4

Excelop.xaml (7.3 KB)
Please find the attached file and let me know if it helps

5

Lak this is perfect THANK YOUU but I am encountering a problem when I changed the name of the column in the “Write Line”

What does this error mean?

image

6

is the datatable empty? have you changed the filter condition ? this issue happens when the datatable is empty

7

All I did was change the excel file to add the correct format with more columns (I have attached the same format with the actual column headings for you to see SampleExcel1.xlsx (24.7 KB). Then I have changed in the Write Line the column name to “SympaID” which acts as the P-ID in the other excel.

8

I think the problem is because of dt(0) - does that mean it looks only on the first number in the column? Because I need it to search for it in the whole column.

9

I just added for checking the value. I am not sure about the issue you are facing. This issue is not happening for me
Excelop.xaml (7.5 KB)

10

Hi @amenoufy ,

Did this work for you?

11

Hi Lak - you are a genius thank you!!!

1 Like
12

Sorry two last questions please I am sorry:

  1. how do I make it repeat for many files within a folder “FolderA”
  2. then save it to another folder? “FolderB”
13

Hi Lak I found out how to make it each document in a folder but I dont know how to save it in another folder could you please help only in that: What should I add here?

image
image710×316 7.77 KB

14

in the destination folder specify full path where you want to save the pdf

15

Sorry but I dont know what to write. Lets say i want to save it in a folder called “FinalDoc” in the same directory. What do I put in the “To” before “Employment agreement”:

image
image710×316 7.77 KB

16

if its in c folder c:/FinalDoc/Employment agreement…

17

Did it work?

18

I used rename file before the copy file sequence. Lak I am very grateful for all your help really thank you for everything.

19

Please mark as solution if this helped you in resolving the issue

20

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.