Dynamic data import / export

datenbank

simply more productive
Up to 10,000 records can be processed with the freeware version!

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)

@echo off
REM adapt the following to the actual path of FHBatch.exe
set FHBATCH="C:\Program Files\FlowHeater V4\BIN\FHBatch.exe"

%FHBATCH% /ReadFileName C:\Temp\*.csv import.fhd

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

@echo off
forfiles /M batch-import*.csv /C "cmd /c ..\..\..\Bin\FHBatch.exe /ReadFileName @path csv-batch-import-ms-access.fhd"

 

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 :-(

@echo off
forfiles -mbatch-import*.csv -c "cmd /c ..\..\..\Bin\FHBatch.exe /ReadFileName @FILE csv-batch-import-ms-access.fhd"

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.

FHBatch.exe /ReadFileName @path csv-batch-import-ms-access.fhd

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