Importing data from an Excel File and using columnMapping

Question

User-430413477 posted

When importing data from an Excel File and using columnMapping the 00s get dropped

Example
DriverID = 1.00. It is being imported into a varchar 200 field

All fields come in correctly except 1.00 now show as 1. The 00s get dropped

sqCopy.ColumnMappings.Add("DriverID", "DriverID")

Answer 1

User991499041 posted

Hi Craigbtx,

When importing data from an Excel File and using columnMapping the 00s get dropped

Example
DriverID = 1.00. It is being imported into a varchar 200 field

All fields come in correctly except 1.00 now show as 1. The 00s get dropped

sqCopy.ColumnMappings.Add("DriverID", "DriverID")

I suppose you import data to DataTable or Table in Database. 

You will need to define data types for columns in your DataTable or Table in Database.

For example

DataTable

DataTable dtExcelData = new DataTable();
dtExcelData.Columns.AddRange(new DataColumn[3] { new DataColumn("Id", typeof(int)),
                    new DataColumn("Name", typeof(string)),
                    new DataColumn("DriverID",typeof(decimal)) });

Table in Database, data type for column

CREATE TABLE [dbo].[MyTabel](
    [PersonId] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](100) NOT NULL,
    [DriverID] [numeric](10, 2) NOT NULL
) ON [PRIMARY]

Regards

zxj

Answer 2

User-707554951 posted

Hi craigbtx, 

For your problem, You need to use Decimal type instead of varchar type:

    <asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button Text="Upload" OnClick = "Upload" runat="server" />

CodeBehind:

using System.IO;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Configuration;

   
            protected void Upload(object sender, EventArgs e)
        {
            //Upload and save the file
            string excelPath = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName);
            FileUpload1.SaveAs(excelPath);

            string conString = string.Empty;
            string extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
            switch (extension)
            {
                case ".xls": //Excel 97-03
                    conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
                    break;
                case ".xlsx": //Excel 07 or higher
                    conString = ConfigurationManager.ConnectionStrings["Excel07+ConString"].ConnectionString;
                    break;

            }
            conString = string.Format(conString, excelPath);
            using (OleDbConnection excel_con = new OleDbConnection(conString))
            {
                excel_con.Open();
                string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
                DataTable dtExcelData = new DataTable();

                //[OPTIONAL]: It is recommended as otherwise the data will be considered as String by default.
                dtExcelData.Columns.AddRange(new DataColumn[3] { new DataColumn("Id", typeof(int)),
                new DataColumn("Name", typeof(string)),
                new DataColumn("Value",typeof(decimal)) });

                using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + sheet1 + "]", excel_con))
                {
                    oda.Fill(dtExcelData);
                }
                excel_con.Close();

                string consString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                using (SqlConnection con = new SqlConnection(consString))
                {
                    using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
                    {
                        //Set the database table name
                        sqlBulkCopy.DestinationTableName = "dbo.tblPersons";

                        //[OPTIONAL]: Map the Excel columns with that of the database table
                        sqlBulkCopy.ColumnMappings.Add("Id", "Id");
                        sqlBulkCopy.ColumnMappings.Add("Name", "Name");
                        sqlBulkCopy.ColumnMappings.Add("Value", "Value");
                        con.Open();
                        sqlBulkCopy.WriteToServer(dtExcelData);
                        con.Close();
                    }
                }
            }
        }

Output:

Related link:

http://www.aspforums.net/Threads/208192/SqlBulkCopy-Excel-Import-Decimal-Precision-lost-and-its-rounded-in-C-Net/

Best regards 

Cathy