Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2010-11-10
14
Medium Priority
?
1,254 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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
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 2000 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

927 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