Using dynamic tools to pick up pdf file names and use as sheet name in excel file

I've done up a pdf extraction macro using to parse the data in the pdf files in a directory and output the results in excel. Currently, the extracted data of all files sit on one excel sheet.

I would like for each file's data to be output to separate sheet on the excel output file by using the pdf file name. Is there any way this can be done using the dynamic tools or any other tools in Alteryx to achieve this?

I've attached my macro workflow here.

Any assistance on this would be much appreciated.

Read PDF Image batch.yxmc

Batch Macro

Dynamic Processing

Accepted answers

ganesamurthy

This Macro should not be batch, it should be standard macro. I am attaching the workflow that you need to acheive your result.

Hope it works well.

Batch pdf extraction.yxzp

All comments

CarlDi

Hi @LFLee

I tried running your macro but it error'd out. Do you have a column that maps the PDF file's name? If so, in the Output data tool, click on the 'Take File/Table Name From Field' checkbox. In The dropdown below, select 'Change File/Table Name' and in the 2nd dropdown, select the column with PDF filenames. See the screenshot below:

Hope that helps!

LFLee

Hi @CarlDi , unfortunately there is no column for the PDF file's name. I've attached the output below for your reference on how it looks like, hence, was hoping that there is a way to pick up the input file name from the selected file. I've also attached the work flow again that contains the batch macro.

Any assistance on this would be much appreciated.

test2.xlsx

Batch pdf extraction.yxmd

ganesamurthy

cond.install <- function(package.name){
options(repos = "http://cran.rstudio.com") #set repo
#check for package in library, if package is missing install
if(package.name%in%rownames(installed.packages())==FALSE) {
install.packages(package.name)}else{require(package.name, character.only = TRUE)}}

cond.install("pdftools")
cond.install("tesseract")

files <- list.files(path="C:\\Users\\ganes\\Desktop\\Training\\AlterYX\\Alteryx Solution\\PDF Read Batch Macro", pattern="*.pdf", full.names=TRUE, recursive=FALSE)
for (file in files){
pngfile <- pdftools::pdf_convert(file,dpi = 200)
text <- tesseract::ocr(pngfile)
x <- data.frame(text,file)
write.Alteryx(x,1)

Use this additional information in the output of your R code, then you will get the full path of the file name. From this you shall extract the Filename that you can use in Output Data tool to write into separate sheet, as suggested.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703