We help IT Professionals succeed at work.

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

1,330 Views
Last Modified: 2012-05-10
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.
Comment
Watch Question

Author

Commented:
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

Author

Commented:
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);

Author

Commented:
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

Author

Commented:
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?  
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
should be...

           vDataTable.Rows.RemoveAt(0);
           vDataTable.Rows.RemoveAt(0);
           vDataTable.Rows.RemoveAt(0);
           
           vDataTable.Rows.RemoveAt(vDataTable.Rows.Count - 1);

Author

Commented:
Nice, I really liked it solved my problem

Author

Commented:
Thanks JamesAperts.. that worked for me...
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.