New features added, version 7.5.2, 7.5.3, 8.1
Program UTCV, Delimited File Manager, is used to define the relationship between single database table and text file by allowing users to import and export data to and from text files (.csv).
This program supports the import of Date, Number, and Varchar2 data types only. Varchar2 columns are limited to 1000 characters. Default values can be specified for database columns that have no corresponding source in the data file. These should have the relative position left null. Default values will also replace null values from the data file. Format masks are supported for converting date data types. A variety of separators are also supported.
Columns that do not participate in a unique index will have their values updated in place from the imported record. This will only be done where the table has a primary key or single unique index.
It is recommended that you use the [Default Mapping] button to have the program automatically enter the columns in the table. Then you can modify or delete the column records that will be imported and exported, as desired.
If you receive an ora-06502 error when trying to import a csv file, make sure that the columns are mapped in the same order as the data in the csv file.
Set up for Delimited Files in NDS
Importing Data from Text Files
Copying Existing CSV Definitions
When using program UTCV, Delimited File Manager, on the web version of NDS Applications, files are imported from and exported to a directory on the web server or client machine.
For files imported from the server, this directory corresponds to the name of an Oracle named directory and is entered in the Directory Name field. A list of values of all Oracle directories is provided. The File Name field in this case should not contain the directory path information, only the file name and extension. Be sure to set the File Located On pop-list to Database Server.
* Oracle named directories provide an alias for the path on the server to the directory so that users don't have to know the actual path to the files. You can have as many Oracle directories as you need. They simply need to be created by the database administrator and have access granted to the users.
* Oracle named directories replace the use of the utl_file_dir parameter in the init.ora file.
* At minimum, installations should have an Oracle directory named UTL_FILE_DIR as some applications expect to find this. The load_utl_file_dir.sql script sets the directory name to 'UTL_FILE_DIR' for any mappings in this program that have the File Located On pop-list set to Database Server. This script must be run by the database administrator as SYSTEM to create the 'UTL_FILE_DIR' directory. If any additional directories are required, the database administrator can create them using the create_oracle_dir.sql script.
For files imported from the client machine, the Directory Name field should remain blank, and the path to the file should be entered in the File Name field, along with the file name and extension. Be sure to set the File Located On pop-list to Client Machine.
The following rules must be adhered to when importing/exporting fixed files.
* Select None from the Delimiter Char pop-list.
* All columns must have At and Thru positions entered.
* When exporting files in this format, all character columns are right padded with spaces to the column length, all numeric columns are left padded with zeroes to the column length, and all date columns are left padded with spaces to the column length.
* You cannot use the [Default Mapping] button when you are using the fixed file format. To get around this, you can initially set the Delimiter Char pop-list to Comma, and press the button. Then change it to None and enter the position information.
1. In the CSV Map ID, enter a user-defined identifier for the import of the data file.
2. In the Name field, enter a descriptive name for the import.
3. In the Table Name field, enter the system table name into which you are importing the data. The list of values is available.
4. Set the Delimiter Char pop-list to the desired setting. This is the character in the data file that separates the columns. Choices are as follows:
* , Comma
* * Asterisk
* ~ Tilde
* + Plus
* - Hyphen
* = Equal
* ^ Caret
* : Colon
* ; Semicolon
* # Pound
* At
* ! Exclamation
* ' Apostrophe
* _ Underscore
* & Ampersand
* $ Dollar
* None (fixed position)
5. In the File located on pop-list, select whether the file is located on your client machine or the database server. This will determine where the browse starts when you click on the […] button to find the file.
6. In the Directory Name field, if the File located on pop-list is set to Database Server, enter the Oracle named directory from which you are importing the file.
* A list of values is provided. If the list of values is blank, contact your database administrator as some set up is required. See the Set up for Delimited Files in NDS section for more information about Oracle named directories.
* If the File located on pop-list is set to Client Machine, leave this field blank.
7. In the File Name field, the entry depends on whether you are importing from the Client Machine or the Database Server. Any data file type can be imported.
* If the File located on pop-list is set to Client Machine, enter the directory and file name into the field.
You can type the desired directory and file name into the field, such as C:\ndslocalfiles\import.csv
The directory from which you are importing the file cannot have spaces in the directory name.
You can use the [...] to find the data file on your computer or network. Press the button, find the desired file, and press [Open]. The program will enter the directory and file in the field.
* If the File located on pop-list is set to Database server, enter only the file name and its extension into the field. The Directory Name field is used to determine from which Oracle named directory the file will be imported.
8. Set the Usage pop-list to the desired setting. Choices are:
* General: The record can be used for imports or exports..
* Import: The record can be used for imports only.
* Export: The record can be used for exports only.
* Template: The record is incomplete. Templates can be used with the [Copy Definition] button as a starting point for the import and export of data files.
9. In the Commit Interval field, enter the number of records that will be imported between commits.
* The default is 1000 records. This is the recommended number for optimum performance during imports. Reducing this number will adversely affect performance, and increasing the number may require more rollback space than you have available.
10. Press [Commit], and go to the lower block.
11. In the Column Name field, enter the column name that you are importing from the text file. The list of values is available.
* You can press the [Default Mapping] button to have the program automatically enter the columns for the table that you entered in the header. Changes can then be made to the column records entered in the lower block. Existing column details will be overwritten.
12. The Datatype and Length fields will be entered automatically from the column parameters.
* Any values that are longer than the length displayed here are truncated to the correct length upon import.
13. For date fields, enter the Date Format Mask to be applied to the dates when importing.
* Any valid Oracle date format can be entered here, and will be validated by the program.
14. Set the Case pop-list to the desired setting.
* This tells the import whether to perform a case conversion when the column is imported. If the case in the data file is different from the case defined here, the case will be converted before the value is stored in the table. This is very important for key fields in the table.
15. The Default Value field can be entered. Default values can be specified for database columns that have no corresponding source in the data file. These columns should have the relative position left null when entered in the lower block. Default values will also replace null values from the data file.
16. Enter the Relative Position in the file in the At field. This is the place in the text file from which information for this column will be pulled.
* For example, if the column has a relative position of 1, the information to be entered in this column is in the first column of the text file.
17. If several columns in the file will be entered in this one column in the NDS table, in the At field, enter the first column in the range. In the Thru field, enter the last column in the range that will be included in a single column in the NDS table.
* For example, if three columns in the file (2, 3, 4) were to be combined into this one column in the NDS Table, you would enter 2 in the At field and 4 in the Thru field.
18. In the Value Subquery field, you can enter a SQL query that returns a single value during the import of the data file. The SQL query is most commonly used to obtain a default value from system tables, generate line numbers, or get key values from the auto numbering function call.
19. Set the Keep Delimiter flag to the desired setting. This flag determines whether the delimiter character will be preserved in the column when multiple columns are imported into the same column in the NDS table.
* This flag is primarily intended for special applications such as importing lead product selections.
* If this flag is set to on, the delimiter character will be preserved.
* If this flag is set to off, the delimiter character will not be preserved.
20. When you have completed entering all columns that will be imported, press the [Import From File] button.
21. Set the Clear Table Before Loading flag to the desired setting.
* If this flag is set to on, then all records in the table will be deleted before the new records are loaded from the text file.
* If this flag is set to off, then the records in the text file will be added to the existing records in the table.
22. Set the Retain Custom Entries to the desired setting, for target tables that contain a CUSTOM_FLG column.
* If this flag is set to on, records with the custom flag set to on will not be deleted from the target table when the data file is imported.
* If this flag is set to off, records with the custom flag set to on will be deleted from the target table when the data file is imported.
23. Set the Skip # Remarks flag to the desired setting.
* If this flag is set to on, and there are records in the data file that begin with a #, they will not be imported.
* If this flag is set to off, and there are records in the data file that begin with a pound sign #, they will be imported.
24. Set the Ignore Duplicate Records flag to the desired setting. When this flag is set to on, no update will be attempted if a duplicate key is detected.
25. Press the [Import] button.
26. The file has been imported into the table.
1. Enter the CSV Map ID. This is a user-defined identifier for the export of the data file.
2. In the Name field, enter a descriptive name for the export.
3. In the Table Name field, enter the system table name from which you are exporting the data. The list of values is available.
4. Set the Delimiter Char pop-list to the desired setting. This is the character in the data file that separates the columns. Choices are as follows:
* , Comma
* * Asterisk
* ~ Tilde
* + Plus
* - Hyphen
* = Equal
* ^ Caret
* : Colon
* ; Semicolon
* # Pound
* At
* ! Exclamation
* ' Apostrophe
* _ Underscore
* & Ampersand
* $ Dollar
5. In the Directory Name field, if the File location on pop-list is set to Database Server, enter the Oracle named directory to which you are exporting the file.
* A list of values is provided. If the list of values is blank, contact your database administrator as some set up is required. See the Set up for Delimited Files in Web NDS section for more information about Oracle named directories.
* If the File location on pop-list is set to Client Machine, leave this field blank.
6. In the File Name field, the entry depends on whether you are exporting to the Client Machine or the Database Server. Any data file type can be exported.
* If the File located on pop-list is set to Client Machine, enter the directory and file name into the field.
You can type the desired directory and file name into the field, such as C:\ndslocalfiles\export.csv
The directory to which you will export the file can not have spaces in the directory name.
You can use the [...] to find the data file on your computer or network. Press the button, find the desired file, and press [Open]. The program will enter the directory and file in the field.
* If the File located on pop-list is set to Database server, enter only the file name and its extension into the field. The Directory Name field is used to determine to which Oracle named directory the file will be exported.
7. Set the Usage pop-list to the desired setting. Choices are:
* General: The record can be used for imports or exports..
* Import: The record can be used for imports only.
* Export: The record can be used for exports only.
* Template: The record is incomplete. Templates can be used with the [Copy Definition] button as a starting point for the import and export of data files.
8. In the Export WHERE Clause field, enter the WHERE clause to restrict the exported information.
* For example, if you were exporting zip codes, and you only wanted to export the zip codes for Florida, you could enter where state_prov_id = 'FL' and only those zip codes would be written to the .csv file.
* The where clause must contain an order by statement, such as 'order by 1'. Otherwise an error may occur when exporting.
9. Press [Commit], and go to the lower block.
10. In the Column Name field, enter the column name that you are exporting to the data file. The list of values is available.
* You can press the [Default Mapping] button to have the program automatically enter the columns for the table that you entered in the header. Changes can then be made to the column records entered in the lower block. Existing column details will be overwritten.
11. The Datatype and Length fields will be entered automatically from the column parameters.
12. For date fields, enter the Date Format Mask to be applied to the dates when exporting.
* Any valid Oracle date format can be entered here, and will be validated by the program.
13. Enter the Relative Position in the file in the At field. This is the place in the text file that this information will be entered.
* For example, position 1 will be entered in the text file first. Position 2 will be entered after position 1, separated by the Delimiter Character defined in the header.
14. When you have completed entering all columns that will be exported, press the [Export To File] button.
15. Set the Write Custom Entries flag to the desired setting for tables with a CUSTOM_FLG column.
* If this flag is set to on, then records with the custom flag set to on will be exported to the data file.
* If this flag is set to off, then records will the custom flag set to on will not be exported to the data file.
16. Set the Write Remarks flag to the desired setting.
* If this flag is set to on, the export will write the Remarks comment text preceded by pound sign # and a timestamp as the first record of the output file.
* If this flag is set to off, no comment text will be written.
17. Enter any remarks.
18. Press the [Export Button].
19. The file has been created in the defined directory.
1. Press the [Copy Definition] button.
2. In the Copy From field, enter the CSV Map ID from which you are creating a new definition.
3. In the Copy To field, enter the CSV Map ID for the new definition.
4. Press [Copy].
5. The new CSV Map ID will be created with the Usage flag set to General.
Press this button to select the directory and data file that will be imported or exported.
Press this button to create the new CSV Map ID from the existing CSV Map ID.
Press this button to enter the copy from and to information for copying existing CSV Map ID's to new CSV Map ID's.
Press this button to have the program automatically enter the columns from the table entered in the header.
Press this button to export the table to the data file according to the parameters entered.
Press this button to enter the parameters to export the table to the data file.
Press this button to import the data file according to the parameters entered.
Press this button to enter the parameters to import from the data file.