Importing and exporting data from a text file

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.

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.

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, click in the Structure pane or Design pane 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 Structure pane or Design pane 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 glif 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. Select a TableScrollPane component from the dbSwing tab, click in the Design pane to add it to your application.

    Scrolling behavior is not available by default in any Swing component or dbSwing extension, so, to get scrolling behavior, we add the scrollable Swing or dbSwing components to a JScrollPane or a TableScrollPane. TableScrollPane provides special capabilities to JdbTable over JScrollPane. See the dbSwing documentation for more information.

  2. Select a JdbTable component from the dbSwing tab, click in the TableScrollPane component to add it to the container.

  3. In the Inspector, set the dataSet property of the JdbTable 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.

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

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


Exporting data

Exporting data, or saving data to a text file, saves all of the data in the current view to the text file, overwriting the existing data. This topic discusses several ways to export data. You can export data that has been imported from a text file back to that file or to another file. You can export data from a QueryDataSet or a ProcedureDataSet to a text file. Or you can resolve data from a TableDataSet to an existing SQL table.

Exporting data to a text file is handled differently than resolving data to a SQL table. Both QueryDataSet and TableDataSet are StorageDataSet components. When data is provided to the data set, the StorageDataSet tracks the row status information (either deleted, inserted, or updated) for all rows. When data is resolved back to a data source like a SQL server, the row status information is used to determine which rows to add to, delete from, or modify in the SQL table. When a row has been successfully resolved, it obtains a new row status of resolved (either RowStatus.UPDATE_RESOLVED, RowStatus.DELETE_RESOLVED, or RowStatus.INSERT_RESOLVED). If the StorageDataSet is resolved again, previously resolved rows will be ignored, unless changes have been made subsequent to previous resolving. When data is exported to a text file, all of the data in the current view is written to the text file, and the row status information is not affected.

Data exported to a text file is sensitive to the current sorting and filtering criteria. If sort criteria are specified, the data is saved to the text file in the same order as specified in the sort criteria. If row order is important, remove the sort criteria prior to exporting data. If filter criteria are specified, only the data that meets the filter criteria will be saved. This is useful for saving subsets of data to different files, but could cause data loss if a filtered file is inadvertently saved over an existing data file.

Warning: Remove filter criteria prior to saving, if you want to save all of the data back to the original file.

Tutorial: Exporting data to a text file

When you export data from a TableDataSet to a text file, JBuilder creates a .SCHEMA file that defines the columns by name and data type. The next time you import the data into JBuilder, you do not have to define the columns, because this information is already specified in the .SCHEMA file.

This part of the tutorial demonstrates how to use the UI designer to add a button for saving the data, with any changes, back to the same text file.

  1. Double-click the Frame file to bring it into the Content pane, if it is not already there. Select the Design tab of the Content pane.

  2. Select contentPane in the Structure pane. In the Inspector, change its layout property to null.

  3. Select tableScrollPane1 in the Structure pane. In the Design window, grab the upper handle and resize the component to allow room to add a button.
  4. Add a JButton component from the Swing tab to the UI designer. On the Properties tab of the Inspector, set the text property to Save Changes.

  5. Click the Events tab of the Inspector. Select, then double-click the actionPerformed() method. This changes the focus of the AppBrowser from the UI designer to the Source pane and displays the stub for the actionPerformed() method.

    Add the following code to the actionPerformed() method:

      try {
          tableDataSet1.getDataFile().save(tableDataSet1);
          System.out.println("Changes saved");
      }
      catch (Exception ex) {
        System.out.println("Changes NOT saved");
        System.err.println("Exception: " + ex);
      }
    

  6. Run the application by selecting Run|Run Project.

When you run the application, if it compiles successfully, the application appears in its own window.

Data is displayed in a table, with a Save Changes button. Make and view changes as follows:

  1. With the application running, select the string field in the first record of the Frame window and change the value in the field from A to Apple. Save the changes back to the text file by clicking the Save Changes button.

  2. View the resulting text file in a text editor. It will now contain the following data:

    1,"Apple"
    2,"B"
    3,"C"
    

  3. Close the text file.

    JBuilder automatically creates a .SCHEMA file to define the contents of the text file.

  4. View the .SCHEMA file in a text editor. Notice that this file contains information about the name of the fields that have been exported and the type of data that was exported in that field. It looks like this:

    []
    FILETYPE = VARYING
    FILEFORMAT = Encoded
    ENCODING = ISO8859_1
    LOCALE = en_US
    DELIMITER = "
    SEPARATOR = ,
    FIELD0 = my_number,Variant.SHORT,-1,-1,
    FIELD1 = my_string,Variant.STRING,-1,-1,
    

  5. Close the .SCHEMA file.

  6. Close the running example.

