Open PDF from listed in column A, search for text in PDF that is in adjacent cell in Column B, and return a TRUE/FALSE Value

[oreo haven]

All,
I have a problem where several invoices may have the wrong information written into them. As long as the number in Column B is inside the PDF listed in Column A, I know that it is ok.
So I would like to search the file listed in column A and see if the strin listed in the corresponding row in Column B is found in it. If so, I just need a "True" to be written to the corresponding Cell in Column C.
I have 3800 invoices to check, right now. Depending on the results, I could end up with 30k more files to check.
I do have Acrobat Pro and have enabled the libraries.
Has anyone here run into this before? The table below is what I would prefer to have in the end.

FULL_PATH TO PDF	STRING_TO_FIND	Is_It_There
c:\users\jsd\documents\invoices\18996_58720_INV767056.pdf	767056	TRUE​
c:\users\jsd\documents\invoices\18996_58728_INV767057.pdf	767057	TRUE​
c:\users\jsd\documents\invoices\18996_58736_INV767058.pdf	767058	TRUE​
c:\users\jsd\documents\invoices\18996_58744_INV767059.pdf	767059	FALSE​
c:\users\jsd\documents\invoices\18996_58752_INV767060.pdf	767060	TRUE​
c:\users\jsd\documents\invoices\18996_58760_INV767065.pdf	767065	TRUE​
c:\users\jsd\documents\invoices\18996_58768_INV767066.pdf	767066	FALSE​
c:\users\jsd\documents\invoices\18996_58776_INV767067.pdf	767067	FALSE​
c:\users\jsd\documents\invoices\18996_58784_INV767069.pdf	767069	TRUE​

 

[sandy666]

maybe

		Result
String_To_Find		Name	TBD	Exist
767056		18996_58720_INV767056.pdf	767056	767056
767057		18996_58728_INV767057.pdf	767057	767057
767058		18996_58736_INV767058.pdf	767058	767058
767059		18996_58744_INV767059.pdf	767059	767059
767060		18996_58752_INV767060.pdf	767060	767060
767065		18996_58752_INV767061.pdf	767061
767066		18996_58760_INV767065.pdf	767065	767065
767067		18996_58768_INV767066.pdf	767066	767066
767069		18996_58776_INV767067.pdf	767067	767067
		18996_58776_INV767068.pdf	767068
		18996_58784_INV767069.pdf	767069	767069

// invoices
let
    Source = Folder.Files("D:\test\oreo\invoices"),
    TSC = Table.SelectColumns(Source,{"Name"}),
    TBD = Table.AddColumn(TSC, "TBD", each Text.BetweenDelimiters([Name], "INV", ".", {0, RelativePosition.FromEnd}, 0), type text),
    Merge = Table.NestedJoin(TBD,{"TBD"},STF,{"String_To_Find"},"STF",JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn(Merge, "STF", {"String_To_Find"}, {"Exist"})
in
    Expand

// STF
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"String_To_Find", type text}})
in
    Type

you'll need to change this D:\test\oreo\invoices to your original path

oreo_pdf.xlsx

Microsoft Excel Workbook

1drv.ms

 

[oreo haven]

These two bits of code need to be inserted into other vba?

 

[oreo haven]

If a moderator sees this, can it be moved to the regular Excel Forum? I actually have no idea how I posted in BI.

 

[sandy666]

These two bits of code need to be inserted into other vba?

this is not vba but M-code for Power Query (Power Bi forum)

use Report button to ask moderator to move this thread to the Excel Questions forum

 

[oreo haven]

Trying this again. Somehow I was in the BI side of the forum and posted there, which was a miserable oversight on my part.
All,
I have a problem where several invoices may have the wrong information written into them. As long as the number in Column B is inside the PDF listed in Column A, I know that it is ok.
So I would like to search the file listed in column A and see if the string listed in the corresponding row in Column B is found in it. If so, I just need a "True" to be written to the corresponding Cell in Column C.
I have 3800 invoices to check, right now. Depending on the results, I could end up with 30k more files to check.
I do have Acrobat Pro and have enabled the libraries.
Has anyone here run into this before? The table below is what I would prefer to have in the end.
I have seen different code doing this. I'm guessing it would be best to hard code my path, and just have the filename in column A. That is easy enough to do.

path and filename	string to find	Is_It_There
c:\users\jsd\documents\invoices\18996_58720_INV767056.pdf	767056	TRUE
c:\users\jsd\documents\invoices\18996_58728_INV767057.pdf	767057	TRUE
c:\users\jsd\documents\invoices\18996_58736_INV767058.pdf	767058	TRUE
c:\users\jsd\documents\invoices\18996_58744_INV767059.pdf	767059	FALSE
c:\users\jsd\documents\invoices\18996_58752_INV767060.pdf	767060	TRUE
c:\users\jsd\documents\invoices\18996_58760_INV767065.pdf	767065	TRUE
c:\users\jsd\documents\invoices\18996_58768_INV767066.pdf	767066	FALSE
c:\users\jsd\documents\invoices\18996_58776_INV767067.pdf	767067	FALSE
c:\users\jsd\documents\invoices\18996_58784_INV767069.pdf	767069	TRUE

 

[oreo haven]

Duplicate
Because this question is in two different threads, please either delete this one and move the one I erroneously placed in the BI forum, or delete the one in the BI forum.

 

[oreo haven]

Thank you, I appreciate your time and your help.

 

[oreo haven]

Now to start reading more in depth on BI. It looks like a nice tool to add for my company.

 

[sandy666]

You are welcome

more about PowerQuery aka Get&Transform

• What is PowerQuery
• Getting started with PQ
• PowerQuery Help
• PowerQuery M reference
• PowerQuery add-in for XL 2010/2013, all above has PQ built-in
• General: Excel Help