how to import csv data into Oracle using c#

Hello,
How to import csv data into Oracle using c #. Where data to be imported 3GB in size and number of rows 7512263. I've managed to import csv data into Oracle, but the time it takes about 1 hour. How to speed up the time it takes to import csv data into oracle. Thank you.

This is my code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Threading;
using System.Text.RegularExpressions;
using System.IO;
using FileHelpers;
using System.Data.OracleClient;

namespace sqlloader
{
    class Program
    {

        static void Main(string[] args)
        {
            int jum;
            int i;
            bool isFirstLine = false;
            FileHelperEngine engine = new FileHelperEngine(typeof(XL_XDR));

            //Connect To Database
            string constr = "Data Source=(DESCRIPTION=(ADDRESS_LIST="
                 + "(ADDRESS=(PROTOCOL=TCP)(HOST= pt-9a84825594af )(PORT=1521 )))"
                 + "(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=o11g)));"
                 + "User Id=xl;Password=rahasia;";
            OracleConnection con = new OracleConnection(constr);
            con.Open();

            // To Read Use:
            XL_XDR[] res = engine.ReadFile("DataOut.csv") as XL_XDR[];

            jum = CountLinesInFile("DataOut.csv");

            FileInfo f2 = new FileInfo("DataOut.csv");
            long s2 = f2.Length;
            int jmlRecord = jum - 1;

            for (i = 0; i < jum; i++)
            {
                ShowPercentProgress("Processing...", i, jum);
                Thread.Sleep(100);

                if (isFirstLine == false)
                {
                    isFirstLine = true;
                }
                else
                {
                    string sql = "INSERT INTO XL_XDR (XDR_ID, XDR_TYPE, SESSION_START_TIME, SESSION_END_TIME, SESSION_LAST_UPDATE_TIME, " +
                                 "SESSION_FLAG, VERSION, CONNECTION_ROW_COUNT, ERROR_CODE, METHOD, HOST_LEN, HOST, URL_LEN, URL, CONNECTION_START_TIME, " +
                                 "CONNECTION_LAST_UPDATE_TIME, CONNECTION_FLAG, CONNECTION_ID, TOTAL_EVENT_COUNT, TUNNEL_PAIR_ID, RESPONSIVENESS_TYPE, " +
                                 "CLIENT_PORT, PAYLOAD_TYPE, VIRTUAL_TYPE, VID_CLIENT, VID_SERVER, CLIENT_ADDR, SERVER_ADDR, CLIENT_TUNNEL_ADDR, " +
                                 "SERVER_TUNNEL_ADDR, ERROR_CODE_2, IPID, C2S_PKTS, C2S_OCTETS, S2C_PKTS, S2C_OCTETS, NUM_SUCC_TRANS, CONNECT_TIME, " +
                                 "TOTAL_RESP, TIMEOUTS, RETRIES, RAI, TCP_SYNS, TCP_SYN_ACKS, TCP_SYN_RESETS, TCP_SYN_FINS, EVENT_TYPE, FLAGS, TIME_STAMP, " +
                                 "EVENT_ID, EVENT_CODE) VALUES (" +
                                 "'" + res[i].XDR_ID + "', '" + res[i].XDR_TYPE + "', '" + res[i].SESSION_START_TIME + "', '" + res[i].SESSION_END_TIME + "', " +
                                 "'" + res[i].SESSION_LAST_UPDATE_TIME + "', '" + res[i].SESSION_FLAG + "', '" + res[i].VERSION + "', '" + res[i].CONNECTION_ROW_COUNT + "', " +
                                 "'" + res[i].ERROR_CODE + "', '" + res[i].METHOD + "', '" + res[i].HOST_LEN + "', '" + res[i].HOST + "', " +
                                 "'" + res[i].URL_LEN + "', '" + res[i].URL + "', '" + res[i].CONNECTION_START_TIME + "', '" + res[i].CONNECTION_LAST_UPDATE_TIME + "', " +
                                 "'" + res[i].CONNECTION_FLAG + "', '" + res[i].CONNECTION_ID + "', '" + res[i].TOTAL_EVENT_COUNT + "', '" + res[i].TUNNEL_PAIR_ID + "', " +
                                 "'" + res[i].RESPONSIVENESS_TYPE + "', '" + res[i].CLIENT_PORT + "', '" + res[i].PAYLOAD_TYPE + "', '" + res[i].VIRTUAL_TYPE + "', " +
                                 "'" + res[i].VID_CLIENT + "', '" + res[i].VID_SERVER + "', '" + res[i].CLIENT_ADDR + "', '" + res[i].SERVER_ADDR + "', " +
                                 "'" + res[i].CLIENT_TUNNEL_ADDR + "', '" + res[i].SERVER_TUNNEL_ADDR + "', '" + res[i].ERROR_CODE_2 + "', '" + res[i].IPID + "', " +
                                 "'" + res[i].C2S_PKTS + "', '" + res[i].C2S_OCTETS + "', '" + res[i].S2C_PKTS + "', '" + res[i].S2C_OCTETS + "', " +
                                 "'" + res[i].NUM_SUCC_TRANS + "', '" + res[i].CONNECT_TIME + "', '" + res[i].TOTAL_RESP + "', '" + res[i].TIMEOUTS + "', " +
                                 "'" + res[i].RETRIES + "', '" + res[i].RAI + "', '" + res[i].TCP_SYNS + "', '" + res[i].TCP_SYN_ACKS + "', " +
                                 "'" + res[i].TCP_SYN_RESETS + "', '" + res[i].TCP_SYN_FINS + "', '" + res[i].EVENT_TYPE + "', '" + res[i].FLAGS + "', " +
                                 "'" + res[i].TIME_STAMP + "', '" + res[i].EVENT_ID + "', '" + res[i].EVENT_CODE + "')";

                    OracleCommand command = new OracleCommand(sql, con);
                    command.ExecuteNonQuery();

                }




            }

            Console.WriteLine("Successfully Inserted");
            Console.WriteLine();
            Console.WriteLine("Number of Row Data: " + jmlRecord.ToString());
            Console.WriteLine();
            Console.WriteLine("The size of {0} is {1} bytes.", f2.Name, f2.Length);
            con.Close();



        }

