Solved

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

Posted on 2010-11-10
14
1,246 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.
0
Comment
Question by:nkc1112
  • 6
  • 6
  • 2
14 Comments
 
LVL 5

Expert Comment

by:jijeesh
ID: 34106393
0
 
LVL 5

Expert Comment

by:jijeesh
ID: 34106404
0
 

Author Comment

by:nkc1112
ID: 34107278
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?
0
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
LVL 3

Expert Comment

by:JamesAperta
ID: 34107772
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();
0
 
LVL 3

Expert Comment

by:JamesAperta
ID: 34107843
in my last comment should be  i < 3 rather than i <= 3

0
 

Author Comment

by:nkc1112
ID: 34107914
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?

0
 
LVL 3

Expert Comment

by:JamesAperta
ID: 34108225
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);
0
 

Author Comment

by:nkc1112
ID: 34113436
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 ?

0
 
LVL 3

Expert Comment

by:JamesAperta
ID: 34116668
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

0
 

Author Comment

by:nkc1112
ID: 34120749
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?  
0
 
LVL 3

Accepted Solution

by:
JamesAperta earned 500 total points
ID: 34126856
The following should work if you are prepared to add a reference to Microsoft Active X Data Objects 2.X from the COM tab   (I used 2.8).  
It will remove the first 3 rows and last row from datatable and then you can insert the DataTable using the WriteToServer(DataTable)
Means using a datatable instead of datareader.




string vQuery = string.Format("select * from [{0}]", vFileName);

ADODB.Recordset vSet = new ADODB.Recordset();
System.Data.OleDb.OleDbDataAdapter vAdapter = new System.Data.OleDb.OleDbDataAdapter();
System.Data.DataTable vDataTable = new System.Data.DataTable();

vSet.Open(vQuery, connectionstring, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockReadOnly, 1);

vAdapter.Fill (vDataTable, vSet);

vDataTable.Rows.RemoveAt(0);
vDataTable.Rows.RemoveAt(1);
vDataTable.Rows.RemoveAt(2);

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

Open in new window

0
 
LVL 3

Expert Comment

by:JamesAperta
ID: 34126888
should be...

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

Author Closing Comment

by:nkc1112
ID: 34127689
Nice, I really liked it solved my problem
0
 

Author Comment

by:nkc1112
ID: 34127691
Thanks JamesAperts.. that worked for me...
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that undeā€¦
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question