1. Data Management Guide
  2. CSV

CSV

CSVUtil is a utility for importing and exporting data in CSV format in and out of the Oracle Transportation Management database. CSVUtil also exports data as a script of insert statements. This document describes how to use CSVUtil and shows some sample CSV files.

CSV files are compact and enable you to import large amounts of data into Oracle Transportation Management. You typically want to use CSVUtil when importing rates into a fresh installation of Oracle Transportation Management.

Note: Updates made directly to the Transportation and Global Trade Management Cloud database by CSVUtil Import can only ensure data consistency with respect to the standard database constraints, e.g. Primary Key, Foreign Key, and Check constraints. Imports do not flow through the main application logic for updates, and so cannot check that the business context of a particular change makes sense. For example, the status of a particular object (e.g. LOCATION STATUS) can be updated. Import can only check that the status GID is valid but not that the status, possibly in association with other status values, constitutes an appropriate state for the object to be in.
Note: CSVUtil is deliberately non-transactional, and therefore may return inconsistent data if modifications to exported tables are made concurrently to the CSVUtil operation.
Note: CSV files cannot contain column values that contain line breaks (such as a multi-line XML document). Unless the database table uses the CLOB type for the column, you cannot import or export such columns in CSV format (use DB.XML format instead). If the column is of type CLOB, additional files for the CLOB column of each record can be read or written, but only when using CSVUtil in ZIP file mode through the web interface. See below for further details.

There are a few ways to use CSVUtil:

  • Via the Oracle Transportation Management web interface
  • Via integration transmissions

A Sample CSV File

Below is a sample CSV file:

ICON 
ICON_GID,ICON_XID,DESCRIPTION,PATH,DOMAIN_NAME,INSERT_USER,INSERT_DATE,UPDATE_USER,UPDATE_DATE 
EXEC SQL ALTER SESSION SET NLS_DATE_FORMAT = 'YYYYMMDDHH24MISS' 
"BATCH_GRID","BATCH_GRID","Reports Batch Grid","/images/icons/reports/batch_grid.jpg","PUBLIC","DBA.ADMIN","20040310091645","DBA.ADMIN","20040630100834"

Line 1 must be the name of the table.

Line 2 must be a comma-separated list of column names. Only the columns being loaded must be specified. Note that CSVUtil is case-sensitive with regards to column names; all column names must be in upper case if using standard database, or in mixed ("camel") case without underscores if using appserver mode (see chapter 6 for more details). Note also that if updating or deleting existing records, all columns in the primary key of the specified table must be included so the records to be updated or deleted can be precisely identified; CSVUtil does not permit "wildcard" updates or deletes using partial primary keys.

After line 2 may be one or more optional EXEC SQL lines, such as the one shown above, to set the date format.

Subsequent lines include the data. The number of columns of data must correspond to the number of columns specified on line 2. The ordering of the data columns must also correspond to line 2.

Character data may be surrounded with double-quotes, as shown above. If you need to include a double-quote character, use “"” instead. The tools described here to export CSV files automatically convert double-quote characters into “"”.

Numeric data should not be surrounded with double-quotes.

Multi-table CSV Files

The output produced by the xcsvw* commands is in multi-table CSV format. The various CSV import commands recognize this format also:

  • The first record in a multi-format file must be "$HEADER".
  • The header section contains table names and the names of the columns used in that table.
  • After the header section comes the body, identified by the $BODY keyword.
  • Each data record in the $BODY must be preceded by its table name on the prior line.

Here is an example:

$HEADER
LOCATION_ROLE_PROFILE
LOCATION_GID,LOCATION_ROLE_GID,CALENDAR_GID,FIXED_STOP_TIME, etc...
LOCATION_STATUS
LOCATION_GID,STATUS_TYPE_GID,STATUS_VALUE_GID,DOMAIN_NAME,INSERT_USER,INSERT_DATE,UPDATE_USER,UPDATE_DATE
LOCATION_CORPORATION
LOCATION_GID,CORPORATION_GID,DOMAIN_NAME,INSERT_DATE,UPDATE_DATE,INSERT_USER,UPDATE_USER
LOCATION_ADDRESS
LOCATION_GID,LINE_SEQUENCE,ADDRESS_LINE,DOMAIN_NAME,INSERT_USER,INSERT_DATE,UPDATE_USER,UPDATE_DATE
LOCATION_REFNUM
LOCATION_GID,LOCATION_REFNUM_QUAL_GID,LOCATION_REFNUM_VALUE,DOMAIN_NAME,INSERT_DATE, etc...
LOCATION
LOCATION_GID,LOCATION_XID,LOCATION_NAME,ADDRESS_LINE1,ADDRESS_LINE2,CITY,etc.
EXEC SQL ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS..'
$BODY
LOCATION
"GUEST.00621918","00621918","00621918",,,,,"TN",,"USA",,,,,"America/New_York",,,,,,,,"N","N","COMMERCIAL",,,"GUEST","S",0,...etc
LOCATION_ADDRESS
"GUEST.00621918",1,,"GUEST","DBA.ADMIN",2001-10-07 17:53:53.0,,
LOCATION_ADDRESS
"GUEST.00621918",2,,"GUEST","DBA.ADMIN",2001-10-07 17:53:53.0,,
LOCATION_CORPORATION
"GUEST.00621918","GUEST.CUST NO","GUEST",2001-10-15 10:50:49.0,,"DBA.ADMIN",
LOCATION_REFNUM
"GUEST.00621918","GLOG","GUEST.00621918","GUEST",2001-10-25 17:13:48.0,2001-10-19 18:23:17.0,"DBA.ADMIN","DBA.GLOGOWNER"
LOCATION_ROLE_PROFILE
"GUEST.00621918","SHIPFROM/SHIPTO",,0,0,"GUEST","S",0,"S",0,"N",,,,,,,,,,2001-10-25 14:12:38.0,2002-08-28 19:13:05.0,"DBA.ADMIN", etc.
LOCATION_STATUS
"GUEST.00621918","GUEST.CREDIT LEVEL","GUEST.CREDIT LEVEL_UNKNOWN","GUEST","DBA.GLOGOWNER",2001-10-17 09:38:05.0,,