1. Data Management Guide
  2. Importing

Importing

This section describes how to import a CSV file using the Oracle Transportation Management user interface.
Note: There are limits on importing CSVs. When importing via the user interface, there is a 50MB limit. However, when importing via integration, there is a 100MB limit.
  1. Log in to Oracle Transportation Management.
  2. Choose Business Process Automation > Integration > Integration Manager.
  3. Click Upload an XML / CSV Transmission.
  4. Select the file to upload. The upload will transfer files from your local machine to the server.
    Note: You must select a .CSV file. This .CSV file cannot make reference to external files for CLOB columns. If you need to use external CLOB files, use the ZIP file format described in chapters 8 (Loading CSV Files as Zip Files) and 9 (Exporting CSV Files Via the Interface).
  5. Click Upload and Oracle Transportation Management displays the page for importing the file. If you select a file other than a .CSV file, a different page will open.
  6. If it is not already selected, select i from the command list.
  7. Leave the dataDir as is.
  8. Leave the dataFileName as is.
  9. The xvalidate drop-down list allows you to turn off verbose diagnostic messaging. To leave messaging on, the value in the drop down list should be Y, which is the default.
  10. In the encoding drop down list, select the appropriate encoding type for your CSV file. If your file contains standard ASCII characters, then it can be encoded as ISO-8859-1. If it contains non-standard, international characters, then it should be encoded as UTF-8.
  11. Click Run and Oracle Transportation Management displays a results page.

To read more about interpreting error messages, see the Reference C: CSVUtil Response Messages section.

CLobs in CSV Files

CSVUtil supports inserting, updating, and deleting CLobs. You can:

  • Include the CLob in the CSV file (each CLob<1Mb, no newline characters or quotes)
  • In the importing CSV file, refer to an external file holding the CLob. (no size restrictions on the CLobs, newline characters allowed). Note that CLOB columns are always exported to external files.
Note: CSVUtil can only handle one CLob per record, and not when using the runsqlloader option. Also, the DB column must be of type CLOB; this will not work if the column type is VARCHAR or VARCHAR2, no matter how large the field is allowed to be.

Here is a sample CSV file that inserts a CLob using the in-line method:

CLOB_TEST
SEQ,DESCR,XML
9,"LINE1",<test1>test1</test1>
10,"LINE2",<test2>test2</test2>

In this case, the "XML" column is of type CLob. When using the in-line method, each CLob:

  • Must be specified on a single line (no newline characters).
  • Must be smaller than 1 megabyte.

Here is a sample CSV file that inserts two CLobs using the external file method:

CLOB_TEST
SEQ,DESCR,EXT_FNAME,XML
11,"LINE1",myxmlfile.xml
12,"LINE2",myxmlfile2.xml

When using the external file method, you must specify a special "pseudo column" called "EXT_FNAME ". The EXT_FNAME pseudo column must be specified immediately to the left of the CLob column. In this case, you will have an extra column on line 2. So in this case, line 2 has 4 columns, but there are only 3 columns in the data lines.

The external file method must be used when inserting CLobs containing newline characters, or when inserting CLobs greater than 1 megabyte.

Note: To include the external files with the main CSV file, the ZIP encapsulation (described in more detail below) must be used so all the files may delivered to the Oracle Transportation Management server in a single HTTP transfer.

Exporting with Parent Data

To export a data record with its parent data, you can specify the xcsvwpd command. This command exports the specified records, along with all the referenced non-public foreign key records associated with the specified records. The generated CSV file is in multi-table format.

Note: All the xcsvw* commands are far more expensive in terms of CPU usage than the plain xcsv command. Using them to export a large data set will take a long time, since many foreign keys must be found. Use the commands with a restrictive where-clause, as shown in the examples, to limit the running time.

Exporting with Child Data

To export a data record with its child data, you can specify the xcsvwcd command. This command exports the specified records, along with all the subordinate child table records corresponding to the selected records.

Exporting with both Parent and Child Data

To export a data record with both its parent and child data, you can specify the xcsvwpcd command. This command should be used with care since it can take a while to run.

GL_User Table

CSVUtil supports adding and deleting records in the GL_USER table. This table stores the Oracle Transportation Management users and their passwords.

When the GL_USER table is specified in the header of a CSV file, special processing is done.

If you are a Transportation and Global Trade Management Cloud user authorized to directly access the GL_USER table, you may add and delete records in the GL_USER table. As an exception for this table, you can only use the commands: i, ii, d, or dd.

Note: The u, uu, and iu commands are not supported when loading the GL_USER table.