how to import csv data into Oracle using c #

[hery]

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

[Mitja Bonca]

damn, what a mess. Why dont you use stored procedure? You will get rid of these looong code. And some work on optimizing the sql query will surely do the trick.
I cannot even see what is all about this strange query, so hard to tell what cound be wrong for slow work.
As said, use stored procedures ( seperate all of them, insert, update, select), and do as little work as possible at ones.

Mitja

[hery]

damn, what a mess. Why dont you use stored procedure? You will get rid of these looong code. And some work on optimizing the sql query will surely do the trick.
I cannot even see what is all about this strange query, so hard to tell what cound be wrong for slow work.
As said, use stored procedures ( seperate all of them, insert, update, select), and do as little work as possible at ones.

Mitja

Thank you for your answer.
I've used stored procedures using ODP.net connection, but the results are almost the same. Is there another way to import csv data into oracle with more quickly. This example source code that I have ever made using ODP.Net Stored Procedure:

VB.NET

Public Sub ExecutImport()

        'Dim myReader As New System.IO.StreamReader(clsFileAndPath)
        Dim CurrentLine As String
        Dim IsFirstLine As Boolean
        Dim myTable As New DataTable()
        Dim SqlTable As New DataTable()
        Dim ColumnName As String
        Dim tmpCount As Integer
        Dim ColumnCount As Integer
        Dim myColumn As DataColumn
        Dim myRowSql As DataRow
        Dim myRowTable As DataRow
        Dim IsValidRow As Boolean
        Dim ColumnIsDouble As Boolean
        Dim UnknownCount As Integer
        Dim RowCount As Integer
        Dim tmpImportProgress As Double
        Dim ImportFileName As String
        Dim dataDate_FSI As String
        Dim dataTime_SFI As String
        Dim dataIMSI_SFI As String
        Dim dataMSISDN_SFI As String
        Dim dataClientAdr_SFI As String
        Dim dataServerAdr_SFI As String
        Dim dataClientPort_SFI As String
        Dim dataServerPort_SFI As String
        Dim dataCommand_SFI As String
        Dim dataHost_SFI As String
        Dim dataUrl_SFI As String
        Dim dataAgent_SFI As String
        Dim dataReply_SFI As String
        Dim dataCltApplBytes_SFI As String
        Dim dataSvrApplBytes_SFI As String
        Dim dataDuration_usec_SFI As String
        Dim MyReaders As New Microsoft.VisualBasic.FileIO.TextFieldParser(clsFileAndPath)
        Dim MyReaders2 As New Microsoft.VisualBasic.FileIO.TextFieldParser(clsFileAndPath)
        Dim currentRow As String()

        strConnection = "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;"

        conn = New OracleConnection(strConnection)
        conn.Open()

        MyReaders.TextFieldType = FileIO.FieldType.Delimited

        MyReaders.SetDelimiters(",")

        clsTotalRows = GetTotalRows()


        Dim aryStr() As String
       

        While Not MyReaders.EndOfData

            Try

                currentRow = MyReaders.ReadFields()

                If IsFirstLine = False Then
                    aryStr = Split(String.Join(vbCrLf, currentRow))
                End If

                If IsFirstLine = False Then
                    IsFirstLine = True
                    tmpCount = 0
                Else

                    dataDate_FSI = currentRow(0) 'myRowTable.ItemArray(0).ToString
                    dataTime_SFI = currentRow(1) 'myRowTable.ItemArray(1).ToString
                    dataIMSI_SFI = currentRow(2) 'myRowTable.ItemArray(2).ToString
                    dataMSISDN_SFI = currentRow(3) 'myRowTable.ItemArray(3).ToString

                    dataClientAdr_SFI = currentRow(4) 'myRowTable.ItemArray(4).ToString
                    dataServerAdr_SFI = currentRow(5) 'myRowTable.ItemArray(5).ToString
                    dataClientPort_SFI = currentRow(6) 'myRowTable.ItemArray(6).ToString
                    dataServerPort_SFI = currentRow(7) 'myRowTable.ItemArray(7).ToString

                    dataCommand_SFI = currentRow(8) 'myRowTable.ItemArray(8).ToString
                    dataHost_SFI = currentRow(9) 'myRowTable.ItemArray(9).ToString
                    dataUrl_SFI = currentRow(10) 'myRowTable.ItemArray(10).ToString
                    dataAgent_SFI = currentRow(11) 'myRowTable.ItemArray(11).ToString

                    dataReply_SFI = currentRow(12) 'myRowTable.ItemArray(12).ToString
                    dataCltApplBytes_SFI = currentRow(13) 'myRowTable.ItemArray(13).ToString
                    dataSvrApplBytes_SFI = currentRow(14) 'myRowTable.ItemArray(14).ToString
                    dataDuration_usec_SFI = currentRow(15) 'myRowTable.ItemArray(15).ToString

                    Dim cmd1 As OracleCommand = New OracleCommand("", conn)
                    cmd1.CommandText = "PROC_CSV_SFI"
                    cmd1.CommandType = CommandType.StoredProcedure

                    Dim myArrayDate_SFI As String() = {dataDate_FSI}
                    Dim myArrayTime_SFI As String() = {dataTime_SFI}
                    Dim myArrayIMSI_SFI As String() = {dataIMSI_SFI}
                    Dim myArrayMSISDN_SFI As String() = {dataMSISDN_SFI}

                    Dim myArrayClientAdr_SFI As String() = {dataClientAdr_SFI}
                    Dim myArrayServerAdr_SFI As String() = {dataServerAdr_SFI}
                    Dim myArrayClientPort_SFI As String() = {dataClientPort_SFI}
                    Dim myArrayServerPort_SFI As String() = {dataServerPort_SFI}

                    Dim myArrayCommand_SFI As String() = {dataCommand_SFI}
                    Dim myArrayHost_SFI As String() = {dataHost_SFI}
                    Dim myArrayUrl_SFI As String() = {dataUrl_SFI}
                    Dim myArrayAgent_SFI As String() = {dataAgent_SFI}

                    Dim myArrayReply_SFI As String() = {dataReply_SFI}
                    Dim myArrayCltApplBytes_SFI As String() = {dataCltApplBytes_SFI}
                    Dim myArraySvrApplBytes_SFI As String() = {dataSvrApplBytes_SFI}
                    Dim myArrayDuration_usec_SFI As String() = {dataDuration_usec_SFI}

                    cmd1.ArrayBindCount = 1

                    Dim dateSFIParams As OracleParameter = New OracleParameter("DATE_SFI", OracleDbType.Varchar2)

                    dateSFIParams.Direction = ParameterDirection.Input
                    dateSFIParams.Value = myArrayDate_SFI
                    cmd1.Parameters.Add(dateSFIParams)


                    Dim timeSFIParams As OracleParameter = New OracleParameter("TIME_SFI", OracleDbType.Varchar2)

                    timeSFIParams.Direction = ParameterDirection.Input
                    timeSFIParams.Value = myArrayTime_SFI
                    cmd1.Parameters.Add(timeSFIParams)

                    Dim IMSI_SFIParams As OracleParameter = New OracleParameter("IMSI", OracleDbType.Varchar2)

                    IMSI_SFIParams.Direction = ParameterDirection.Input
                    IMSI_SFIParams.Value = myArrayIMSI_SFI
                    cmd1.Parameters.Add(IMSI_SFIParams)

                    Dim MSISDN_SFIParams As OracleParameter = New OracleParameter("MSISDN", OracleDbType.Int64)

                    MSISDN_SFIParams.Direction = ParameterDirection.Input
                    MSISDN_SFIParams.Value = myArrayMSISDN_SFI
                    cmd1.Parameters.Add(MSISDN_SFIParams)

                    Dim ClientAdr_SFIParams As OracleParameter = New OracleParameter("ClientAdr", OracleDbType.Varchar2)

                    ClientAdr_SFIParams.Direction = ParameterDirection.Input
                    ClientAdr_SFIParams.Value = myArrayClientAdr_SFI
                    cmd1.Parameters.Add(ClientAdr_SFIParams)

                    Dim ServerAdr_SFIParams As OracleParameter = New OracleParameter("ServerAdr", OracleDbType.Varchar2)

                    ServerAdr_SFIParams.Direction = ParameterDirection.Input
                    ServerAdr_SFIParams.Value = myArrayServerAdr_SFI
                    cmd1.Parameters.Add(ServerAdr_SFIParams)

                    Dim ClientPort_SFIParams As OracleParameter = New OracleParameter("ClientPort", OracleDbType.Int64)

                    ClientPort_SFIParams.Direction = ParameterDirection.Input
                    ClientPort_SFIParams.Value = myArrayClientPort_SFI
                    cmd1.Parameters.Add(ClientPort_SFIParams)

                    Dim ServerPort_SFIParams As OracleParameter = New OracleParameter("ServerPort", OracleDbType.Int64)

                    ServerPort_SFIParams.Direction = ParameterDirection.Input
                    ServerPort_SFIParams.Value = myArrayServerPort_SFI
                    cmd1.Parameters.Add(ServerPort_SFIParams)

                    Dim Command_SFIParams As OracleParameter = New OracleParameter("Command", OracleDbType.Varchar2)

                    Command_SFIParams.Direction = ParameterDirection.Input
                    Command_SFIParams.Value = myArrayCommand_SFI
                    cmd1.Parameters.Add(Command_SFIParams)

                    Dim Host_SFIParams As OracleParameter = New OracleParameter("Host", OracleDbType.Varchar2)

                    Host_SFIParams.Direction = ParameterDirection.Input
                    Host_SFIParams.Value = myArrayHost_SFI
                    cmd1.Parameters.Add(Host_SFIParams)

                    Dim Url_SFIParams As OracleParameter = New OracleParameter("Url", OracleDbType.Varchar2)

                    Url_SFIParams.Direction = ParameterDirection.Input
                    Url_SFIParams.Value = myArrayUrl_SFI
                    cmd1.Parameters.Add(Url_SFIParams)

                    Dim Agent_SFIParams As OracleParameter = New OracleParameter("Agent", OracleDbType.Varchar2)

                    Agent_SFIParams.Direction = ParameterDirection.Input
                    Agent_SFIParams.Value = myArrayAgent_SFI
                    cmd1.Parameters.Add(Agent_SFIParams)

                    Dim Reply_SFIParams As OracleParameter = New OracleParameter("Reply", OracleDbType.Int64)

                    Reply_SFIParams.Direction = ParameterDirection.Input
                    Reply_SFIParams.Value = myArrayReply_SFI
                    cmd1.Parameters.Add(Reply_SFIParams)

                    Dim CltApplBytes_SFIParams As OracleParameter = New OracleParameter("CltApplBytes", OracleDbType.Int64)

                    CltApplBytes_SFIParams.Direction = ParameterDirection.Input
                    CltApplBytes_SFIParams.Value = myArrayCltApplBytes_SFI
                    cmd1.Parameters.Add(CltApplBytes_SFIParams)

                    Dim SvrApplBytes_SFIParams As OracleParameter = New OracleParameter("SvrApplBytes", OracleDbType.Int64)

                    SvrApplBytes_SFIParams.Direction = ParameterDirection.Input
                    SvrApplBytes_SFIParams.Value = myArraySvrApplBytes_SFI
                    cmd1.Parameters.Add(SvrApplBytes_SFIParams)

                    Dim Duration_usec_SFIParams As OracleParameter = New OracleParameter("Duration_usec", OracleDbType.Int64)

                    Duration_usec_SFIParams.Direction = ParameterDirection.Input
                    Duration_usec_SFIParams.Value = myArrayDuration_usec_SFI
                    cmd1.Parameters.Add(Duration_usec_SFIParams)

                    Try
                        cmd1.ExecuteNonQuery()

                    Catch e As Exception
                        Console.WriteLine("Execution Failed:" + e.Message)
                    End Try

                    'MessageBox.Show(String.Join(vbCrLf, currentRow))
                End If


            Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException

                MsgBox("Line " & ex.Message & "is not valid and will be skipped.")

            End Try

            tmpImportProgress += (100 / clsTotalRows)
            clsImportProgress = CInt(tmpImportProgress)
            RaiseEvent ImportProgress()

        End While

        clsImportedDataSet = myDataSet

    End Sub

