Link to home
Create AccountLog in
Avatar of nkc1112
nkc1112

asked on

Insert CSV file into SQL Server 2005 table using oledb in C#

Hi,

I have a CSV file that needs to go into SQL Server 2005 table using C#. I want to use SQL Bulk copy and OLEDB connection. Please provide me the necessary code example. I am new to use SQL bulk copy and OLEDB.  
While inserting into database table from CSV file, i need to elminate the first three rows and last row from CSV file also.
Avatar of jijeesh
jijeesh
Flag of India image

Avatar of nkc1112
nkc1112

ASKER

Thanks for the information  but still i need to know how to use sql bulk copy?

 filepath = connection.FilePath;
                string FileName = Path.GetFileNameWithoutExtension(filepath);

                string connectionstring = @"Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filepath + ";Extended Properties='text;HDR=Yes;FMT=Delimited(,)';";
                using (OleDbConnection con = new OleDbConnection(connectionstring))
                {
                    OleDbCommand cmd = new OleDbCommand(string.Format("select * from [{0}]", FileName), con);
                    try
                    {
                        con.Open();
                        using (DbDataReader db = cmd.ExecuteReader())
                        {
// calling to get connection string
                            SqlConnection conn = connection.Helper;
                            using (SqlBulkCopy dr = new SqlBulkCopy(conn))
                            {
                                try
                                {
                                    SqlCommand commad = new SqlCommand("Delete from dbo.test_Stage", conn);
                                    conn.Open();
                                    commad.ExecuteNonQuery();
                                    dr.DestinationTableName = "test_stage";
                                    dr.ColumnMappings.Add("", "Empnum");
                                    dr.ColumnMappings.Add("", "LastName");
                                    dr.ColumnMappings.Add("", "FirstName");
                                    dr.ColumnMappings.Add("", "CID");
                                    dr.ColumnMappings.Add("", "VID");
                                    dr.ColumnMappings.Add("", "CourseTitle");
                                    dr.ColumnMappings.Add("", "DateCompleted");
                                    dr.WriteToServer(db);
                                    db.Close();
                                    con.Close();
                                    conn.Close();
                                }
                                catch(Exception ex)
                                {
                                    db.Close();
                                    con.Close();
                                    connection.WriteToErrorLog("This Error occured in While Importing data :" + ex.Message + Environment.NewLine + "With FilePath:" + filepath  + Environment.NewLine + "With FileName:" + Name, "Import Data Error");
                                }
                            }
                        }
                    }

Can you please tell me how to start reading csv file from row 4th to last but one line.. for e.g. if csv file has 30 rows, then i need to start reading from 4th row to 29th row..  can any one help me with sample code?
sample code to read a csv file into a string and then splits it (expecting \r\n at end of each line which you might need to change)
it then ignores first 3 and last line


System.IO.StreamReader vReader = new System.IO.StreamReader(@"c:\test.csv");
string vFileStr = vReader.ReadToEnd();
string [] vEOL = new string [1];

vEOL [0] = "\r\n";

string[] vRecs = vFileStr.Split(vEOL, StringSplitOptions.RemoveEmptyEntries);

for (int i = 0; i < vRecs.Length; i++)
{
     // ignore first 3 and last record
     if ((i <=3) || (i >= vRecs.Length-1))
         continue;

         // process record as it's not one of the control records
         DoSomething(vRecs[i]);

}

vReader.Close();
in my last comment should be  i < 3 rather than i <= 3

Avatar of nkc1112

ASKER

Thanks for you suggestions. I have tried different connection strings for OLEDB to read CSV file but looks like every thing fails.. CAn any one suggest me with proper connection string?

what error are you seeing?

Your connection string seems to work.  one possible error I see is you are removing the extension from the csv file name.

 string FileName = Path.GetFileNameWithoutExtension(filepath);

If you change that to the following so that the extension remains.

string FileName = Path.GetFileName (filepath);
Avatar of nkc1112

ASKER

Thank you for you suggestion, i have implemented exactly like what you said,  but i am using Datareader to read the file and after that I am not getting how to just dumb into table strarting from 4th row and not last row.. Do you have any suggestions how to use Datareader ?

Sorry but I don't know how to exclude data from datareader other than reading through them one at a time.

You could create a temporary csv that excludes the rows you aren't interested in and then use the reader to read from that instead of original csv


           
 System.IO.StreamReader vReader = new System.IO.StreamReader(@"c:\test.csv");
            System.IO.StreamWriter vWriter = new System.IO.StreamWriter(@"c:\temp.csv");
            string vFileStr = vReader.ReadToEnd();
            string [] vEOL = new string [1];

            vEOL [0] = "\r\n";

            string[] vRecs = vFileStr.Split(vEOL, StringSplitOptions.RemoveEmptyEntries);

            for (int i = 0; i < vRecs.Length; i++)
            {
                    // ignore first 3 and last record
                if ((i <3) || (i >= vRecs.Length-1))
                    continue;

                // process record
                vWriter.WriteLine (vRecs[i]);

            }

            vReader.Close();
            vWriter.Close();

Open in new window

Avatar of nkc1112

ASKER

Thanks for your suggestion, I have implemented the for loop to exclude some data, but problem is the CSV may also contaion around 20000 rows or more than that, so it is taking much time. I am trying to find a solution how to just delete first 3 lines and last line from a file with out running in a loop.  Once i can delete that unwanted data, i can use Idatareader to read the file at a time and insert into data base table using sql bulk copy.  Can any one can  help me how to just delete first 3 rows and last row from my csv file with out running in a loop?  
ASKER CERTIFIED SOLUTION
Avatar of JamesAperta
JamesAperta

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
should be...

           vDataTable.Rows.RemoveAt(0);
           vDataTable.Rows.RemoveAt(0);
           vDataTable.Rows.RemoveAt(0);
           
           vDataTable.Rows.RemoveAt(vDataTable.Rows.Count - 1);
Avatar of nkc1112

ASKER

Nice, I really liked it solved my problem
Avatar of nkc1112

ASKER

Thanks JamesAperts.. that worked for me...