Automated batch CSV import into MS Access
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.
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.
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
Note: If your version of Windows does not include the forfiles command (versions earlier than Vista or Windows 2003) you can download forfiles.exe from ftp://ftp.microsoft.com/ResKit/y2kfix/x86 for free.
Unfortunately, for that version the syntax is slightly different :-(
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).
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.
In Windows 2000, (2003 Server), or XP you will find the task planner at Start -> Control Panel -> Scheduled Tasks
In Windows Vista or Windows 2008 Server you will find the task planner at Programs -> Accessories -> System Tools -> Scheduled Tasks
Starting with Windows 2008 or Windows 7 you will find the task planner at Start -> Control Panel -> Administrative Tools -> Scheduled Tasks
Microsoft®, Windows®, Access® are registered trademarks of Microsoft Corporation