Importing data from text files in IBConsole

By: Gelein van de Voorde

Abstract: Starting with version (InterBase 2009 hotfix update 3) IBConsole supports importing data from fixed format text files. This feature is not mentioned in the helpfile of IBConsole. This article gives an explanation of how to find and to use the import tool. In this article the Employee table of the Employee database is used as an example.

Getting started

In IBConsole connect to the Employee database and select the Tables node in the left pane.

Doubleclick the Employee table in the right pane.

Select the Data Tab.

Click on the import button (the one marked with the red circle). The “Import data to table” dialog will be displayed.

Click on the Browse button (marked with the red circle). The “Select input file” dialog will be displayed.

In the “Files of type” dropdown list select Text file.

Select the text file you want to use. In this example we use the file employee.txt, that holds the data of the Employee table. This file was previously made with the Export function of IBConsole. Select the file in click the “Open” button (or double click on the file name). As the figure below shows the appearance of the “Import data to table” dialog changes. At the top the name of the importfile is displayed. Under it there is a scrollbox where the content of the text file is displayed. You can use the scrollbars to examine the content of the file. Below the scrollbox the position of the cursor and the number of selected characters are displayed. There is also a button “Assign to Column”, that is grayed out yet.

Beneath is a listview showing the fields of the table.

As the figure above shows, the file has 2 headerlines that we want to skip on import. If the importfile have trailing lines you want to skip you can also fill in the number of trailing lines to skip. Complete empty lines will never be imported. You can check the “Use Blanks as Null” checkbox to force <null> values on importing.

Assigning columns

The next we need to do is assign values to the Postition and the Size of the fields. To do this we select some data in the scrollbox starting with the EMP_NO column. A position of 1 and a width of 6 is displayed. Next we select the EMP_NO column in the listview.

We press the “Assign to Column” button and the values are assigned to the column.

Repeat this procedure for every column you want to import data to. Columns with no position assigned will be skipped on import. Finally press the “OK” button to start the import. It is not necessary that the importfile has headerlines although it makes the assignments a lot easier. The figure below shows an example of an inputfile without headerlines and spaces between the columns.

Managing definitions

Four buttons are supplied to manage the definitions.

The “Reset definition” button can be used to reset a definition to the initial state. This can be usefull when you accedently assigned a definition that can not be used.

The “Delete definition” button can be used for the same purpose, but is irrevisible. Once deleted you only can get the definition back by restarting the dialog.

The “Save definitions” button can be used to save the definitions to a file. IBConsole saves the definitions in a text file in a special format so it can be recognized. A file save dialog will be displayed to supply a path and a filename. The file will get the extension “.def”.

The “Import definitions” button can be used to recall definitions that where saved earlier to a file. A file open dialog will be displayed to select the definition file from the file system.

Published on: 10/5/2009 5:32:20 PM

Server Response from: ETNASC03

Copyright© 1994 - 2013 Embarcadero Technologies, Inc. All rights reserved.