Import CSV Data

In this blog post, we will look at a simple application to import sales order data from CSV files into the demo database. The demo application contains PL/SQL packaged functions that open the CSV file, load the CSV data into a staging table, extract fields from the delimited CSV records, perform validation, log error messages, insert data into the order tables, and move the CSV files that have been processed.

The following guide provides instructions on how to install Oracle Express, install the demo application, and run the order CSV import process.

Read Me Read Me    

Sales Order Import

You can view the documentation for the order import application by clicking on the links below:

Import Spec Order Import Technical Specification     Import User Guide Order Import User Guide    

The requirements for our sales order data import process are as follows:

  1. The sales order data is received in CSV format text files.
  2. The files are named with the prefix ‘order’ and extension ‘csv’.
  3. Each file must have a unique name.
  4. The process must be automated to run every hour, and import all new order CSV files received.
  5. The data must be validated, and if there are any errors, the entire CSV file must be rejected, moved to an ‘Error’ directory, and all errors reported.
  6. A manual procedure is required to fix rejected files.
  7. If the data passes validation, load the data into the order tables.
  8. Move successfully imported files to a ‘Processed’ archive directory.
  9. Delete old error messages relating to the orders that have been successfully imported.

Fixing rejected files

  1. Manual intervention is required.
  2. An import error report will show the following information for each rejected file:
    • Filename.
    • A key value that uniquely identifies the order: Order Reference.
    • CSV data record (all fields).
    • An error message identifying the invalid data.
    • Date and time error reported.
  3. Locate the rejected CSV file in the Error directory, via the filename on the error report.
  4. Edit the CSV file, and manually correct each error reported.
    • Order Reference may be altered, but you must check that there are no existing orders with the same reference.
    • Dates must be in the format DD/MM/YYYY.
    • Ship Date must be on or later than the Order Date, in the format DD/MM/YYYY.
    • Customer ID invalid: contact the sales department to either obtain the correct code, or have a new account created.
    • Product ID invalid: contact the sales department for the correct code.
  5. Move the corrected file to the ‘Received’ directory to be re-processed.

Example CSV File

The order data is received in CSV files, similar to the following example:

"Ord Ref","Order Date","Comm","Customer","Ship Date","Product","Qty"

The first row of the CSV file is the header, containing a list of field names. The body of the file contains the order data, with a row for each order line. Each CSV file can contain one or more orders, each order having one or more lines.

Field No Name Data Type Size Description
1 Ord Ref Char 10 Order Reference. Maximum length 10 characters. Must be unique per order.
2 Order Date Date 10 Date format DD/MM/YYYY.
3 Commission Char 1 A to Z, may be NULL.
4 Customer ID Number 6 Must exist on the Customer table.
5 Ship Date Date 10 Date format DD/MM/YYYY. Must be on or later than the Order Date.
6 Product ID Number 6 Must exist on the Product table.
7 Qty Number 8 Quantity of product ordered. Must be a valid whole number > 0, and <= 99999999.


The following Directory objects have been created in the database, to allow the applications to access the CSV files, process, and move them.

Directory Name Path


The application consists of the following program modules:

Module Name Description
IMPORT_ORDER.BAT A DOS script that finds order CSV files, moves each file in turn to the DATA_IN directory, and runs the SQL*Plus script IMPORT_ORDER.SQL to process each file.
IMPORT_ORDER.SQL SQL script that calls a PL/SQL package function to validate, and import the CSV data.
IMPORT.ORD_IMP PL/SQL package function. Validates the CSV data. Records errors in the IMPORTERROR table. If there are no errors, the data is imported into the order tables of the database, and the CSV file is moved to DATA_IN_PROCESSED. If errors are found the file is moved to DATA_IN_ERROR.



This is a Windows Batch Script that does the following:

Search the DATA_HOME\RECEIVED directory for CSV files containing order data. For each CSV file found with the name ‘order*.csv’:

  • Copy the CSV file to the DATA_IN import directory.
  • Run PL/SQL: execute script IMPORT_ORDER.SQL passing filename.
  • Delete the CSV file from the received directory.

REM Set the application environment variables
CALL ..\config\SET_ENV

    REM Copy the csv to the data import directory

    REM Execute the sqlplus script to load the data

    REM Tidy up - delete the csv file from the received directory
    DEL "%%F"



This SQL script calls the PL/SQL package function IMPORT.ORD_IMP, passing the CSV filename.

    v_filename VARCHAR2(100) := '&1';
    v_result BOOLEAN;
    util_admin.log_message('Order Data Import from file: '||v_filename);
    v_result := import.ord_imp(v_filename);
    IF v_result THEN
        raise_application_error (-20099,'Order import failed. View errors in IMPORTERROR for file '||v_filename);
    END IF;
        util_admin.log_message('Error importing file ' || v_filename,SQLERRM,'IMPORT_ORDER.SQL','B','E');


PL/SQL Package: import.sql

The ord_imp PL/SQL function, in the import package, loads the CSV data into a staging table, validates the data, and loads valid data into the order tables. If validation fails, the errors are recorded, the CSV file is rejected, and no data is loaded into the order tables.

There are two functions that are specific to the order import process: ord_imp, and ord_valid.

The following generic functions are used by the import process. These functions can be re-used when creating additional data import processes.

Package Function/Procedure Description
import delete_error Delete old error messages for orders that have been successfully imported.
import import_error Record validation error message on IMPORTERROR table.
util_file load_csv Load CSV file data into the IMPORTCSV table.
util_file delete_csv Delete rows from IMPORTCSV for each CSV file that has been processed.
util_file rename_file Rename the CSV file by moving it to directory DATA_IN_PROCESSED if order imported, or DATA_IN_ERROR if it failed validation.
util_string get_field Extract Nth field from a delimited string (the CSV record).
util_admin log_message Record errors in the application log table APPLOG.
orderrp currentprice Finds the current price for the specified product.

The ord_imp function processes the CSV data as follows:

  1. Call the package function UTIL_FILE.LOAD_CSV to load order data from a CSV file into the IMPORTCSV staging table. The load_csv function returns an integer FILEID, which identifies the group of records loaded from the CSV file into the staging table.

  2. If the file was not found, report error and stop processing.

  3. Validate the data in IMPORTCSV matching FILEID.
    1. Set column KEY_VALUE in table IMPORTCSV to a unique value, that identifies each order, in this case it will be the first field in the CSV file, ORDREF.
    2. Record all validation errors found in the IMPORTERROR table, including the KEY_VALUE field.

  4. If data fails validation:
    1. Delete the data from the IMPORTCSV staging table.
    2. Move the CSV file to the error directory.
    3. Stop processing, exit with an error status.

  5. If data passes validation:
    1. Insert data into the ORD and ITEM tables.
    2. Delete old error messages from the IMPORTERROR table for the orders successfully imported, using the KEY_VALUE column of IMPORTCSV.
    3. Delete the data from the IMPORTCSV staging table.
    4. Move the CSV file to the processed directory.
    5. Exit with a success status.

In the next blog post, we will demonstrate a PL/SQL application that exports data from the database to a CSV file.

Previous Blog Next Blog