Today, I'm going to show you how to take the data
from your PDF form fields in Adobe Acrobat Pro and export just the form field data into Excel
so you can track and analyze the results. Now, this is different from just how to convert a
PDF file into an Excel file. If you want to know how to do that, be sure and check out the
other video on my channel. Let's take a look. Okay, I have my file folder open here on
my desktop where I have four of the same PDF forms filled in. It's the same form, but
it's filled in by four different people. So, I've saved those out here onto a folder.
And so what I'm going to do is show you how to open this in Adobe Acrobat. I'm just
going to right-click, and we're going to say open with and select Adobe Acrobat. And
this is going to open in Adobe Acrobat Pro. So, this is the completed form, and these are the
form fields that I want to export into Excel so that I can do some further data analysis. So
what I'm going to do is go over here and click on 'Prepare a Form.' And from here, I'm going to
go up to this three-dot menu. It's an option menu that's next to 'Prepare a Form,' and just click
on that. And then I'm going to click on 'Export Data.' And this opens up where I can navigate
and save the data file. And so what I'm going to do is I'm going to go into the folder where
I have the PDF, and I'm going to create a new file folder with a subfolder within that folder,
and we'll just call this the PDF data. I'm going to double click to open that. And then, as the
file type, I'm going to come down and drop down, and I'm going to select the '.txt,' which is
the text file type, and then click on 'Save.' So now I have my new file folder set
up here. I double click in there, and I have the text Data file from that completed
form. Now I'm going to open Excel. We're going to open a new blank Excel workbook, and I'm
going to click on the 'Data' tab. Tab, and we're going to click on 'Get Data From File.'
And then we're going to click on 'From Folder.' We're going to navigate to the folder that we
want to select and click open. Now down here, we're going to drop down where it
says 'Combine,' and we're going to say 'Combine and Transform Data.'
This is going to combine the files. So we can see that these are the data fields
that have been filled in on our PDF form, and we're going to click 'Okay.' Now this is
going to open the Power Query Editor where we can make some edits to the data before we
load it onto our spreadsheet. So for example, if we don't need the source name that's just
going to name the data file, we can right-click and delete that column. And then if we have extra
columns that we don't need to keep track of like maybe the second name column, we can remove those.
And maybe we want to change the headers up here, we can double click and type in and
fill in a new name for our headers. And notice that over here our applied steps
are being tracked and recorded. And so this will be applied to every new file that gets placed
into that file folder. When we refresh our data, these steps will be applied to that automatically.
And so then when we're ready, we can click on 'Close and Load.' We can say 'Close and Load To,'
and we can say in our existing worksheet here. And then we'll click 'Okay.' And then this is going
to load our data right in here into Excel for us. Now if we go back to our file folder, let's go
back out one step. Now let's select our next form. Right-click, open with Adobe Acrobat. Go down to
'Prepare a Form,' select our three-dot options, export that data, and we're going to make sure
that it's in that PDF data subfolder. We're going to drop down the file type, select
the '.txt' file type, and click 'Save.' So now in that file folder, we have
two files. So we'll go back into Excel, and then we'll go to our data tab. We'll click
on 'Refresh,' and it will add our next form that we've dropped into that file folder. So that
this is a great way if you have a lot of forms coming in and you just want to drop each one
into that five-file folder. You can map and load the Power Query to connect to that file
folder. So any new file that gets saved there, you can refresh your data, and it will
add onto your data here that you can then analyze. So this is a great way that you
can set this up in Power Query for Excel. Now I'm going to show you a different way. If
you have a lot of forms, say you have a 100 forms that you've saved out into your file folder, um,
there's another way that you can export your data into Excel. Let's go back into Adobe Acrobat and
on the 'Prepare Form' tab, go up to our options and then click on this option that says 'Merge
Data Files into Spreadsheet.' And from here, we can select all the files that we want to
add. So we'll click on 'Add a File,' we'll go out to our folder, and we can select all of
the files that we have saved out there. So just, I hit shift and uh, clicked all of them so
you select all of them and then click 'Open.' And this will list all of the files
that you want to extract there, and then click on 'Export.' We're going
to save this out into our PDF data, and it's going to be saved as a CSV
file. We can name it whatever we want to. So maybe this is our first extract and
click 'Save.' And now we can view the file. All right, so now we got a little warning that
because this is a CSV file, there might be some data loss. So it gives us the option to save as an
Excel file. So we want to do that. Let's go ahead and save this as a file type an Excel workbook.
Now we have this in Excel, and we can modify as needed. So if we want to delete this column we
can and we can rename our header columns up here. We can bold Center do some formatting. Maybe we
can get rid of the second first name column here and rename our headers up here. So this is a
great way if you have several PDFs saved out in your file folder and you want to mass export
those into Excel directly. You can do that here, and if you need to do a couple of iterations
of that, you can also set up the Power Query to map to that file folder just like we
did with the individual text files. You would just map them to those CSV files. Be sure
and share this video with your friends and give it a thumbs up to like it. You can subscribe
to my channel and click the bell to receive notifications every time I post new videos.
Thanks for watching. I'll see you next time.
