This post is an extension of our previous post – Import an Excel file into Access . Today we are going to show you how to build on this job in order to iterate upon a group of excel files in a specified file directory, enabling you to automate the bulk loading of multiple excel files into a MS Access database table.
If you haven’t yet read the previous tutorial please read it here before proceeding.
Step 1: Set up the File Directory for Iteration
If you recall, the job we just built should look like this:
This job is great but it is only set up to process a specific Excel file which we designate. Our next step is to extend this job so that it will process multiple excel files of the same format from a specified directory.
Select the component tFileList from the Palette and drag it into the job design window
Click on the tFileList component and then click on the “Component” tab near the bottom center of the screen
Under “Directory” , select the directory that contains the excel files by clicking on the “…” button.
Step 2: Connect tFileList
Right click on the tFileList component, select Run -> Iterate
Connect this “Iterate” link to the tFileInputExcel component
Step 3: Edit the tExcelInput field
Select the tFileInputExcel component and then navigate to the Component tab near the bottom middle of the screen
Change the Property type from “Repository” to “Built In”
Under “File name / Stream” delete the filepath string
Type in tfilelist then press cntrl + space
Select tFileList_1.CURRENT_FILEPATH
Step 4: Run the job
Congratulations! You are now ready to run your job.
Make sure you have multiple excel files in your directory
Select the “Run” tab near the bottom middle of the screen and click the “Run” button
Now you will see all your files are processed one by one and loaded into the Access database