Clarion Community Help

 

Data Conversion

You may have existing data from legacy applications that you want to save and use in your Clarion applications. Therefore, this chapter shows you:

·How to import a table definition from an existing table.

·How to browse and edit a table using the Database Manager.

·How to convert data from one table format to another.

Starting Point:

The LCLESSON.DCT should be open.

Importing a .CSV File Definition

One easy way to convert tables is to export your old data from the previous application to Comma Separated Values (.CSV) files. This is the file format originally used by the Basic language'the data is contained in DOUBLE-quotes, commas separate fields, and a Carriage Return/Line Feed separates records. Clarion's BASIC file driver will easily read from and write to these .CSV files.

We will import the definition of an existing .CSV file containing Customer data, then generate a simple table conversion program (to show you just how easy it is to do) to place the data into a TopSpeed table.

Import the file definition

1.In the DCT Explorer, press the Add button drop list, and select the Import Table option.

2.In the Select Server entry choose Comma Delimited Files (BASIC) from the dropdown list.

3.Press the ellipsis button to the right of the Select Dictionary entry to open the Select Database dialog. Alternatively, you can also press the Next button.

4.In the Filename field, press the ellipsis button to select '\Lessons\LearningClarion\import1.csv then press the Open button.

5.In the Driver Options field, press the ellipsis button to open the BASIC Driver String Builder dialog.

6.Locate and check the First Record is Header check box.

7.Press the OK button to close the BASIC Driver String Builder dialog, and then press the OK (or Next) button to close the Select Database dialog.

The import process completes, and you should now see a new Import1 table in the DCT Explorer.

Now you have the IMPORT1.CSV file's definition. The next step will be to look at the data in the Database Manager.

View the imported data

RIGHT-CLICK on the IMPORT1 table, highlight Browse Table then CLICK to call the Database Browser. Or press the Browse button on the DCT Explorer toolbar. Press the Apply button when the Dataset Parameters dialog is opened. This will open the Database Browser.

The Clarion Database Browser allows you to directly edit the data in your tables. This is a programmer's tool, designed to allow you to do whatever is necessary to change the actual data contained in your tables. This means that there are no safeguards against violating your database's Referential Integrity or Data Integrity rules. Therefore, you must take care when you use this tool.

In this lesson, we do not need to make any changes at this time, and simply wanted to identify this IDE tool if needed.

Converting a Table

At this point, you're looking at the .CSV file's data in Clarion's Database Browser utility. Next, you need to move that data into a TopSpeed table so your Clarion programs can use it.

Close the Database Browser at this time and return to the DCT Explorer in the already opened GSLesson dictionary.

Generate a table conversion program

1.In the DCT Explorer, right click on the CUSTOMER table, and select the following conversion option:

You can specify a table to convert from three different sources. The target can be directly on disk (using Data File), a table definition in the current dictionary (using Table Definition), or a table definition in another dictionary (using Table Definition from another DCT). We have chosen a table from our current dictionary.

If you have Clarion.NET installed, you also have the option of generating a conversion program in standard Clarion language format or Clarion# to generate a conversion program that runs with the .NET Framework.

The table that you select first in the conversion program process is always the target. In our example, the CUSTOMER table is the target.

2.In the Select a Table dialog, highlight IMPORT1 and press the Select button.

3.The Select New Project File dialog appears. Accept the default name (convert.cwproj), and the default LearningClarion folder name, by pressing the Save button.

Project files determine what source file to compile, and how to build (link) it. The default extension of project files is *.cwproj.

When you press the Save button, this will generate all the Clarion source code necessary to take the data in the Source Filename, and copy it into a new Target Filename, using the file format specified by the Target Structure.

4.The Select Destination Data File dialog appears. Highlight the CUSTOMER.TPS file located in the Learning Clarion folder, and press the Open button.

5.Another dialog pops up that asks you if you would like to load the data conversion program. Press the Yes button. We will return to the conversion program shortly.

The best reason to generate Clarion source code for the data conversion is to provide you the opportunity to modify the code before you compile and execute it to handle any special data conversion needs you may have. This makes the conversion process completely flexible to handle any situation that can occur.

Delete the IMPORT1 file definition

The only purpose this file definition served was to allow the Dictionary Editor to generate file conversion source code for you. Therefore, we can delete it from the Data Dictionary right now.

1.Highlight the IMPORT1 table in the DCT Explorer, and press the Delete button.

2.Press the Yes button when asked to confirm the deletion.

3.Press the Save and Close button to exit the Data Dictionary Editor, and press the Yes button to save your changes as you exit.

Load the conversion program

1.Choose View Solution Explorer (or press CTRL+ALT+L).

2.Expand the Convert project node, and highlight the Convert.clw file, then press the Open button in the Solution Explorer toolbar, or you can right-click and select Open from the popup menu, or simply double-click on the file to open it.

Clarion's Text Editor appears with the file loaded, ready to edit.

The Dictionary Editor created the conversion program code in this file. This contains all the Clarion language source code necessary to read the data from the BASIC (.CSV) file and copy it to the TopSpeed table.

Compile/Build and run the conversion program

The Dictionary Editor generated the Convert.cwproj file for you at the same time it created the Convert.clw file.

Every Clarion program has a Project that controls the options for compiling the source code and linking to create the resulting .EXE file. For hand-coded programs (and conversion programs generated by the Database Manager), these settings are contained in a .cwproj file.

At this point, you could modify the generated source code to perform any special data conversion you require (see the How to Convert a File'Generate Source and How to Make a Field Assignment topics for more information on customizing table conversion code). However, there is nothing we need to do in this project, so you simply compile and run the program.

1.Choose Debug Start Without Debugger from the IDE Menu (or press CTRL+F5). You can also press the button on the IDE toolbar.

This compiles the program, links it into an .EXE, then runs the resulting executable to perform the file conversion. A status window appears as the program runs, letting you know the progress of the file conversion. Since there are only a few records to convert in this case, you probably won't be able to read it (it'll go by too fast).

2.Choose File Close File to close the program source file and exit the Text Editor.

3.Choose File Close Solution to close the conversion project. Close the Solution Explorer by clicking on the “X” in the upper right corner of the Solution Explorer.

Check it out

Now you can check the data in the new file by opening it with the Database Browser and browsing through the records.

1.Choose Tools Browse Table from the IDE Menu.

2.In the Select Driver' dialog window, highlight TopSpeed (TPS) and press the Select button.

3.In the Open TopSpeed File dialog, press the ellipsis button, and select the CUSTOMER.TPS file, then press the OK button.

A Dataset Parameters dialog opens that allows you to specify how many rows you want to read. Press the Apply button to open the Database Browser:

This demonstrates another way to open the Database Browser, other than from within the Dictionary Editor.

4.Press the Save button in the Database Browser toolbar to save the changes to the TopSpeed table.

5.Press the Exit button to close the Browser, or you can simply select File Close File from the IDE Menu.

OK, What Did I Just Do?

Here's a quick recap of what you just accomplished:

Now you've converted some valuable existing data to the TopSpeed table format so your Clarion applications can use it. In the next chapter, we will begin building an application “from scratch” using the Application Generator.

Click here to jump to the next lesson