Importing and exporting data from a text file

Database application development is a feature of JBuilder Professional and Enterprise. Distributed application development is a feature of JBuilder Enterprise.

In JBuilder, a TableDataSet component is used to store data imported from a text file. Once the data is provided to the data set, it can be viewed and modified. To save changes back to the text file, export the data back to the text file.

To import data from a text file, use the TextDataFile component to provide the location of the text file and parameters specific to its structure. Use a StorageDataSet, such as a TableDataSet component, to store the data locally for viewing and editing. Create Column objects so the TableDataSet knows the type of data and the name of the field for each column of data.

Columns of a TableDataSet are defined by adding columns in the Source window, the UI designer, or by loading a text file with a valid .SCHEMA file. This topic discusses the first two options. Importing data using an existing .SCHEMA file is discussed in "Tutorial: creating a database application from a text file". Your text file has a valid .SCHEMA file only if it has previously been exported by JBuilder.

These are the topics covered:

Tutorial: Importing data from a text file

This tutorial shows how to provide data to an application using a TableDataSet component and a comma-delimited text data file. This type of file can be exported from most desktop databases. This application is available as a finished project in TextFileImportExport.jpr in the /samples/DataExpress/TextFileImportExport directory of your JBuilder installation.

For this example, create a text file to import as follows:

  1. Open a text editor.
  2. Enter the following three rows and two columns of data (a column of integer values and a column of string values) into a blank text file. Press the Enter or Return key at the end of each row. Enter the quotation marks as well as the data.

    1,"A"
    2,"B"
    3,"C"
    
  3. Save the file with the name ImportTest.txt. Close the file.

To read the data from this text file and create a database application in JBuilder,

  1. Select File|Close. Select File|New. Double-click the Application icon and accept all defaults.

  2. Select the Design tab in the content pane.

  3. Select a TextDataFile component from the Data Express tab of the component palette, and click in the component tree or the UI designer to add the component to your application. Select the following properties in the Inspector, and set their values as indicated:
    Property name Value
    delimiter " (double quote)
    separator , (comma)
    fileName <path to>ImportTest.txt

    A delimiter in a text file is a character that is used to define the beginning and end of a string field. By default, the delimiter for string data types is a double quotation mark. For this tutorial, no changes are needed.

    A separator in a text file is a character that is used for differentiating between column values. By default, the separator character is a tab (/t). For this example, the separator is a comma (,). When using other text files, modify these properties accordingly.

    Specify the complete path and file name for the fileName field.

  4. Select a TableDataSet component from the Data Express tab of the component palette, click in the component tree or UI designer to add the component to your application. Select its dataFile property, and set it to textDataFile1.

  5. Add columns to the TableDataSet. This tutorial describes adding columns to the data set through the UI designer. To add columns using the editor, see "Adding columns to a TableDataSet using the editor". If you have completed this tutorial previously and exported the data to a text file, JBuilder created a .SCHEMA file that provides column definitions when the text file is opened and you do not need to add columns manually.

    Click the expand icon to the left of the TableDataSet component to expose existing columns. In this case, there are no existing columns, so select <new column> and set the following properties in the Inspector for the first column:

  6. Set the properties for the second column by selecting <new column> again. Set the following properties in the Inspector:

To make the data available to your application,

  1. Add a TableScrollPane to the panel in the UI designer, then drop a JdbTable into the center of it.
  2. Set the dataSet property of the jdbTable1 to tableDataSet1. You will see an error dialog if a valid data file is not specified or if the columns are not defined correctly. If you do not instantiate a visual component to view data, you must explicitly open the file in the source code to have access to the data.

  3. Select Run|Run Project to compile and run the application.

    The running application looks like this, after adding file export capabilities, as discussed in "Tutorial: Exporting data to a text file".

    Import/Export application

  4. Close the running application.

When you run this application, the data in the text file is loaded into a TableDataSet and displayed in the visual table component to which it is bound. You can now view, edit, add, and delete data from the data set. A TableDataSet component can be used as either a master or a detail table in a master-detail relationship. To save changes back to the text file, you must export the data back. See "Exporting data" for more information on exporting.

Adding columns to a TableDataSet in the editor

You can add columns to the TableDataSet in two ways: visually in the UI designer and with code in the editor on the Source tab. Adding columns in the UI designer is covered in "How to import data from a text file". If you previously exported to a text file, JBuilder created a .SCHEMA file that provides column definitions when the text file is next opened; therefore, you do not need to add columns manually.

To add the columns using the editor, you define new Column objects in the class definition for Frame1.java as follows:

  1. Select Frame1.java in the content pane, then select the Source tab. You will see the class definition in the Source window. Add the follow line of code:
         Column column1 = new Column();
         Column column2 = new Column();
    
  2. Find the jbInit() method in the source code. Define the name of the column and the type of data that will be stored in the column, as follows:

         column1.setColumnName("my_number");
         column1.setDataType(com.borland.dx.dataset.Variant.SHORT);
    
         column2.setColumnName("my_string");
         column2.setDataType(com.borland.dx.dataset.Variant.STRING);
    
  3. Add the new columns to the TableDataSet in the same source window and same jbInit() method, as follows:

         tableDataSet1.setColumns(new Column[] { column1,column2 } );
    
  4. Compile the application to bind the new Column objects to the data set, then add any visual components.

Importing formatted data from a text file

Data in a column of the text file may be formatted for exporting data in a way that prevents you from importing the data correctly. You can solve this problem by specifying a pattern to be used to read the data in an exportDisplayMask. The exportDisplayMask property is used for importing data when there is no .SCHEMA file associated with the text file. If there is a .SCHEMA file, its settings have precedence. The syntax of patterns is defined in "Edit/display mask patterns".

Date and number columns have default display and edit patterns. If you do not set the properties, default edit patterns are used. The default patterns come from the java.text.resources.LocaleElements file that matches the column's default locale. If no locale is set for the column, the data set's locale is used. If no locale is set for the data set, the default system locale is used. The default display for a floating point number shows three decimal places. If you want more decimal places, you must specify a mask.

Retrieving data from a JDBC data source

The following code is an example of retrieving data from a JDBC data source into a TextDataFile. Once the data is in a TextDataFile, you can use a StorageDataSet, such as a TableDataSet component, to store the data locally for viewing and editing. For more information on how to do this, see "Tutorial: Importing data from a text file".

	Database db = new Database();
	db.setConnection(new
		com.borland.dx.sql.dataset.ConnectionDescriptor("jdbc:oracle:thin:@" +
 		datasource, username, password));
	QueryDataSet qds = new QueryDataSet();
	qds.setQuery(new com.borland.dx.sql.dataset.QueryDescriptor(db, "SELECT
 		* FROM THETABLE", null, true, Load.ALL));
	TextDataFile tdf = new TextDataFile();
	tdf.setFileName("THEDATA.TXT");
	tdf.save(qds);

This code produces a data file and an associated .SCHEMA file.

You can use this type of data access to create a database table backup-and-restore application that works from the command line, for example. To save this information back to the JDBC data source, see "Saving changes loaded from a TextDataFile to a JDBC data source".