Click here to Skip to main content
16,016,306 members
Search within:


how to read specific columns in excel file and store in sql DataBase table using c# windows application
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
.NET
Hi
In My Project, I'm Import data from Excel to Sql server 2008 Using C# Winforms 2010.
I can read the Excel File by Mapping the Columns in First row of the Column Header and store it to sql DataBase Table . But the Problem is That Suppose in Excel File First row is Been Changed to Second Row or AnyOther Rows Then C# Code Can't Map the Rows and Through an Error. So How to Read specific columns in excel file and store in sql table using c# windows application ?

Here Is My Code .
Can Any One Help Me With Right Answer

private void BtnSubmit_Click(object sender, EventArgs e)
{
    if (IcCutomerName.Text != "" && CustSalesDate.Text != "")
    {
        ImportExcel();
        PopulateListView2();
        AutoCompleteSalesNo();
        AllClear();
    }
    else
    {
        if (MessageBox.Show("Need Customer Name To Submit", "Message", MessageBoxButtons.OKCancel, MessageBoxIcon.Information) == DialogResult.OK)
        {
            IcCutomerName.Focus();
        }
        else
        {
            BtnSubmit.Focus();
        }
    }
}


public void ImportExcel()
{
    try
    {
        try { Connection(); }
        catch { return; }
        cmd = new SqlCommand("Delete from ExcelCustomerSales", cs);
        cmd.ExecuteNonQuery();
        cs.Close();

        string excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + TbFilePath.Text + "; Extended Properties=\"Excel 12.0;HDR=Yes\"";
        using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
        {
            OleDbCommand command = new OleDbCommand
            ("Select * FROM [Sheet1$]", connection);
            connection.Open();
            using (DbDataReader dr = command.ExecuteReader())
            {
                try { Connection(); }
                catch { return; }
                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(cs))
                {
                    bulkCopy.DestinationTableName = "ExcelCustomerSales";
                    bulkCopy.ColumnMappings.Add("Item/Model Code", "ModelCode");
                    bulkCopy.ColumnMappings.Add("Item/Model", "ModelName");
                    bulkCopy.ColumnMappings.Add("Qty", "Quantity");
                    bulkCopy.ColumnMappings.Add("Sales Value", "SalesValue");
                    bulkCopy.ColumnMappings.Add("Discount Amt", "DiscountAmt");
                    bulkCopy.ColumnMappings.Add("Amount", "Amount");
                    bulkCopy.ColumnMappings.Add("Net Amount", "NetAmount");
                    bulkCopy.ColumnMappings.Add("SR Qty", "SRQty");
                    bulkCopy.ColumnMappings.Add("SR Value", "SRValue");
                    bulkCopy.ColumnMappings.Add("SR Discount Amt", "SRDiscountAmt");
                    bulkCopy.ColumnMappings.Add("SR Tax Amt", "SRTaxAmt");
                    bulkCopy.ColumnMappings.Add("SR Net Amt", "SRNetAmt");
                    bulkCopy.ColumnMappings.Add("Total Qty", "TotalQty");
                    bulkCopy.ColumnMappings.Add("Total Value", "TotalValue");
                    bulkCopy.ColumnMappings.Add("Total Discount", "TotalDiscount");
                    bulkCopy.ColumnMappings.Add("Total Tax", "TotalTax");
                    bulkCopy.ColumnMappings.Add("Total Net Value", "TotalNetValue");
                    bulkCopy.WriteToServer(dr);
                }
            }
            connection.Close();
            cs.Close();
            EmptyData();
            ErrorAlert();
            MessageBox.Show("Data Exported To Sql Server Successfully");
        }
    }
    catch
    {
        MessageBox.Show("Data Exported To Sql Server has been Failed", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
}


private void EmptyData()
{
    try { Connection(); }
    catch { return; }
    cmd = new SqlCommand("Delete from ExcelCustomerSales Where ModelCode IS NULL AND  ModelName IS NULL AND  Quantity  IS NULL AND  SalesValue  IS NULL AND  DiscountAmt IS NULL AND  Amount  IS NULL AND  NetAmount IS NULL AND  SRQty IS NULL AND  SRValue IS NULL AND  SRDiscountAmt IS NULL AND  SRTaxAmt  IS NULL AND  SRNetAmt  IS NULL AND  TotalQty  IS NULL AND  TotalValue  IS NULL AND  TotalDiscount IS NULL AND  TotalTax  IS NULL AND  TotalNetValue IS NULL", cs);
    cmd.ExecuteNonQuery();
    cs.Close();
}

public void ErrorAlert()
{
    try { Connection(); }
    catch { return; }
    int Num1 = 0, Num2 = 0;
    cmd = new SqlCommand("Select MAX(ID) from CustomerSalesNo", cs);
    dr = cmd.ExecuteReader();
    while (dr.Read())
    {
        Num1 = int.Parse(dr[0].ToString());
    }
    dr.Close();
    Num2 = (Num1 + 1);
    cmd = new SqlCommand("Insert Into CustomerSalesNo(OsDcNo) Values(GetDate())", cs);
    cmd.ExecuteNonQuery();

    getDate = SqlDateTime.Null;
    DateTime? ads = null;
    try
    {
        ads = Convert.ToDateTime(CustSalesDate.EditValue);
    }
    catch
    {
    }

    string CustomerCode ="", CategoryName="", MMCode = "", ModelCode="",  ModelName = "", Quantity = "", ColorName="",  ModelSize="", SalesValue = "", DiscountAmt = "", Amount = "", NetAmount = "", SRQty = "", SRValue = "", SRDiscountAmt = "", SRTaxAmt = "", SRNetAmt = "", TotalQty = "", TotalValue = "", TotalDiscount = "", TotalTax = "", TotalNetValue = "", CMID = "", SMID = "", CUSTID = "", MMID = "", Remarks = "Customer Sales Report Saved";
    string CustomerName = IcCutomerName.Text;
    string SalesNo = Num2.ToString() + "-" + CustSalesDate.EditValue;

    SqlDataAdapter da = new SqlDataAdapter("Select ModelCode, ModelName, Quantity, SalesValue, DiscountAmt, Amount, NetAmount, SRQty, SRValue, SRDiscountAmt, SRTaxAmt, SRNetAmt, TotalQty, TotalValue, TotalDiscount, TotalTax, TotalNetValue from ExcelCustomerSales", cs);
    DataTable dt = new DataTable();
    da.Fill(dt);
    foreach (DataRow Row in dt.Rows)
    {
        ModelCode = Row["ModelCode"].ToString();
        ModelName = Row["ModelName"].ToString();
        Quantity = Row["Quantity"].ToString();
        SalesValue = Row["SalesValue"].ToString();
        DiscountAmt = Row["DiscountAmt"].ToString();
        Amount = Row["Amount"].ToString();
        NetAmount = Row["NetAmount"].ToString();
        SRQty = Row["SRQty"].ToString();
        SRValue = Row["SRValue"].ToString();
        SRDiscountAmt = Row["SRDiscountAmt"].ToString();
        SRTaxAmt = Row["SRTaxAmt"].ToString();
        SRNetAmt = Row["SRNetAmt"].ToString();
        TotalQty = Row["TotalQty"].ToString();
        TotalValue = Row["TotalValue"].ToString();
        TotalDiscount = Row["TotalDiscount"].ToString();
        TotalTax = Row["TotalTax"].ToString();
        TotalNetValue = Row["TotalNetValue"].ToString();

        SqlDataAdapter da1 = new SqlDataAdapter("Select ModelCode, CategoryName, ModelSize, ID, MSID from ModelMaster where ModelCode='" + ModelCode + "'", cs);
        DataTable dt1 = new DataTable();
        da1.Fill(dt1);
        foreach (DataRow Row1 in dt1.Rows)
        {
            MMCode = Row1["ModelCode"].ToString();
            CategoryName = Row1["CategoryName"].ToString();
            ModelSize = Row1["ModelSize"].ToString();
            MMID = Row1["ID"].ToString();
            SMID = Row1["MSID"].ToString();
        }

        SqlDataAdapter da2 = new SqlDataAdapter("Select CustomerCode, ID from CustomerMaster where CustomerName='" + IcCutomerName.Text + "'", cs);
        DataTable dt2 = new DataTable();
        da2.Fill(dt2);
        foreach (DataRow Row2 in dt2.Rows)
        {
            CustomerCode = Row2["CustomerCode"].ToString();
            CUSTID = Row2["ID"].ToString();
        }

        if (ModelCode.ToUpper() == MMCode.ToUpper())
        {
            try
            {
                cmd = new SqlCommand("Insert Into CustomerSales(CustomerCode, CustomerName, SalesNo, SalesDate, CategoryName, ModelCode, ModelName, ColorName, ModelSize, Quantity, SalesValue, DiscountAmt, Amount, NetAmount, SRQty, SRValue, SRDiscountAmt, SRTaxAmt, SRNetAmt, TotalQty, TotalValue, TotalDiscount, TotalTax, TotalNetValue, CMID, SMID, CUSTID, MMID, Remarks) Values (@CustomerCode, @CustomerName, @SalesNo, @SalesDate, @CategoryName, @ModelCode, @ModelName, @ColorName, @ModelSize, @Quantity, @SalesValue, @DiscountAmt, @Amount, @NetAmount, @SRQty, @SRValue, @SRDiscountAmt, @SRTaxAmt, @SRNetAmt, @TotalQty, @TotalValue, @TotalDiscount, @TotalTax, @TotalNetValue, @CMID, @SMID, @CUSTID, @MMID, @Remarks)", cs);
                cmd.Parameters.Add(new SqlParameter("@CustomerCode", (object)CustomerCode));
                cmd.Parameters.Add(new SqlParameter("@CustomerName", (object)CustomerName));
                cmd.Parameters.Add(new SqlParameter("@SalesNo", (object)SalesNo));
                cmd.Parameters.Add(new SqlParameter("@SalesDate", (object)ads));
                cmd.Parameters.Add(new SqlParameter("@CategoryName", (object)CategoryName));
                cmd.Parameters.Add(new SqlParameter("@ModelCode", (object)ModelCode));
                cmd.Parameters.Add(new SqlParameter("@ModelName", (object)ModelName));
                cmd.Parameters.Add(new SqlParameter("@ColorName", (object)ColorName));
                cmd.Parameters.Add(new SqlParameter("@ModelSize", (object)ModelSize));
                cmd.Parameters.Add(new SqlParameter("@Quantity", (object)Quantity));
                cmd.Parameters.Add(new SqlParameter("@SalesValue", (object)SalesValue));
                cmd.Parameters.Add(new SqlParameter("@DiscountAmt", (object)DiscountAmt));
                cmd.Parameters.Add(new SqlParameter("@Amount", (object)Amount));
                cmd.Parameters.Add(new SqlParameter("@NetAmount", (object)NetAmount));
                cmd.Parameters.Add(new SqlParameter("@SRQty", (object)SRQty));
                cmd.Parameters.Add(new SqlParameter("@SRValue", (object)SRValue));
                cmd.Parameters.Add(new SqlParameter("@SRDiscountAmt", (object)SRDiscountAmt));
                cmd.Parameters.Add(new SqlParameter("@SRTaxAmt", (object)SRTaxAmt));
                cmd.Parameters.Add(new SqlParameter("@SRNetAmt", (object)SRNetAmt));
                cmd.Parameters.Add(new SqlParameter("@TotalQty", (object)TotalQty));
                cmd.Parameters.Add(new SqlParameter("@TotalValue", (object)TotalValue));
                cmd.Parameters.Add(new SqlParameter("@TotalDiscount", (object)TotalDiscount));
                cmd.Parameters.Add(new SqlParameter("@TotalTax", (object)TotalTax));
                cmd.Parameters.Add(new SqlParameter("@TotalNetValue", (object)TotalNetValue));
                cmd.Parameters.Add(new SqlParameter("@CMID", (object)CMID));
                cmd.Parameters.Add(new SqlParameter("@SMID", (object)SMID));
                cmd.Parameters.Add(new SqlParameter("@CUSTID", (object)CUSTID));
                cmd.Parameters.Add(new SqlParameter("@MMID", (object)MMID));
                cmd.Parameters.Add(new SqlParameter("@Remarks", (object)Remarks));
                cmd.ExecuteNonQuery();
            }
            catch { }

        }
        else
        {
            try
            {
                cmd = new SqlCommand("Insert Into ErrorCustomerSales(CustomerCode, CustomerName, SalesNo, SalesDate, CategoryName, ModelCode, ModelName, ColorName, ModelSize, Quantity, SalesValue, DiscountAmt, Amount, NetAmount, SRQty, SRValue, SRDiscountAmt, SRTaxAmt, SRNetAmt, TotalQty, TotalValue, TotalDiscount, TotalTax, TotalNetValue, CMID, SMID, CUSTID, MMID, Remarks) Values (@CustomerCode, @CustomerName, @SalesNo, @SalesDate, @CategoryName, @ModelCode, @ModelName, @ColorName, @ModelSize, @Quantity, @SalesValue, @DiscountAmt, @Amount, @NetAmount, @SRQty, @SRValue, @SRDiscountAmt, @SRTaxAmt, @SRNetAmt, @TotalQty, @TotalValue, @TotalDiscount, @TotalTax, @TotalNetValue, @CMID, @SMID, @CUSTID, @MMID, @Remarks)", cs);
                cmd.Parameters.Add(new SqlParameter("@CustomerCode", (object)CustomerCode));
                cmd.Parameters.Add(new SqlParameter("@CustomerName", (object)CustomerName));
                cmd.Parameters.Add(new SqlParameter("@SalesNo", (object)SalesNo));
                cmd.Parameters.Add(new SqlParameter("@SalesDate", (object)ads));
                cmd.Parameters.Add(new SqlParameter("@CategoryName", (object)CategoryName));
                cmd.Parameters.Add(new SqlParameter("@ModelCode", (object)ModelCode));
                cmd.Parameters.Add(new SqlParameter("@ModelName", (object)ModelName));
                cmd.Parameters.Add(new SqlParameter("@ColorName", (object)ColorName));
                cmd.Parameters.Add(new SqlParameter("@ModelSize", (object)ModelSize));
                cmd.Parameters.Add(new SqlParameter("@Quantity", (object)Quantity));
                cmd.Parameters.Add(new SqlParameter("@SalesValue", (object)SalesValue));
                cmd.Parameters.Add(new SqlParameter("@DiscountAmt", (object)DiscountAmt));
                cmd.Parameters.Add(new SqlParameter("@Amount", (object)Amount));
                cmd.Parameters.Add(new SqlParameter("@NetAmount", (object)NetAmount));
                cmd.Parameters.Add(new SqlParameter("@SRQty", (object)SRQty));
                cmd.Parameters.Add(new SqlParameter("@SRValue", (object)SRValue));
                cmd.Parameters.Add(new SqlParameter("@SRDiscountAmt", (object)SRDiscountAmt));
                cmd.Parameters.Add(new SqlParameter("@SRTaxAmt", (object)SRTaxAmt));
                cmd.Parameters.Add(new SqlParameter("@SRNetAmt", (object)SRNetAmt));
                cmd.Parameters.Add(new SqlParameter("@TotalQty", (object)TotalQty));
                cmd.Parameters.Add(new SqlParameter("@TotalValue", (object)TotalValue));
                cmd.Parameters.Add(new SqlParameter("@TotalDiscount", (object)TotalDiscount));
                cmd.Parameters.Add(new SqlParameter("@TotalTax", (object)TotalTax));
                cmd.Parameters.Add(new SqlParameter("@TotalNetValue", (object)TotalNetValue));
                cmd.Parameters.Add(new SqlParameter("@CMID", (object)CMID));
                cmd.Parameters.Add(new SqlParameter("@SMID", (object)SMID));
                cmd.Parameters.Add(new SqlParameter("@CUSTID", (object)CUSTID));
                cmd.Parameters.Add(new SqlParameter("@MMID", (object)MMID));
                cmd.Parameters.Add(new SqlParameter("@Remarks", (object)Remarks));
                cmd.ExecuteNonQuery();
            }
            catch { }
        }
    }
    cs.Close();
}
Posted
Updated 15-Nov-18 0:47am
Add a Solution
Comments
Richard MacCutchan 1-Mar-13 6:34am    
You can address cells by their column and row offsets, but you still need to know in advance what those offsets will be.
KalaiPondy 1-Mar-13 6:46am    
can u Give me an sample Coding ?

2 solutions

private void btnLoadData_Click(object sender, EventArgs e)
    {
        Microsoft.Office.Interop.Excel.Application excelApp;
        Microsoft.Office.Interop.Excel.Workbook workbook;
        Microsoft.Office.Interop.Excel.Worksheet worksheet;
        Microsoft.Office.Interop.Excel.Range range;
        excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();

        // Opening Excel file
        workbook = excelApp.Workbooks.Open(txtPath.Text, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);

        worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets.get_Item(1);
        range = worksheet.UsedRange;

        int column = 0;
        int row = 0;

        System.Data.DataTable dt = new System.Data.DataTable();
        dt.Columns.Add("Product");
        dt.Columns.Add("Item/Model Code");
        dt.Columns.Add("Item/Model");
        dt.Columns.Add("Qty.");
        dt.Columns.Add("Sales Value");
        dt.Columns.Add("Discount Amt.");
        dt.Columns.Add("Amount");
        dt.Columns.Add("Net Amount");
        dt.Columns.Add("SR Qty");
        dt.Columns.Add("SR Value");
        dt.Columns.Add("SR Discount Amt.");
        dt.Columns.Add("SR Tax Amt.");
        dt.Columns.Add("SR Net Amt.");
        dt.Columns.Add("Total Qty");
        dt.Columns.Add("Total Value");
        dt.Columns.Add("Total Discount");
        dt.Columns.Add("Total Tax");
        dt.Columns.Add("Total Net Value");

        for (row = 4; row <= range.Rows.Count; row++)
        {
            DataRow dr = dt.NewRow();
            for (column = 1; column <= range.Columns.Count; column++)
            {
                dr[column - 1] = (range.Cells[row, column] as Microsoft.Office.Interop.Excel.Range).Value2.ToString();
            }
            dt.Rows.Add(dr);
        }

        workbook.Close();
        excelApp.Quit();

        // Binding to DataGridView
        dataGridView1.DataSource = dt;
    }
 
Share this answer
 
Posted
Hi KalaiPondy
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
string csv_file_path = "Give your filepath here";
DataTable csvData = GetDataTabletFromCSVFile(csv_file_path);
DataView dv = new DataView(csvData);
DataTable dt = dv.ToTable(false, "ColumnName");//which particular columns you want to read
SqlConnectionStringBuilder csb = new SqlConnectionStringBuilder();
csb.DataSource = ".";
csb.InitialCatalog = "Project";
csb.IntegratedSecurity = true;
string connString = csb.ToString();
using (SqlConnection con = new SqlConnection(connString))
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
{
sqlBulkCopy.DestinationTableName = "db TableName";
sqlBulkCopy.ColumnMappings.Add("Dealer ID", "DealerID");
sqlBulkCopy.ColumnMappings.Add("Stock", "StockNo");
sqlBulkCopy.ColumnMappings.Add("VIN", "VIN");
sqlBulkCopy.ColumnMappings.Add("Make", "Make");
sqlBulkCopy.ColumnMappings.Add("Model", "Model");
sqlBulkCopy.ColumnMappings.Add("Dealer Name", "DealerName");
con.Open();
sqlBulkCopy.WriteToServer(dt);
con.Close();
}
}
}
private static DataTable GetDataTabletFromCSVFile(string csv_file_path)
{
DataTable csvData = new DataTable();
try
{
using (TextFieldParser csvReader = new TextFieldParser(csv_file_path))
{
csvReader.SetDelimiters(new string[] { "," });
csvReader.HasFieldsEnclosedInQuotes = true;
string[] colFields = csvReader.ReadFields();
foreach (string column in colFields)
{
DataColumn dtcolumn = new DataColumn(column);
dtcolumn.AllowDBNull = true;
csvData.Columns.Add(dtcolumn);
}
while (!csvReader.EndOfData)
{
string[] fieldData = csvReader.ReadFields();
for (int i = 0; i < fieldData.Length; i++)
{
if (fieldData[i] == "")
{
fieldData[i] = null;
}
}
csvData.Rows.Add(fieldData);

}
}

}
catch (Exception)
{
throw;
}
return csvData;

}
}
}

 
Thanks
Srilekha Bolamoni. 

If it helped you then mark it as an answer
Definitely it will work.
 
Share this answer
 
Posted
Comments
CHill60 15-Nov-18 7:44am    
KalaiPondy solved their own query back in 2013
Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month
Related Questions
How to read particular columns of excel data in C# windows application and store it in SQL database
how to read excel files in C# and store the data into sql server database table?
How do read excel percentage columns as a string to datatabel
Read Text File Specific Columns
Displaying files of unknown types stored in SQL server using C# WPF
Identity specification column skipping values
How to read file excel row and sheet specific using openxml ?
How to select specific columns Excel. Then saved in a database. C#
Upload specific files' types into database
mechanism to import excel data in SQL database table dynamically

Advertise
Privacy
Cookies
Terms of Use
Last Updated 15 Nov 2018
Layout: fixed | fluid
Copyright © CodeProject, 1999-2024
All Rights Reserved.

Web01 2.8:2024-07-22:1

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900