Unanswered

Issue with number stored as text when extracting data from pdf to excel

(0) Share

Report

Posted on by Bfarrell95

Hi All

I am having an issue with my Power Automate Flow when I am Extracting data from a pdf document to an Excel sheet. I am trying to pull in some figures from the PDF into my excel table and then analyse the Max, Min and Count of the figures. I have attached a screenshot of this part of the table.

The issue that I am having is that when these figures are added into my table, they are being stored as Text and the formulae do not work until I click into the cells individually. Is there a way to fix this as I am hoping for this to be fully automated and do not want to have to click into each cell every time new data is included in the table.

I have also attached a copy of the sample area which I have been pulling the information from in the PDF. In my document automation model the figures are being extracted by their field as I have been unable to get it working as a table as I cannot figure out the code.

Number Stored As Text.PNG

Data in PDF.PNG

Thanks in advance.

Categories:

General topics

I have the same question (0)

All responses (5)

Answers (0)

wskinnermctc 6,519 Moderator on at

Like (1)

Report

You will need to get the currency from the PDF to be formatted as a number within the flow as currency or integer and then put into the excel sheet. I'm guessing the euro/dollar sign will probably be the hard part to skip over or remove from the text.
You should try to replace/remove the euro/dollar sign with a replace() expression. Then convert the text to a float integer.

I don't have the steps exactly how to do it, but you can test and use this as an example.

https://debajmecrm.com/how-to-convert-a-string-to-currency-format-in-power-automate/

Was this reply helpful? Yes No
Bfarrell95 21 on at

Like (0)

Report

Thanks for that @wskinnermctc however, unfortunately I am not able to get that working for me in the way that I want it to.

Was this reply helpful? Yes No
spmofulton 66 on at

Like (1)

Report

@Bfarrell95 so what were you not able to get working?

I suspect this might be a post-extract issue (where you need power automate to extract that data). Then you need a different power automate to transform the data. Since a good way to fix this might be power query, I have asked if its possible to automate power query using power automate.
https://powerusers.microsoft.com/t5/General-Power-Automate/Can-I-use-power-automate-on-power-query/m-p/2029285#M118949

Was this reply helpful? Yes No
Bfarrell95 21 on at

Like (0)

Report

Thanks @spmofulton @wskinnermctc

I have actually managed to fix this by changing the layout on the PDF so that the currency sign is further away from the first number and the automation tool is not picking up the currency sign.

Brian

Was this reply helpful? Yes No
spmofulton 66 on at

Like (1)

Report

Glad you were able to get things fixed. I was able to get a reply that is possible to automate power query(*), so if you get stuck again, then please don't hesitate to reach out.

(*) P.S. Because then could you solve this by creating a quick power automate script following wskinnermctc instructions.

Was this reply helpful? Yes No