Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2010-11-10
14
Medium Priority
?
1,251 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
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.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

715 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