You must Sign In to post a response.
  • Category: .NET

    Reads the excel sheet data but unable to insert in database

    Below is my code which is used to read the excel sheet the problem is all the columns are of type string in the below code but in my database i have 4 columns of type decimal because of this i am able to readthe excel sheet but i am unable to insert the data in database which gives me this error **The given value of type String from the data source cannot be converted to type decimal of the specified target column.** in actual cs page.how can i insert the data in database

    Code for reading the excel sheet data-

    public DataTable Read(string strPath, int isheet)//,DataRow[] dr1
    {
    dt.Columns.Add("Month", typeof(string));
    dt.Columns.Add("ID", typeof(string));
    dt.Columns.Add("Client Name", typeof(string));
    dt.Columns.Add("Charges", typeof(string));
    dt.Columns.Add("Payment", typeof(string));
    dt.Columns.Add("Adjustment", typeof(string));
    dt.Columns.Add("W/O", typeof(string));
    string ID = "";
    string url = _urlConverter.getFileURLFromSystemPath(strPath, strPath);
    PropertyValue[] loadProps = new PropertyValue[0];
    XComponent xComponent = _componentLoader.loadComponentFromURL(url, "_blank", 0, loadProps);
    XSpreadsheets oSheets = ((XSpreadsheetDocument)xComponent).getSheets();
    XIndexAccess oSheetsIA = (XIndexAccess)oSheets;
    XSpreadsheet oSheet = (XSpreadsheet)oSheetsIA.getByIndex(0).Value;
    try
    {
    int iRow = 1;
    int iEmpty = 0;
    bool boolcontinue = true;
    while (boolcontinue)
    {
    DataRow drNew = dt.NewRow();
    ID = ((XText)oSheet.getCellByPosition(0, iRow)).getString();
    if (!string.IsNullOrEmpty(ID))
    {
    iEmpty = 0;
    int i = 0;
    for (int iCol = 0; iCol < 7; iCol++)
    {
    drNew[i] = ((XText)oSheet.getCellByPosition(iCol, iRow)).getString();
    i++;
    }
    dt.Rows.Add(drNew);
    iRow++;
    }
    else
    {
    iEmpty++;
    }
    if (iEmpty > 10)
    {
    break;
    }
    }
    }
    catch (System.Exception ex)
    {

    }
    finally
    {
    oSheet = null;
    oSheetsIA = null;
    oSheet = null;
    xComponent.dispose();
    localContext = null;
    }
    return dt;
    }

    Code in .cs page which inserts the data in database after reading the excel sheet data-

    protected void Upload_Click(object sender, EventArgs e)
    {
    string fileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
    FileUpload1.PostedFile.SaveAs(Server.MapPath("~/Files/" + fileName));
    string fullpath = Path.GetFullPath(Server.MapPath("~/Files/" + fileName));
    ReadExcelSheet obj = new ReadExcelSheet();
    //send filepath,sheet number,selected rows to class file
    DataTable dt = obj.Read(fullpath, 1);
    bool Ismatch = false;
    for (int i = 0; i < dt.Rows.Count; i++)
    {
    string date = dt.Rows[i]["Month"].ToString();
    string[] date1 = date.Split('/');
    string month = date1[0];
    string year = date1[2];
    if (month == txtmonth.Text && year == txtyear.Text)
    {
    Ismatch = true;
    //break;
    }
    else
    {
    Ismatch = false;
    break;
    }
    }
    if (Ismatch == true)
    {
    lblmessage.Text = "Valid document";
    }
    else
    {
    lblmessage.Text = "Not a Valid document";
    Label1.Text = "";
    return;
    }
    //checking the input month and year records exists or not in DB
    SqlCommand cmd = new SqlCommand("select Uploaded from TestMCount1 ", con);
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataTable DBdt = new DataTable();
    da.Fill(DBdt);
    if (DBdt.Rows.Count > 0)
    {
    int month1;
    bool validMonth = int.TryParse(txtmonth.Text, out month1);
    int year1;
    bool validYear = int.TryParse(txtyear.Text, out year1);

    var filteredRows = from row in DBdt.AsEnumerable()
    let date = row.Field<System.DateTime>("date")
    where date.Month == month1 && date.Year == year1
    select row;

    DataRow[] dr = filteredRows.ToArray();
    DataTable selectedrows = filteredRows.CopyToDataTable();
    if (selectedrows.Rows.Count > 0)
    {

    for (int i = 0; i <= selectedrows.Rows.Count - 1; i++)
    {
    string date2 = selectedrows.Rows[i]["Month"].ToString();
    con.Open();
    SqlCommand cmd1 = new SqlCommand("delete from TestMCount1 where Uploaded='" + date2 + "'", con);
    SqlDataAdapter da1 = new SqlDataAdapter(cmd1);
    cmd1.ExecuteNonQuery();
    con.Close();
    }
    SqlBulkCopy objbulk = new SqlBulkCopy(con);
    objbulk.DestinationTableName = "TestMCount1";
    //mapping the columns
    objbulk.ColumnMappings.Add("ID", "MID");
    objbulk.ColumnMappings.Add("Month", "Uploaded");
    objbulk.ColumnMappings.Add("Client Name", "ClientName");
    objbulk.ColumnMappings.Add("Charges", "Charge");
    objbulk.ColumnMappings.Add("Payment", "Payment");
    objbulk.ColumnMappings.Add("Adjustment", "Adjustment");
    objbulk.ColumnMappings.Add("W/O", "WO");

    con.Open();
    objbulk.WriteToServer(dt);
    con.Close();
    Label1.Text = "Record Uploaded";
    }
    else
    {
    SqlBulkCopy objbulk = new SqlBulkCopy(con);
    objbulk.DestinationTableName = "TestMCount1";
    //mapping the columns
    objbulk.ColumnMappings.Add("ID", "MID");
    objbulk.ColumnMappings.Add("Month", "Uploaded");
    objbulk.ColumnMappings.Add("Client Name", "ClientName");
    objbulk.ColumnMappings.Add("Charges", "Charge");
    objbulk.ColumnMappings.Add("Payment", "Payment");
    objbulk.ColumnMappings.Add("Adjustment", "Adjustment");
    objbulk.ColumnMappings.Add("W/O", "WO");
    con.Open();
    objbulk.WriteToServer(dt);
    con.Close();
    Label1.Text = "Record Uploaded";
    }
    }
    else
    {
    SqlBulkCopy objbulk = new SqlBulkCopy(con);
    objbulk.DestinationTableName = "TestMCount1";
    //mapping the columns
    objbulk.ColumnMappings.Add("Month", "Uploaded");
    objbulk.ColumnMappings.Add("ID", "MID");
    objbulk.ColumnMappings.Add("Client Name", "ClientName");
    objbulk.ColumnMappings.Add("Charges", "Charge");
    objbulk.ColumnMappings.Add("Payment", "Payment");
    objbulk.ColumnMappings.Add("Adjustment", "Adjustment");
    objbulk.ColumnMappings.Add("W/O", "WO");
    con.Open();
    objbulk.WriteToServer(dt);
    con.Close();
    Label1.Text = "Record Uploaded";
    }
    }

    SQL Table

    ID int
    Uploaded datetime
    MID varchar(500)
    ClientName varchar(500)
    Charge decimal(19, 4)
    Payment decimal(19, 4)
    Adjustment decimal(19, 4)
    WO decimal(19, 4)
    Comment varchar(500)
    createdDate datetime
  • #768864
    Here is a straightforward method to export data from Excel to database, check the following link

    http://www.c-sharpcorner.com/blogs/import-and-export-data-from-excel-to-database

  • #768865
    Hi,

    As per error details "The given value of type String from the data source cannot be converted to type decimal of the specified target column.", the root cause of this is while inserting time you have to convert appropriate datatype other wise you will get this type of error only. As per your column datatype you have to convert your string to decimal type then only you won't get this type of error.

    Convert.ToDecimal(yourstring);
    Hope this helps you..

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #768867
    Hai Kavitha,
    As per the error message "**The given value of type String from the data source cannot be converted to type decimal of the specified target column.** ", looks like your data in Excel file and the table in sql server is mismatching with its type and that's the reason, it's unable to insert. You need to have the same type in both.
    The other way could be a small code where you can first get all the data of Excel file to the dataset or read through datareader and then insert to the database.
    You can follow the below snippet of code which will be helpful to you:

    https://www.codeproject.com/Tips/636719/Import-MS-Excel-data-to-SQL-Server-table-using-Csh

    Hope it will be helpful to you.

    Regards,
    Pawan Awasthi(DNS MVM)
    +91 8123489140 (whatsApp), +60 14365 1476(Malaysia)
    pawansoftit@gmail.com

  • #768874
    though on conversion but still i am getting the same error can you post the complete code

  • #768887
    do not use BulkInsert class, just read the excel rows one by one create a query and add it in database, use OLEDB object to read excel sheet with out excel interop object
    see below code

    //connect with EXCEL DB
    // Connect EXCEL sheet with OLEDB using connection string
    // if the File extension is .XLS using below connection string
    //In following sample 'szFilePath' is the variable for filePath
    szConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;
    "Data Source='" + szFilePath +
    "';Extended Properties=\"Excel 8.0;HDR=YES;\"";

    // if the File extension is .XLSX using below connection string
    szConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;
    "Data Source='" + szFilePath +
    "';Extended Properties=\"Excel 12.0;HDR=YES;\"";

    //read excel file
    using (OleDbConnection conn = new OleDbConnection(connectionString))
    {
    conn.Open();
    OleDbDataAdapter objDA = new System.Data.OleDb.OleDbDataAdapter
    ("select * from [Sheet1$]", conn);
    DataSet excelDataSet = new DataSet();
    objDA.Fill(excelDataSet);
    dataGridView1.DataSource = excelDataSet.Tables[0];
    }


    Thanks
    Koolprasd2003
    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]


Sign In to post your comments