Skip to main content
simply more productive

The Data Specialist

Thanks to our intuitive transformation designer, you can convert data effortlessly to a variety of formats without knowing how to program. It’s not only efficient, it’s also easy to use.
Flowheater Download

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"

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).

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

 

FlowHeater - The Data Specialist

Efficient data integration and transformation with FlowHeater – the perfect solution for a seamless transfer of your data.

Rechtliches

Support & Contact

Kontaktinformation

Telefon:0951 / 99339792 E-Mail:This email address is being protected from spambots. You need JavaScript enabled to view it.

Copyright © 2009-2024 by FlowHeater GmbH. All rights reserved.