Create Text File for Loader

How To Access

  • Main Menu | Database | Create Text File for Server Loader (Server will be whatever the backend server is)

  • Shortcut menu | Create Text File for Server Loader (Server will be whatever the backend server is)

The menu items are only enabled if a converted SQL table is selected and the driver is Oracle, PostgreSQL, or MySQL/MariaDB drivers.

This dialog allows you to generate a text file from one or more DataFlex files. The text file can then be used with the database server’s high-speed text migration utility. An example is SQL*Loader for Oracle. You have the option of creating new RECNUM values or keeping the original RECNUM values.

ConvertDAT.png

Export Options tab

Settings in the Export Options tab determine the format and location of the output data. This is the same regardless of the server back end.

File

  • File Name: The name of the Dataflex file. This is read only.

  • Total Records: The number of records in the file. This is read only.

  • Text File Name: Type a new name for the text file if you want.

  • Directory: Directory where the file will be stored. Use the Browse button to change the export path for the file.

Export Options

  • Select the Export Data & Control File option if you want the data to be exported to a text (.txt) file.

  • Select the Export Control File Only option if you only want to generate the controlling scripts and batch file (this option is only available for Oracle).

  • Field Separator: Use the pulldown list to choose the field separator in the text file. This must be different from the decimal separator.

  • Decimal Separator: Use the pulldown list to choose the decimal separator in the text file. This must be different from the field separator.

  • Output Format: Select either Windows or Unix format.

  • Preserve Recnum: Check this box to copy existing DataFlex RECNUM values to the RECNUM column in the SQL table. If this option is not selected, the SQL server assigns RECNUM values starting with one.

  • Recnum Range: Check this box to enable the From and To text boxes. If these numbers are filled in, then only the rows that have RECNUM values in the given range are exported. Otherwise the entire file is exported.

Server SQL Loader Options tab

The SQL Load Options tab contains server-specific settings that determine how the data is loaded. What is shown depends on the server backend.

Oracle SQL*Loader Options tab

  • Control File Name: The name of the SQL*Loader control file. The control file tells SQL*Loader how to interpret the data file. It includes for example, the names and format of the data files, how to identify the start and end of data fields, and the type of load to be performed.

  • Client Execution: If the loader scripts will be run from the client, select the Client Execution option. If the loader scripts will be run from the server, uncheck the Client Execution option.

  • DIRECT PATH Load: SQL*Loader provides two methods for loading data: conventional path load and direct path load. If unchecked, a conventional path load (the default) will be used. This uses the SQL INSERT statement and a bind array buffer to load data into database tables. This method is used by all Oracle tools and applications. If checked, a direct path load will be used. This parses the input data according to the description given in the loader control file, converts the data for each input field to its corresponding Oracle column data type, and builds a column array structure (an array of <length, data> pairs). Direct path load is faster than conventional path load. However, there are certain restrictions on direct path loads that may require you to use a conventional path load. Consult your Oracle Enterprise Manager manual for more details.

  • Load Type: There are three options:

    • REPLACE: drops the existing table and then creates the table and loads the new data.

    • APPEND: indicates that the table need not be empty before SQL*Loader is run. Existing data is preserved and new data is loaded.

    • TRUNCATE: deletes all data from the table before performing the load.

PostgreSQL Load Options tab

  • Client Execution: Check this box if the loader scripts will be run from the client. If the loader scripts will be run from the server, uncheck this option.

MySQL/MariaDB Load Options tab

  • Client Execution: Check this box if the loader scripts will be run from the client. If the loader scripts will be run from the server, uncheck this option.

  • Load Type: There are two options:

    • REPLACE: if an existing row in the table has the same value as a new row for a primary key or a unique index, the old row is deleted before the new row is inserted.

    • IGNORE: input rows that duplicate an existing row on a unique key value are skipped.