Export Word Form Data to Excel | Extract Form Data From Multiple Word Documents Automatically

so you want to extract the data that is stored in a Microsoft Word form and import it into Microsoft Excel and we want to do this for multiple forms as we receive new forms we want Excel to automatically pick up that new data now the way we're going to do this is that we're going to put all the forms that we receive into a particular folder as we add new forms Excel will automatically extract the data from those new forms so you can see I already have a folder set up and I've got forms from Chester tugwell myself Greg McGregor and Sue Kemp and the interesting thing about these forms is that they're actually text files so I need to show you how to export your forms as text files so let's start by looking at the form that I've already created to work with forms you really need to show the developer tab you may already have that visible on your Ribbon but just in case you haven't right click on one of the other tabs customize the ribbon tick this option here developer and the form controls I've used here are the Legacy form controls so essentially I've used this option here text form field the exception is this one here this is a drop down list and for that I use this option drop down form field now once you've got your form set up you need to go to the restrict editing button and section two here editing restrictions tick this box allow only this type of editing in the document and select filling in forms and then you need to start enforcing protection and put in a password if you want so I'll complete another form now this form must be saved in a particular way for this method to work what you do is you go to file options and then to Advanced scroll down until you see a section called preserve Fidelity when sharing this document and you tick this option here say form data as delimited text file click on OK now what that'll mean is when I go to file save it will automatically save the file as a plain text file so I'm going to give file a name and then click on Save you can see that it is going to export the data as a plain text file and if I click on OK it creates that text file in this folder if I opened up that text file you can see the data there so all these text files have the data laid out in the same way and stored in the same way so you all have to make a decision in terms of how you're going to distribute your form are you going to distribute it with that option ticked so as a reminder that's file options advanced scroll down preserve Fidelity when sharing this document and I've ticked that option there if that is ticked whenever the end user saves their form it will save it as a text file if you don't have that text you're going to receive the forms back as Word documents and you'll have to go into each individual form and tick that option before you save so how do I get that form data into Excel well I'll open up Excel so I've got a brand new spreadsheet and I'm going to go to the data tab on my ribbon I'm going to get data from file from folder I'm now going to navigate to the folder that I'm saving those forms in and once I've selected that folder I just click on open and Excel is now going to list all of the files in that folder so what I need to do is go down to transform data at the bottom here and I only want to import the text files so a file with an extension.txt so in the extension column I'm going to untick select all and then tick text click on OK then what I can do is Select this content column go up to remove columns remove other columns and then click on this button combine files just giving me a preview of the first file in that folder that looks fine click on OK and you can see that it's extracted the data from the four text files in that folder and what I need to do is create names for these columns so to rename a column double click where you've got the existing column name and just type in your new column name you can do that for all the columns now you can see in the mobile column the leading zeros on these telephone numbers have been deleted and the way to resolve that is to go to this applied steps list and you can see there is a step there called change type if you delete that and confirm your deletion there you can see that it resolves that problem for you now you might then want to specify the data type stored in each of these columns and you can do that by clicking on this little icon here where it's got abc123 if you click on the button and choose text there text there text there this is also text this is text this is text so with your data you may have numeric fields in which case choose whole number decimal number or currency for example so once we've set up the data and by the way you'll only need to do this once go to close a load up here top left and it will import the data into Excel now if I go back to word and complete another form so this time we have Mr Keith lemon email Keith at email.com telephone number and he heard about us through an ad in the paper so I'll go to file save it's automatically going to save it as a plain text file so this is Keith lemon and it's been saved in the same folder as the other forms click on OK here so back to excel now all I have to do I don't have to go back through those steps that we looked at before we've done that and we only need to do it once all I need to do is on the data tab click on this refresh button and you can see it brings through Keith's record into the Excel spreadsheet okay that's all I wanted to cover in this particular video hopefully that's useful if it is please give me a thumbs up and subscribe and I'll see you next video [Music] thank you

Channel: Chester Tugwell

Views: 17,460

Keywords: extract data from multiple word documents to excel, how to transfer data from word to excel automatically, extract specific data from word to excel, extract data from word fillable form, how do i link a form to an excel spreadsheet, how to link word file to excel sheet, how to link form to excel worksheet, link word form to excel spreadsheet, how to export word form data fields to excel, how to export data from word to excel, exporting word form data to excel

Id: QS554vB5gKI

Length: 8min 25sec (505 seconds)

Published: Tue Dec 13 2022