Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Previous revision
guides:migration-toolkit:sqlways-wizard:database-migration-guide:conversion-options:table-options:data-options:files [2018/04/14 16:00]
guides:migration-toolkit:sqlways-wizard:database-migration-guide:conversion-options:table-options:data-options:files [2023/12/15 08:13] (current)
volha.zhyrava
Line 1: Line 1:
 +\\
 +[[https://​www.ispirer.com|Ispirer Website]]
 +[[https://​www.ispirer.com/​products/​migration-toolkit|Ispirer Toolkit Overview]]
 +[[https://​www.ispirer.com/​download/​download-demo|Free Trial]]
 +====== Files ======
  
 +==== Overview ====
 +
 +^ **Option Name** ^ Description ^
 +| [[files#​Default for the target|Default for the target]] | Controls output text format |
 +| [[files#​Column delimiter|Column delimiter]] | Specifies column delimiter for files with data |
 +| [[files#​Blanks count|Blanks count]] | Specifies the number of blanks between columns for FIX output format |
 +| [[files#​Commit count|Commit count]] | Controls number of rows for COMMIT during data insert |
 +| [[files#​Line delimiter|Line delimiter]] | Specifies line delimiter for files with data |
 +| [[files#​Replace newline|Replace newline]] | Specifies a character string that replaces newline characters in the data  |
 +| [[files#​Remove newline|Remove newline]] | Controls whether newline characters should be removed or no  |
 +| [[files#​Resolve delimiter conflicts|Resolve delimiter conflicts]] | Controls if resolving delimiter conflicts should be done during data export |
 +| [[files#​Output column names in the first row of data file|Output column names in the first row of data file]] | Controls if columns names should be add before data in the first row of data file |
 +| [[files#​Directory for LOB Files|Directory for LOB Files]] | Indicate the target folder for the LOB data objects ​ |
 +| [[files#​Write the LOB data inside the text files|Write the LOB data inside the text files]] | Controls if data of the LOB columns (CLOB, BLOB, TEXT, IMAGE and MEMO) is written inside the text file |
 +
 +{{:​guides:​migration-toolkit:​sqlways-wizard:​database-migration-guide:​conversion-options:​table-options:​data-options:​ddl_files_comb.jpg?​nolink|}}
 +
 +Choose "​Tables"​ in the tree on the left, "Data Options"​ tab, "​Files"​ combobox.
 +
 +==== Default for the target ====
 +
 +The option controls output text format in which data will be extracted from source database for further importing into target database.
 +Below output text formats are allowed.
 + * Column delimited (CSV)
 + * Fixed length: ​
 + * TAB delimited
 + * INSERT statements
 + * XML
 + * Btrieve data file
 + * PC/IXF
 +
 +The tool identifies more suitable out text format for specific target DBMS. For most databases data is extracted into *.TXT files. ​
 +
 +==== Column delimiter ====
 +
 +The option controls column delimiter in files with data. By default comma ","​ delimiter is used.
 +
 +==== Blanks count ====
 +
 +This option specifies the number of blanks that are placed between columns for FIX (fixed length) output format. The default value is 1.
 +
 +==== Commit count ====
 +
 +This option is only valid for INS (INSERT statements) output format and is used to generate a COMMIT statement after the specified number of INSERT statements is generated. The default value is 0 (no commits are generated).
 +
 +==== Line delimiter ====
 +
 +The option specifies line delimiter for files with data. By default carriage return (CR) is used.
 +
 +==== Replace newline ====
 +
 +Specifies 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. 
 +Character columns like CHAR and VARCHAR e.g. can contain newline characters in the data. By default, SQLWays saves them in the export files. Using this option you can specify a string that will replace newline characters. ​
 +For example, to replace newlines with zzz, set REPLACE_NEWLINE=zzz,​ to replace newlines with a blank, set REPLACE_NEWLINE=” ”. 
 +To specify the hexadecimal value of a character use the following syntax: 0xhh. For example, 0x2c specifies a comma character. Non printing characters like tab, carriage return and new line can be represented by \t, \r and \n accordingly. To specify a backslash character use '​\\'​. You can combine different character representations to specify the string. For example, REPLACE_NEWLINE=¦¦¦0x2c0x2c¦¦¦ ​
 +When you need to remove newlines from the data, use the REMOVE_NEWLINE option. The REPLACE_NEWLINE option is ignored when the REMOVE_NEWLINE option is set to Yes (see below). ​
 +In most cases, when you need to save newline characters in the data you have to change the line delimiter using the /LDEL option. This makes possible properly processing rows by import/load utilities.
 +
 +==== Remove newline ====
 +
 +The option controls whether newline characters should be removed or no.
 +If "​Yes"​ is specified, newline characters are removed from the data. Newline characters are 0x0D0A or \r\n for Windows and 0x0A or \n for Unix. 
 +Character columns like CHAR and VARCHAR e.g. can contain newline characters in the data. By default, SQLWays Wizard saves them in the export files. Using this option, you can specify that newline characters be removed from the data. The default is No. Possible values - "​Yes",​ "​No"​. ​
 +If you need to replace newline characters with another string, use the REPLACE_NEWLINE option (see earlier). ​
 +In most cases, when you need to save newline characters in the data, you have to change the line delimiter using the /LDEL option. This makes possible properly processing rows by import/load utilities.
 +
 +==== Resolve delimiter conflicts ====
 +
 +This option controls if resolving delimiter conflicts should be done during data export.
 +It is helpful when source data contains the same symbols as used as delimiter. If delimiter conflicts are not resolved, target database will not be able to import data correctly.
 +If resolving delimiter conflicts option is set into "​Yes",​ additional files with conflict data are created. In these files specific delimiters are used to demarcate each row and each column.
 +By default "​Yes"​ value is used.
 +
 +==== Output column names in the first row of data file ====
 +
 +The option controls if columns names should be add before data in the first row of data file.
 +By default "​No"​ value is used.
 +
 +==== Directory for LOB Files  ====
 +
 +Using this text-box you can specify the path to the location of the files, that will contain the <​html><​acronym title="​Large OBject">​LOB</​acronym></​html>​ data for load into the //Oracle// or //IBM DB2// databases after the export.
 +
 +In the <​html><​acronym title="​Large OBject">​LOB</​acronym></​html>​ directory box, enter the path or click (...) to indicate the target folder for the <​html><​acronym title="​Large OBject">​LOB</​acronym></​html>​ data objects (large objects such as <​html><​acronym title="​Character Large OBject">​CLOB</​acronym></​html>​ and <​html><​acronym title="​Binary Large OBject">​BLOB</​acronym></​html>​ data types in Oracle, IBM DB2; TEXT and IMAGE data types in Microsoft SQL Server, Sybase; MEMO data types in Access and FoxPro). For each <​html><​acronym title="​Large OBject">​LOB</​acronym></​html>​ value, SQLWays Wizard creates one file in the <​html><​acronym title="​Large OBject">​LOB</​acronym></​html>​ directory, specifying the file name in the ASCII file containing the table rows.
 +
 +The default value is the lob sub-directory of the export directory.
 +
 +==== Write the LOB data inside the text files ====
 +
 +This option controls the LOB data storage in the output file(s). If this option is specified the data of the LOB columns (CLOB, BLOB, TEXT, IMAGE and MEMO) is written inside the text file.
 +
 +This option influences the LOB values storage when the target databases are Oracle and IBM DB2. As the default for these databases each LOB value is written into a separate file. This eliminates the risk of misinterpreting the data during the load into Oracle using SQL Loader and IBM DB2 using IMPORT/LOAD utilities.
 +
 +This option doesn'​t influence the LOB data storage for the target databases, like: Microsoft SQL Server, Sybase, MySQL or Pervasive SQL. For these databases the LOB data is always written inside the text file, as data load utilities and commands like BCP for Microsoft SQL Server and Sybase, LOAD DATA INFILE command for MySQL and BUTIL utility for Pervasive. SQL don't have the option to load the data into one of the columns in the table from a separate file.
 +
 +\\
 +
 +If you have any other questions regarding the usage of our tool, please contact our support team: [[[email protected]]]