It is often useful to be able to read in multiple files as a single data set instead of reading each file individually. For example, you may receive a daily file of sales transactions but when you're preparing your end of month report, you want to see the whole month of data.
This article provides a simple example using the Directory List node to look for multiple Excel files, then using the Excel File node to read in multiple files together.
The Directory list node is used for a range of purposes and this is just one example!
Download the Example Files
The attachment at the bottom of this article contains the example including the data. Download the zip file and extract to the location of your choosing. Once the files have been extracted, continue with the instructions below.
Importing the Example File
Import the example data flow (*.lna) by following the instructions in the section titled "Importing data flows and library nodes" within the "Sharing Data Flows and Library Nodes" help section.
Read in multiple files from a directory
- Add a Directory List node (Input Connectors) to your canvas
- In the Properties panel for the Directory List node, enter in the directory name where your files are saved and enter in the file pattern that you want to search for. For example, you can search of ALL files in the directory by entering "*.*" or you can search for files that all have "sales" at the start of filename by putting "sales*". In this example, we have the sample data saved in a folder "D:\Dataverse\Examples\data" and we want to find all the Excel files so the pattern to search is "*.xlsx".
- Run the Directory list node. You should see that there is now a "3" to indicate that 3 files were found. Click once on the "3" and the Data Viewer will open up and display the filenames for the three files found:
- Add an Excel File node (Input connectors) to your canvas.
- Drag the output pin of the Directory List node over to the Excel File node to connect.
- In the Properties panel for the Excel file node, type in "Filename" for the FilenameExpr property - leave the File property as blank.
- Run the Excel File node. You will see that there are 114 total records across the 3 files that have been read in. Clicking once on "114" will bring up the Data Viewer so that we can see this data.