Pdf and excel automation

Help Studio
, ,
1

Hello,
I have a pdf where I need to extract “Effective Date” “Partnumber” “Total Cost”
I have extracted these data points using read pdf and regex and later according to my workflow I must place total cost value in excel row by filtering partnumbers. My output excel looks like this.

image
image1632×515 38.6 KB

How can I place the cost number for each specific partnum (from pdf) and effective date

2

@hymasharma.vemavarapu

cost value dynamic or static

3

dynamic… it changes for each partnum

image
image1033×635 84.2 KB

4

Hi @hymasharma.vemavarapu ,

Could you let us know how is the extraction done ? What was the regex used ?

Do you have the Output after applying regex in the form of a Datatable ?

5

I must enter the total cost in 04/01/2023 column of excel … i highlighted in the above screenshot

6

does your workflow have those values? or yet to get those?

7

yes i got output in data table.

8

I didnt get that. can you please elaborate

9

Hi @hymasharma.vemavarapu ,

Could you check the below workflow :
Excel_Update_SpecificRows.zip (10.9 KB)

  1. We have a Regex Datatable / Excel data, where you have extracted the values and the Part Number and the Total Price are co-related. In your case we have this data already as a Datatable, say RegexDT.

  2. Next, we would have to read the Excel sheet data where we need to update the values. Get it as a Datatable, let’s Say Sheet2DT.

  3. Now we can loop through the RegexDT using a For Each Row activity and inside it we can use the LookUp Datatable activity to get the row index of the matching part number value present in the Sheet2DT. The row index variable name be rowIndex.

  4. Next, we can Check if the rowIndex value = -1, if so skip the update, meaning that the Part Number is not present in the Sheet2DT. If not -1, then we can update the Sheet2DT row value using the below Assign statement :

Sheet2DT.rows(rowIndex)("04-01-2023") = CurrentRow("Total Price").ToString
  1. At last, update back the Datatable to the Excel sheet, by writing the datatable without Headers and starting the Range from A2.

Check the workflow and let us know if you were able to understand the approach.

10

Hi…thank you so much… i will try this now and will let you know

1 Like
11

@hymasharma.vemavarapu

Another way…We can directly write on excel as well using below method

  1. Use find/replace and search for partnumber in column A and store it to varpartcell
  2. Use find/replace and then search for date you need in first row and save the output cell in vardatecell
  3. Use write cell activity to write the data and give the cell number as followSystem.Text.RegularExpressions.Regex.Match(vardatecell,"\w+).Value + System.Text.RegularExpressions.Regex.Match(varpartcell,"\d+).Value

I am extracting row number from part and column from date variables

Hope this helps

Cheers