C# and Oracle – Importing Data (CSV)

In order to import data into an Oracle database, it must be in a suitable format. CSV files are one such format.  A CSV file, or Comma Separated Values file, is a delimited file that uses commas to separate values. They store tabular data in plain text. The first row in a CSV file often contains headers for each column of data. Each row in the file thereafter is a record of related data. In the example contents of a CSV file below, each row contains information relating to a person, including their first name, last name, title and date of birth in the format YYYY-MM-DD (four digit year, two digit month and two digit day).

"firstname","lastname","title","dob"
"Zoe","Davis","Miss","1979-07-11"
"Tom","Ingram","Mr","1971-10-04"
"Karen","Thomas","Mrs","1969-03-08"
"Samantha","Yates","Miss","1995-08-27"

Below is an example of how C# can be used to import data from a CSV file called ‘personimport.csv’, into a table called ‘person’, which was used in the examples for selecting, inserting, updating and deleting data.

Firstly, a connection to the database is established, the CSV file path is set and a check is made to see if it actually exists. If it does, it gets assigned to a reader object and the rows are processed one by one. For each row of data, the double quotes are removed and it is split at the commas into separate items within an array. For the first row, the headers are checked to see if is in the right format. For all other rows, an ‘Insert‘ statement is constructed and then used to add the new record to the database. Finally, feedback is given as to the number of records added.

// Database connection variable.
OracleConnection connect = new OracleConnection(
    "Data Source=localhost:1521/Demo;" + 
    "User Id=DemoUN; Password=DemoPW");

try
{

    // Connect to database.
    connect.Open();

}
catch (Exception e)
{

    // Confirm unsuccessful connection and stop program execution.
    Console.WriteLine("Database connection unsuccessful.");
    System.Environment.Exit(1);

}

// File path.
string filePath = @"C:\demo\personimport.csv";

// Check if the CSV file exists.
if (!File.Exists(filePath))
{

    // Message stating CSV file could not be located.
    Console.WriteLine("Could not locate the CSV file.");

    // Stop program execution.
    System.Environment.Exit(1);

}

try
{

    // Assign the CSV file to a reader object.
    using var reader = new StreamReader(filePath);

    // Test for headers.
    bool headers = true;

    // Data variables.
    string line;
    string[] currentRow;
    string sqlPersonInfo;
    OracleCommand sqlInsert;
    int recordCount = 0;

    // Process the contents of the reader object.
    while ((line = reader.ReadLine()) != null)
    {

        // Remove double quotes from the line.
        line = line.Replace("\"", "");

        // Split the line at the commas.
        currentRow = line.Split(',');

        // Check for correct column headers if first row.
        if (headers == true)
        {

            if (currentRow[0] != "firstname" ||
                currentRow[1] != "lastname" ||
                currentRow[2] != "title" ||
                currentRow[3] != "dob")
            {

                // Message stating incorrect CSV file headers.
                Console.WriteLine("Incorrect CSV file headers.");

                // Stop program execution.
                System.Environment.Exit(1);

            }
            else
            {

                headers = false;

            }

        }
        else
        {

            // Construct the insert statement.
            sqlPersonInfo = @"
                INSERT INTO person 
                    (firstname, lastname, title, dob)
                VALUES 
                    (:firstname, :lastname, :title, :dob)
            ";

            // Query text incorporated into SQL command.
            sqlInsert = new OracleCommand(sqlPersonInfo, connect);

            // Bind the parameters to the query.
            sqlInsert.Parameters.Add("firstname", currentRow[0]);
            sqlInsert.Parameters.Add("lastname", currentRow[1]);
            sqlInsert.Parameters.Add("title", currentRow[2]);
            sqlInsert.Parameters.Add("dob", Convert.ToDateTime(currentRow[3]));

            // Execute SQL.
            sqlInsert.ExecuteNonQuery();

            // Increment the record count.
            recordCount += 1;

        }

    }

    // Provide feedback on the number of records added.
    if (recordCount == 0)
    {

        Console.WriteLine("No new person records added.");

    }
    else if (recordCount == 1)
    {

        Console.WriteLine(recordCount + " person record added.");

    }
    else
    {

        Console.WriteLine(recordCount + " person records added.");

    }

}
catch (Exception e)
{

    // Confirm error adding person information and exit.
    Console.WriteLine("Error adding person information.");
    System.Environment.Exit(1);

}
finally
{

    // Close the database connection.
    connect.Close();

}

The contents of the ‘person’ table now looks as follows.

id firstname lastname title dob
1 Bob Smith Mr 1980-01-20
3 Fred Bloggs Mr 1975-05-07
4 Alan White Mr 1989-03-20
5 Fiona Bloggs Mrs 1985-05-19
6 Zoe Davis Miss 1979-07-11
7 Tom Ingram Mr 1971-10-04
8 Karen Thomas Mrs 1969-03-08
9 Samantha Yates Miss 1995-08-27

Further Resources