Stored Procedure:

CREATE OR REPLACE PROCEDURE XL.PROC_CSV_SFI(DATE_SFI VARCHAR2,
                                           TIME_SFI VARCHAR2, IMSI VARCHAR2, MSISDN VARCHAR2,
                                           CLIENTADR VARCHAR2, SERVERADR VARCHAR2,
                                           CLIENTPORT NUMBER, SERVERPORT NUMBER, COMMAND VARCHAR2,
                                           HOST VARCHAR2, URL VARCHAR2, AGENT VARCHAR2,
                                           REPLY NUMBER, CLTAPPLBYTES NUMBER, SVRAPPLBYTES NUMBER, DURATION_USEC NUMBER) IS  
BEGIN  
  INSERT INTO REF_SFI (DATE_SFI, TIME_SFI, IMSI, MSISDN, CLIENTADR,SERVERADR, CLIENTPORT, SERVERPORT,COMMAND, HOST, URL, AGENT, REPLY,
                                   CLTAPPLBYTES, SVRAPPLBYTES, DURATION_USEC) 
                       VALUES (TO_DATE(DATE_SFI,'dd/mm/yyyy'), TIME_SFI,TO_NUMBER( IMSI), MSISDN, CLIENTADR, SERVERADR, CLIENTPORT, SERVERPORT, COMMAND, HOST, URL, AGENT, REPLY,
                                   CLTAPPLBYTES, SVRAPPLBYTES, DURATION_USEC);  
  COMMIT;  
  
  EXCEPTION
  WHEN OTHERS THEN
    NULL;
  
END;

I am using vb.net language and with different scenarios. But with the same result. Thank you.
Can you give me examples with your answers mean.