Ispirer SQLWays Database Migration Software

Data Options

SQLWays exports data to text files.

General
If the Export Data check box is selected:
In the Start row box, enter the number of the start row to convert. The default number is 1.
In the Number of rows box, enter the number of the exported rows. The default value is all rows matching the query condition.
In the Prefetch count box, enter the number of rows that are read before being written to the text file. The default number is 1000 rows.

Formats: On this page you can specify text file formats and various properties.
If the Use datetime format check box is selected, you can specify a format of the datetime datatypes in the text files. Datetime data consist of valid date and time combinations. Depending on the database this is the DATE data type in Oracle, TIMESTAMP data type in IBM DB2 and DATETIME data type in Microsoft SQL Server and Sybase.

A datetime format can be composed of one or more datetime format elements as listed below.

YYYY - 4-digit year
YY - Last 2 digits of year
MM - 2-digit numeric abbreviation of month (01-12)
MON - 3-symbol abbreviated name of month (JAN, FEB etc)
DD - 2-digit day of month (01-31)
HH - 2-digit hour of day (01-12)
HH12 - 2-digit hour of day (01-12)
HH24 - 2-digit hour of day (00-23)
MI - 2-digit minute (00-59)
SS - 2-digit second (00-59)

F - Fraction of second. The number of F symbols represents the precision. For example, FFF for accuracy to the milliseconds and FFFFFF for accuracy to the microseconds. For example, to get datetime values like 21-DEC-2002 21-21-00 set the datetime format DD-MON-YYYY HH24-MI-SS. If the Use datetime format check box is clear, the default datetime format is used. By default, ODBC drivers convert datetime values using the ISO format YYYY-MM-DD HH24:MI:SS.FFF (the part FFF depends on database).

Datetime data consists of valid date and time combinations. Time can contain a fraction part. For example, IBM DB2 TIMESTAMP data type can contain microseconds, Microsoft SQL Server and Sybase DATETIME can keep milliseconds. In the Save datetime fraction box, select how datetime fraction is saved.
If Yes is selected, the fractional seconds are saved in the text files.
If No is selected, the fractional seconds are not saved in the text files.
If Default is selected, the fractional seconds are saved unless the target database is Oracle. If Default is selected, the target database is Oracle and the datetime datatypes are converted to the Oracle DATE data type, the fractional seconds are not saved, since the Oracle DATE data type doesn't support fractional seconds. If Default is selected, the target database is Oracle and the datetime datatypes are converted to the Oracle TIMESTAMP data type, the fractional seconds are saved, since the Oracle TIMESTAMP data type supports fractional seconds. Note. This option is ignored if any value is specified in the option Datetime format (see earlier).
If the Use date format check box is selected, you can specify a format of the date datatypes in the text files. A date is a three-part value (year, month and day). Not all databases have a data type that allows you to keep date values without a time part. For example, such data type exists in IBM DB2 and MySQL (the DATE data type). A date format can be composed of one or more date format elements as listed below.

YYYY - 4-digit year
YY - Last 2 digits of year
MM - 2-digit numeric abbreviation of month (01-12)
MON - 3-symbol abbreviated name of month (JAN, FEB etc)
DD - 2-digit day of month (01-31)

For example, to get date values like 21-DEC-2002, set the datetime format DD-MON-YYYY. If the Use date format check box is clear, the default date format is used. By default, ODBC drivers convert date values using the ISO format YYYY-MM-DD.

If the Use time format check box is selected, you can specify a format of the time datatypes in the text files. A time is a three-part value (hour, minute and second). Not all databases have a data type that allows you to keep time values without a date part. For example, such data type exists in IBM DB2 and MySQL (the TIME data type). A time format can be composed of one or more time format elements as listed below.

HH - 2-digit hour of day (01-12)
HH12 - 2-digit hour of day (01-12)
HH24 - 2-digit hour of day (00-23)
MI - 2-digit minute (00-59)
SS - 2-digit second (00-59)

For example, to get time values like 21-21-00 set the time format HH24-MI-SS. If the Use time format check box is clear, the default time format is used. By default, ODBC drivers convert time values using the ISO format HH24:MI:SS.

By default the Use ODBC float conversion check box is selected, so numeric data (REAL, FLOAT, DOUBLE datatypes) are converted to the text representation by ODBC driver. Otherwise, SQLWays performs the conversion.
Decimal point: This option specifies a single character, which is used instead of a decimal point character for numeric values. The default value depends on the Regional Settings.

Files
If the Default for the target check box is selected, it is possible to specify the output format for the text file. Available values are:
Column-delimited (CSV) output format
Fixed length output format
TAB-delimited output format
INSERT statements
XML output format
Btrieve output format
In the Column delimeter box, enter a column delimiter used in the text files. This option can be used for the CSV format only. The default value is a comma. It is possible to specify one or multiple characters as a column delimiter.
In the Blanks count box, enter number of blanks that are placed between columns for FIX (fixed length) output format. The default number is 1.
In the Line delimeter box, enter a line delimiter used in the text files. The default value is carriage return and new line characters (0x0D0A or \r\n) unless import system is Unix. If import system is Unix, the default. value is a new line character (0x0A or \n).
In the Replace new line box, enter a character string that replaces newline characters in the data. Newline characters are 0x0D0A or \r\n for Windows and 0x0A or \n for Unix.
If the Remove new line check box is selected, newline characters are removed from the data. Newline characters are 0x0D0A or \r\n for Windows and 0x0A or \n for Unix.

To specify Data Export options in the command prompt

SQLWays command prompt - [Data] subsection

             

Copyright © 1999-2015 Ispirer Systems Ltd. Ispirer and SQLWays are trademarks of Ispirer Systems Ltd. All other product names may be trademarks of the respective companies. All rights reserved.