Automated batch CSV import into MS Access

This example describes how several CSV text files can be automatically imported into an MS Access database table by FlowHeater, demonstrating the use of the Access Adapter and the Batch Module.

The complete Definition described here can be found in the file Examples\EN\AccessAdapter\csv-batch-import-ms-access.fhd. All the examples are included in the download FlowHeater. The example can be used with Access versions 97 up to Office/Access 2016. The database formats of MDB, MDE, ACCDB and ACCDE are supported.

The problem

We want to automatically import several CSV text files, however many exist in a given directory, into an MS Access database table. In order to complete this task we will perforce require the Batch Module (FHBatch.exe) and a short CMD shell script to activate the whole process.

The solution

In a FlowHeater Definition there is no feature to specify wildcards or similar for the filename to process, so we have to make use of a little trick. Firstly, we create a FlowHeater Definition file that we can use to import the CSV text file by hand. After we have thoroughly tested the CSV import, we save the Definition with the name CSV-Batch-Import-MS-Access.fhd . Next we create a CMD shell script file called batch-import.cmd with contents as follows:

The CMD shell skript (batch-import.cmd)

This example will process with the Batch Module all CSV files that exist in the C:\Temp directory .
Note: All the CSV files there must have the same structure and be compatible with the import.fhd FlowHeater Definition.

Older FlowHeater versions did not support wildcards for the /ReadFileName parameter option. For this you must proceed as follows .

The CMD shell skript (batch-import.cmd) for Version 1.x

For technical information about the forfiles command click here. The forfiles command effectively loops through the names of all the CSV files in the current directory that comply with the wildcard specification batch-import*.csv, due to the /M parameter used. For each filename located, the Batch Module (FHBatch.exe) is invoked and passed the actual filename as a parameter.

The /ReadFileName option tells the Batch Module to substitute the CSV filename in the FlowHeater Definition file csv-batch-import-ms-access.fhd with the actual CSV filename of each file passed as a substituted parameter (thanks to the @path).

Automating import/export tasks

Automating using FlowHeater Server

It is very easy to plan such import/export tasks with FlowHeater Server and have them run at regular intervals. It is also possible with FlowHeater Server to monitor folders and automatically process new files immediately upon creation. FlowHeater Server also offers the possibility of sending email notifications including attachment of the files generated.

Automation as a Windows task

Of course, we could always simply run the CMD shell script manually as it stands, by a double click on the script’s icon. However, if we want to run it periodically and automatically then we need to ensure the CMD shell script is included as the target of a scheduled task under Windows.

You will find the task planner at Start -> Control Panel -> Administrative Tools -> Scheduled Tasks

_{Microsoft®, Windows®, Access® are registered trademarks of Microsoft Corporation}