C# .NET - how to read Tab delimeted text files (Bulk Data) and insert into SQL SERVER?

Asked By avula on 29-Jun-12 12:56 AM
Jitendra Faye replied to avula on 29-Jun-12 12:58 AM
Try like this-

string strProvier = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Employees.mdb";
 OleDbConnection con = new OleDbConnection(strProvier);
 OleDbCommand cmd = new OleDbCommand();
 int effectedRow = 0;
 int lineCounter = 0;
 
 con.Open();
 
 if ((con.State.ToString() == "Open"))
{
  
   StreamReader stReader = new StreamReader("Employees.csv");
   string[] strRowData = null;
  
   while (stReader.Peek() >= 0)
   {
     lineCounter = lineCounter + 1;
     strRowData = stReader.ReadLine().Split(",");
    
     try {
       cmd.CommandText = "INSERT INTO tbl_employees(emp_first_name,emp_last_name,emp_salary) VALUES ('" + strRowData(0) + "','" +                   strRowData(1) + "','" + strRowData(2) + "')";
       cmd.Connection = con;
       effectedRow = cmd.ExecuteNonQuery();
      
       if ((effectedRow == -1)) {
         //  Messagebox.Show("Line: " + lineCounter + " Error"); 
       }
       else {
          // Messagebox.Show("Line: " + lineCounter + " Executed Successfully");
        
       }
     }
     catch (OleDbException er) {
       Messagebox.Show("Line: " + lineCounter + " Error: " + er.Message);
      
     }
   }
   stReader.Close();
   con.Close();
 }
 else {
   Messagebox.Show("Not Connected To Database");
 }
avula replied to avula on 29-Jun-12 01:02 AM
hi here is my code

protected void Page_Load(object sender, EventArgs e)
    {

    DataTable data = RetrieveSourceData();
    CopyData(data);
    }

    public static DataTable RetrieveSourceData()
    {
    //connection string changes depending on the operation
    //system you are running
    string sourceConnString = @"Provider=Microsoft.Jet.OLEDB.4.0;
            Data Source=C:\Documents and Settings\Administrator\Desktop\;
            Extended Properties=text;";

    DataTable sourceData = new DataTable();
    using (OleDbConnection conn =
       new OleDbConnection(sourceConnString))
    {
      conn.Open();
      // Get the data from the source table as a SqlDataReader.
      OleDbCommand command = new OleDbCommand(
          @"SELECT * from Products.txt", conn);

      OleDbDataAdapter adapter = new OleDbDataAdapter(command);
      adapter.Fill(sourceData);
      conn.Close();
    }
    return sourceData;
    }

    public static void CopyData(DataTable sourceData)
    {
    string destConnString = @"Password=sdfdx;Persist Security Info=True;User ID=xyz;Initial Catalog=dsfdx;Data Source=1.27.1.01";
    // Set up the bulk copy object.
    using (SqlBulkCopy bulkCopy =
        new SqlBulkCopy(destConnString))
    {
      bulkCopy.DestinationTableName = "dbo.tblProduct";
      // Guarantee that columns are mapped correctly by
      // defining the column mappings for the order.
      bulkCopy.ColumnMappings.Add("Name", "Name");
      bulkCopy.ColumnMappings.Add("ProductNumber", "ProductNumber");
      bulkCopy.ColumnMappings.Add("Price", "ListPrice");
      // Write from the source to the destination.
      bulkCopy.WriteToServer(sourceData);
    }
    }

please help me

i need above code should be in tab delimeted not like this  " ", " ",

thank you

can you Please modified above source code and let me know thanks every body