        static void ShowPercentProgress(string message, int currElementIndex, int totalElementCount)
        {
            if (currElementIndex < 0 || currElementIndex >= totalElementCount)
            {
                throw new InvalidOperationException("currElement out of range");
            }
            int percent = (100 * (currElementIndex + 1)) / totalElementCount;
            Console.Write("\r{0}{1}% complete", message, percent);
            if (currElementIndex == totalElementCount - 1)
            {
                Console.WriteLine(Environment.NewLine);
            }
        }

        static int CountLinesInFile(string f)
        {
            int count = 0;
            using (StreamReader r = new StreamReader(f))
            {
                string line;
                while ((line = r.ReadLine()) != null)
                {
                    count++;
                }
            }
            return count;
        }

    }

    [DelimitedRecord(",")]
    public class XL_XDR
    {
        public string XDR_ID;
        public string XDR_TYPE;
        public string SESSION_START_TIME;
        public string SESSION_END_TIME;
        public string SESSION_LAST_UPDATE_TIME;
        public string SESSION_FLAG;
        public string VERSION;
        public string CONNECTION_ROW_COUNT;
        public string ERROR_CODE;
        public string METHOD;
        public string HOST_LEN;
        public string HOST;
        public string URL_LEN;
        public string URL;
        public string CONNECTION_START_TIME;
        public string CONNECTION_LAST_UPDATE_TIME;
        public string CONNECTION_FLAG;
        public string CONNECTION_ID;
        public string TOTAL_EVENT_COUNT;
        public string TUNNEL_PAIR_ID;
        public string RESPONSIVENESS_TYPE;
        public string CLIENT_PORT;
        public string PAYLOAD_TYPE;
        public string VIRTUAL_TYPE;
        public string VID_CLIENT;
        public string VID_SERVER;
        public string CLIENT_ADDR;
        public string SERVER_ADDR;
        public string CLIENT_TUNNEL_ADDR;
        public string SERVER_TUNNEL_ADDR;
        public string ERROR_CODE_2;
        public string IPID;
        public string C2S_PKTS;
        public string C2S_OCTETS;
        public string S2C_PKTS;
        public string S2C_OCTETS;
        public string NUM_SUCC_TRANS;
        public string CONNECT_TIME;
        public string TOTAL_RESP;
        public string TIMEOUTS;
        public string RETRIES;
        public string RAI;
        public string TCP_SYNS;
        public string TCP_SYN_ACKS;
        public string TCP_SYN_RESETS;
        public string TCP_SYN_FINS;
        public string EVENT_TYPE;
        public string FLAGS;
        public string TIME_STAMP;
        public string EVENT_ID;
        public string EVENT_CODE;


    }


}

I hope someone can give me a solution. Thanks

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Suthish Nair15y ago
First of all you need to optimize the code.

1. Take the string sql outside for loop.

2. Remove the Thread.Sleep

3. Use OracleParameterCollection or OracleParameter, using the method you can move out the insert statement outside for loop.

   Using .Clear() you can reuse the Oracle Command (insert).

4. Use single Transaction, instead of multiple opened connections.

5. Or, check these links. Different ways discussed here.

   Link1, Link2, Link3
Guest User15y ago
Is there anything in the Windows event logs that can tell you why it failed?

This is beyond my experience... all I can suggest is that the error is possibly data-related (file corruption, bad formatting, etc.). Try splitting the CSV file into smaller files and importing them one at a time. This would help pinpoint any problems in the import data.
hery15y ago
thank you for your answer.
I've tried to use sql * loader, but when I run error "unhandled win32 exception occurred sqlldr.exe [5644]. " I run this script:

SQLLDR XL / rahasis @ O11G CONTROL = E: \ APT \ WorkingFolder \ WorkingFolder \ Ctrl.csv

This file Ctrl.csv:

options (skip = 1, direct = true, rows = 10000, bindsize = 512 000)
LOAD DATA
INFILE 8testskrip_HTTP.csv
BADFILE dataFile.bad
APPEND INTO TABLE XL_XDR
Fields terminated BY ',' optionally ENCLOSED BY '"'
(xdr_id, xdr_type, session_start_time, session_end_time, session_last_update_time, session_flag, Indonesia,
connection_row_count, ERROR_CODE, methods, host_len, host, url_len, url, connection_start_time, connection_last_update_time,
connection_flag, connection_id, total_event_count, tunnel_pair_id, responsiveness_type, client_port, payload_type,
virtual_type, vid_client, vid_server, client_addr, server_addr, client_tunnel_addr, server_tunnel_addr, error_code_2, ipid,
c2s_pkts, c2s_octets, s2c_pkts, s2c_octets, num_succ_trans, connect_time, total_resp, timeouts, retries, rai, tcp_syns,
tcp_syn_acks, tcp_syn_resets, tcp_syn_fins, event_type, flags, time_stamp, event_id, event_code)
Guest User15y ago
Have you tried using Oracle's SQL*Loader utility application for this?

http://www.orafaq.com/wiki/SQL*Loader_FAQ