You can continue to edit, insert, delete, and save data until you close the application, but you must click the Save Changes button to write any changes back to the text file. When you save the changes, the existing file will be overwritten with data from the current view.

Tutorial: Using patterns for exporting numeric, date/time, and text fields

By default, JBuilder expects data entry and exports data of date, time, and currency fields according to the locale property of the column. You can use the exportDisplayMask property to read or save date, time, and number fields in a different pattern. These steps demonstrate creating an exportDisplayMask for a new column of type DATE.

  1. Select Frame1 in the Content pane, then select the Design tab. Expand the TableDataSet component in the Structure pane by clicking on the expand glif to its left. Select <new column>, then modify the column's properties in the Inspector as follows:

  2. Run the application. In the running application window, enter a date in the locale syntax of your computer in the my_date column of the first row. For example, with the locale property set to English (United States), you must enter the date in a format of MM/dd/yy, like 11/16/95. Click the Save Changes button to save the changes back to the text file.

  3. View the text file in a text editor, such as Notepad. It will now contain the following data:

    1,"Apple",11/16/95
    2,"B"
    3,"C"
    

  4. Close the text file.

  5. View the .SCHEMA file in a text editor. Notice that the new date field has been added to the list of fields. It looks like this:

    []
    FILETYPE = VARYING
    FILEFORMAT = Encoded
    ENCODING = ISO8859_1
    LOCALE = en_US
    DELIMITER = "
    SEPARATOR = ,
    FIELD0 = my_number,Variant.SHORT,-1,-1,
    FIELD1 = my_string,Variant.STRING,-1,-1,
    FIELD2 = my_date,Variant.DATE,-1,-1,
    

  6. Close the .SCHEMA file.

The next steps show what happens when you change the date pattern, edit the data, and save the changes again.

  1. Close the running application and the text files and return to the JBuilder designer. Select the my_date column and enter the following pattern into the exportDisplayMask property in the Inspector: MM-dd-yyyy. The syntax of patterns is defined in "String-based patterns (masks)". This type of pattern will read and save the date field as follows: 11-16-1995.

  2. The application would produce an error now if you tried to run it, because the format of the date field in the text file does not match the format the application is trying to open. Manually edit the text file and remove the value ",11/16/95" from the first row.

    Instead of the above step, you could manually enter code that would establish one exportDisplayMask for importing the data and another exportDisplayMask for exporting the data.

  3. Run the application. In the running Frame window, enter a date in the my_date column of the first row using the format of the exportDisplayMask property, such as 11-16-1995. Click the Save Changes button to save the changes back to the text file.

  4. View the text file in a text editor. It will now contain the following data:

    1,"Apple",11-16-1995
    2,"B"
    3,"C"
    

  5. Close the text file.

  6. View the .SCHEMA file in a text editor. Notice that the date field format is displayed as part of the field definition. When the default format is used, this value is blank, as it is in the FIELD0 definition. It looks like this:

    []
    FILETYPE = VARYING
    FILEFORMAT = Encoded
    ENCODING = ISO8859_1
    LOCALE = en_US
    DELIMITER = "
    SEPARATOR = ,
    FIELD0 = my_number,Variant.SHORT,-1,-1,
    FIELD1 = my_string,Variant.STRING,-1,-1,
    FIELD2 = my_date,Variant.DATE,-1,-1,MM-dd-yyyy
    

  7. Close the .SCHEMA file.

When the text data file is imported next, the data will be imported from the information in the .SCHEMA file. To view data in the table in a different pattern, set the displayMask property. To modify data in the table using a different pattern, set the editMask property. These properties affect viewing and editing of the data only; they do not affect the way data is saved. For example, to enter data into a currency field without having to enter the currency symbol each time, use a displayMask that uses the currency symbol, and an editMask that does not contain a currency symbol. You can choose to save the data back to the text file with or without the currency symbol by setting the exportDisplayMask.