CSV text file import into a Microsoft Access database

This example describes how the Access Adapter can be used to import a CSV text file into a MS Access database table. It also explains how to configure FlowHeater for importing with insert or update SQL operations.

Note: An export of data to a CSV or flat file follows the same principles, but where the Adapters on the READ and WRITE sides are swapped over.
The complete Definition described here can be found in the file Examples\AccessAdapter\csv-import-ms-access.fhd. All the examples are included in the downloaded ZIP archive of FlowHeater. The example can be used with Access versions 97, 2000, 2002 (XP) 2003 and 2007. The database formats of MDB, MDE, ACCDB and ACCDE are supported.

Let’s get started. First create a new MS Access database called "FlowHeater.mdb" (or "FlowHeater.accdb") to contain the import. Next insert a new table called Import with the following properties:

This results in a new table with 8 columns and a Primary Key. The Primary Key (ID field) has the additional property of an identity (AutoNumber) column. The unique values assigned to the ID will be determined by MS Access itself and FlowHeater is not involved in this.

choose MS Access Adapter for import

Choose MS Access Adapter

Now open FlowHeater and click on the menu option "New" to create a new Definition. In the resulting popup window select for the READ side the TextFile Adapter and for the WRITE side the Access Adapter.

choose CSV text file to import

Select CSV text file

Confirm with OK to close the popup and now open the Configurator for the READ Adapter and select the file import.csv in the directory "ExamplesENAccessAdapter". Check the checkbox "First row contains field names" and confirm the request of whether the field names should be loaded. All other settings on this page/tab should remain their defaults.

Terminate the popup window with OK and open the Configurator for the WRITE Adapter.

MS Access CSV import properties

MS Access CSV import properties

For the database selection, choose the newly created Access database file (FlowHeater.mdb or FlowHeater.accdb) and then switch to the "Fields / Data types" tab.

MS Access data types and field properties

MS Access data types and field properties

Choose the Import table from the dropdown and then click the "Load Schema" button to load the schema.

autoconnect CSV-SQL fields

Autoconnect CSV-SQL fields

Close the Configurator popup with OK and click on the Designer command "Automatically connect fields" (circled in red). Your screen should now appear similar to the screenshot on the right.

Notice that we now have 2 unconnected fields on the READ side and 3 on the WRITE side. First construct a Pipe to connect the DateOfBirth field to the BirthDate field by dragging and dropping from one field to the other. Now move the mouse over this Pipe so that it is becomes bolder, indicating it is selected, and right mouse click to open the context menu for the Pipe. Choose the menu option Insert a Clone Heater. At your right mouse click position on the Pipe a Clone Heater will be inserted. Drag the newly inserted Heater further down the design area, stretching the Pipe with it. This makes some space where we can to insert two more Heaters, to split the combined PostalCode/City string field into two separate string fields as PostCode and City in our MS Access table.

insert heaters/functions

Insert heaters/functions

Twice drag and drop the SubString Heater from the string Heaters selection area at the bottom of the Designer page to roughly level with the fields of PostalCode and City on the design area. Connect the PostalCode/City field on the READ side to both these SubString Heaters. Then connect one of the Heaters to the PostalCode field and the second Heater to the City field on the WRITE side. Next double click on the SubString Heater you just connected to the PostalCode field. Enter in the popup that opens a Start Offset value of 0 and Length of 5 and confirm with OK. Repeat this for the Heater connected to the City field, but instead enter a Start Offset of 6 and a Length of 0.

Note: FlowHeater strings begin with offset 0, as we used for the PostalCode and the length of 0 we specified for City means until the end of the string.

Your screen should now look like the screenshot above. Now execute the definition, either by clicking on the command button (circled in red) or by pressing F5 on the keyboard.

start the CSV import

Start the CSV import

This opens the FlowHeater execute and test window (see on the right). It is always prudent to first test an import before committing the actual changes to a database. So first try a test and check the results in this "Test and Execute" window. Leave the "Test run" option checked and click on the "Start Definition" button.

The label of the button will briefly change to read "Abort" and then the following window will appear. Note: The "Abort" button can be used to break off a time-consuming execution of a FlowHeater Definition at any point.

Result

Here we can see that FlowHeater has automatically recognized the ID field as an automatic or identification value. Close the window, uncheck the "Test run" option in the Execute and Test popup window and click Start Definition again. Now our CSV text file is actually written to the Import table in your MS Access database. Notice that the values in the ID column are now assigned actual values and in the first column "FH Action" has the value of insert. Run the Definition a second time and you will see that FlowHeater has recognized that this time it is a revision and so update appears in the "FH Action" column.

Open the Configurator of the Access Adapter and uncheck the option Update data. Press the Start Definition button again and observe the result this time (with and without a test run). This time FlowHeater inserts the records into the database again. You may be wondering why it is that the ID field is not copied from the CSV data into the database, because a Pipe connects them? The reason is it depends on the ID field, which is automatically assigned by the MS Access database because it has the IDENTITY / AUTONUMBER property and the Pipe is only needed for updates.

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