What type of flat file contains plain text with delimiters to set apart the columns?
-type Possible values: Show Defines the type of the input file. This is only needed if the input file has a non-standard file extensions. If this parameter is not specified, the import type is derived from the input file's extension. This parameter supports code-completion. If invoked, it will only display available import types. -mode Defines how the data should be sent to the database. Possible values are ' For some DBMS, the additional modes: ' This parameter supports code-completion. -file Defines the full name of the input file. Alternatively you can also specify a directory (using Defines the table into which the data should be imported This parameter is ignored, if the files are imported using the This parameter supports code-completion. -sourceDir Defines a directory which contains import files. All files from that directory will be imported. If this switch is used with text files and no target table is specified, then it is assumed that each filename (without the extension) defines the target table. If a target table is specified using the When using the If the file names imported with from the directory specified with -sourceDir contain the owner (schema) information, this owner (schema) information can be ignored using this parameter. Otherwise the files might be imported into a wrong schema, or the target tables will not be found. -excludeFiles Using -excludeFiles, files from the source directory (when using -sourceDir) can be excluded from the import. The value for this parameter is a comma separated list of partial names. Each file that contains at least one of the values supplied in this parameter is ignored. Normally However, When importing more than one file (using the If your DBMS neeeds frequent commits to improve performance and reduce locking on the import table you can control the number of rows after which a COMMIT is sent to the server. When using
batch import and your DBMS requires frequent commits to improve import performance, the You can turn off the use of a commit or rollback during import completely by using the option Using Possible values: Controls if SQL Workbench/J handles the transaction for the import, or if the import must be committed (or rolled back) manually. If Possible values: This parameter controls the behavior when errors occur during the import. The default is The default value for this parameter can be controlled in the
settings file and it will be displayed if you run With PostgreSQL Possible values: This parameter controls the behavior when an empty file (i.e. with a length of zero bytes) is used for the input file. The default value is Possible values: Controls if SQL Workbench/J guards every insert or update statement with a savepoint to recover from individual error during import, when Using a savepoint for each DML statement can drastically reduce the performance of the import. -keyColumns Defines the key columns for the target table. This parameter is only necessary if import is running in It is assumed that the values for the key columns will never be This parameter is ignored if files are imported using the Possible values: Controls if identity or auto-increment columns will be included in the import. If this is used, the JDBC driver must correctly report the column to be excluded as an AUTOINCREMENT column. This can be verified in the table definition display of the DbExplorer. If the column is reported with Possible values: Controls the option to override identity columns, for DBMS that support the standard SQL's The option No check is done, if the target DBMS supports the option. The default is, to not use any overriding option. -schema Defines the schema into which the data should be imported. This is necessary for DBMS that support schemas, and you want to import the data into a different schema, then the current one. -encodingDefines the encoding of the input file (and possible CLOB files) If code-completion is invoked for this parameter, it will show a list of encodings defined through the configuration property Possible values: If this parameter is set to true, data from the target table will be deleted (using This parameter is ignored for spreadsheet imports. -truncateTablePossible values: This is essentially the same as A numeric value that defines the size of the batch queue. Any value greater than 1 will enable batch mode. If the JDBC driver supports this, the INSERT (or UPDATE) performance can be increased drastically. This parameter will be ignored if the driver does not support batch updates or if the mode is not Possible values: If using batch execution (by specifying a batch size using the When you specify When using update mode an additional A numeric value to define the first row to be imported. Any row before the specified row will be ignored. The header row is not counted to determine the row number. For a text file with a header row, the physical line 2 is row 1 (one) for this parameter. When importing text files, empty lines in the input file are silently ignored and do not add to the count of rows for this parameter. So if your input file has two lines to be ignored, then one empty line and then another
line to be ignored, -startRow=10 and -endRow=20 11 rows will be imported (i.e. rows 10 to 20). If this is a text file import with a header row, this would correspond to the physical lines 11 to 21 in the input file as the header row is not counted.
-columnFilter
This defines a filter on column level that selects only certain rows from the input file to be sent to the database. The filter has to be defined as This parameter is ignored when the If If a file with that name exists it will be deleted when the import for the table is started. The file will not be created unless at least one record is rejected during the import. The file will be created with the same encoding as indicated for the input file(s). -maxLength With the parameter The parameter defines the maximum length for certain columns using the following format: Possible values: In case you are importing a boolean column (containing "true", "false") into a numeric column in the target DBMS, SQL Workbench/J will automatically convert the literal To store different values than 0/1 in the target column, use the parameters These parameters control the conversion of boolean literals into numbers. If these parameters are used, any text input that is identified as a "false" literal, will be stored with the number specified with To use -1 for false and 1 for true, use the following parameters: These parameters can be combined with Please note:
These parameters control the conversion of boolean literals into boolean values. These two switches define the text values that represent the (boolean) values The value to these switches is a comma separated list of literals that should be treated as the specified value, e.g.: Please note:
The format for date columns. This is only used for text imports and when using -stringDates=true with spreadsheet imports -timestampFormatThe format for datetime (or timestamp) columns in the input file. This is only used for text imports and when using -stringDates=true with spreadsheet imports -localeThe locale (language) to be used for parsing date and timestamp values. This is only used for text imports and spreadsheet imports using -stringDates=true This parameter supports code-completion. -illegalDateIsNull If this is set to This is only used for text and spreadsheet imports -trimValuesPossible values: Controls whether leading and trailing whitespace are removed from the input values before they are stored in the database. When used in combination with The default value for this parameter can be controlled in the
settings file and it will be displayed if you run Note that, input values for non character columns (such as numbers or date columns) are always trimmed before converting them to their target datatype. This is only used for text and spreadsheet imports -emptyStringIsNullPossible values: Controls whether input values for character type columns with a length of zero are treated as The default value for this parameter is Note that, input values for non character columns (such as numbers or date columns) that are empty or consist only of whitespace will always be treated as This is only used for text and spreadsheet imports This parameter can be used to define SQL expressions to be used instead of a plain column reference for the INSERT statement. This is useful to apply SQL functions directly on the server to the value retrieved from the input file. The format is The position of the input value is denoted by a question mark. The question mark must not be quoted, even if the input is a string/character value. As an example, this can be used to convert a comma separated string into a Postgres array: With this parameter you can supply constant values for one or more columns that will be used when inserting new rows into the database. The constant values will only be used when inserting rows (e.g. using The format of the values is To specify a function call to be executed, enclose the function call in You can also specify a The syntax to specify a SELECT statement is similar to a function call: The parameter for the SELECT statement do not need to be quoted as internally a prepared statement is used. However the values in the input file must be convertible by the JDBC driver. If the input column from the source file is not part of the target table, the value will be
passed as a string to the statement. This means that the SQL query should cast the parameter to the appropriate data type if needed, e.g. In addition to the function call or The following three variables are supported
Please refer to the examples for more details on the usage. -insertSQLDefine the statement to be used for inserting rows. This can be used to use hints or customize the generated INSERT statement. The parameter may only contain the
-insertSQL='INSERT /*+ append */ INTO'-adjustSequences Possible values: For DBMS that support sequences which are associated with a column, this parameter can be used to adjust the next value for the sequence to the maximum value of the imported data. This can also be used to synchronize identity columns for DBMS that allow overriding the generated values. Currently this is implemented for PostgreSQL, DB2 (LUW), H2 Database and HyperSQL (aka HSQLDB). -preTableStatement -postTableStatement This parameter defines a SQL statement that should be executed before the import process starts inserting data into the target table. The name of the current table (when e.g. importing a whole directory) can be referenced using To define a statement that should be executed after all rows have been inserted and have been committed, you can use the These parameters can e.g. be used to enable identity insert for MS SQL Server: -preTableStatement="set identity_insert ${table.name} on" -postTableStatement="set identity_insert ${table.name} off" Errors resulting from executing these statements will be ignored. If you want to abort the import in that case you can specify These statements are only used if more than one table is processed. -runTableStatementOnErrorPossible values: Controls the execution of the post-table statement in case an error occurred while importing the data. By default the post-table statement is executed even if the import was not successful. If this is should not happen, use Possible values: Controls handling of errors for the SQL statements defined through the Valid values: Control the update frequence in the status bar (when running in GUI mode). The default is every 10th row is reported. To disable the display of the progress specify a value of 0 (zero) or the value What are the 4 types of database?A relational database management system is one of four common types of systems you can use to manage your business data.. hierarchical database systems.. network database systems.. object-oriented database systems.. What is flat file database and relational database?Flat file database is used to store data in a single table structure. Relational database is used to store data in a multiple-table structure. Flat file database can be accessed by a variety of software applications. Relational database uses a Relational Database Management System (RDBMS) to access the data.
Which of the following is an example of a flat file format?One of the most common flat file examples is a comma-separated values (CSV) file. In a CSV file, table data is represented by lines of ASCII text.
What is flat table?A Flat Table stores data that is not unique but still relevant to data in another table, where the same data may be used for many contacts. Flat Tables eliminate the potential for redundant data. For example, a list of songs that may appear on multiple play lists, vendor information, or product details.
|