bulk-copy from Excel Sheet to Sql table

Question

When i bulk copy the data from excel, Some values come null inside the sql table.

void ImporttoDatatable()
{
    try
    {
        if (FlUploadcsv.HasFile)
        {
            string FileName = FlUploadcsv.FileName;

            string ext = Path.GetExtension(FlUploadcsv.FileName).ToLower();
            int filesize = FlUploadcsv.PostedFile.ContentLength;

            if (ext == ".xls" || ext == ".xlsx")
            {
                if (filesize < 52428800)
                {


                        string path = string.Concat(Server.MapPath("~/Document/" + FlUploadcsv.FileName));
                        FlUploadcsv.PostedFile.SaveAs(path);
                        OleDbConnection OleDbcon = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;");
                        OleDbCommand command = new OleDbCommand("SELECT * FROM [Sheet1$]", OleDbcon);
                        OleDbDataAdapter objAdapter1 = new OleDbDataAdapter(command);
                        OleDbcon.Open();
                        // Create DbDataReader to Data Worksheet
                        DbDataReader dr = command.ExecuteReader();

                        // Bulk Copy to SQL Server 
                        using (SqlBulkCopy bulkInsert = new SqlBulkCopy(constr))
                        {
                            bulkInsert.DestinationTableName = "m_Details";

                            bulkInsert.WriteToServer(dr);
                            lblmsg1.Text = "Data updated successfully.";
                            dr.Close();
                            bulkInsert.Close();
                            OleDbcon.Close();




                        }

                }
                else
                {
                    lblmsg1.Text="File size cannot be greater than 2 MB";
                }
            }
            else
            {
                lblmsg1.Text = "Please select a Excel file only.";
            }
        }
        else
        {
            lblmsg1.Text = "Please select a file";
        }

        }

    catch (Exception ex)
    {
        lblmsg1.Text = ex.Message;

    }
}

My Sql table is:

TABLE [dbo].[m_Details](
[n_AppNo] [varchar](max) NULL,
[n_PriortyNo] [varchar](max) NULL,
[v_remark] [varchar](50) NULL,
[v_RegName] [varchar](max) NULL,
[n_sector] [varchar](50) NULL,
[v_pocket] [varchar](50) NULL,
[n_plotNo] [varchar](50) NULL,
[v_blockNo] [varchar](50) NULL,
[v_locality] [varchar](100) NULL,
[n_Area] [varchar](50) NULL,
[v_PlotType] [varchar](50) NULL,
[v_CISL] [varchar](max) NULL,
[n_pages] [varchar](50) NULL,
[d_scanDate] [varchar](100) NULL,
[n_PC] [varchar](50) NULL,
[v_Department] [varchar](50) NULL

)

Actually my n_AppNo column has varios type data inside Excel Sheet please follow these images. Before copy data in excel

https://i.sstatic.net/WUKey.png

after copy data in sql table

https://i.sstatic.net/EEwzl.png

Answer 1

If a text column contains data that could be interpreted as mixed data (for example, alphanumeric characters representing hexadecimal data), the initial few rows determine the actual data type used and subsequent rows may be transferred as NULLs. This behavior is by design for the Excel ISAM. The first 8 rows determine the data type of the column. For example, if most of the first 8 rows contain numeric characters, the datatype of the column is a number. All subsequent values that do not fit that datatype are returned as NULL. You can find more details:

• here
• I need a workaround for Excel Guessing Data Types problem
• Why SSIS always gets Excel data types wrong, and how to fix it!e

Fixes:

• By adding IMEX=1 (Import Export Mode) property to the connection string, excel reads the data correctly. Set the OleDbcon object as below:

  OleDbConnection OleDbcon = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;IMEX=1;");

• Second solution would be to edit the registry settings and set the TypeGuessRows property to 0 in order to let the excel driver to use all rows in the file to determine the data types properly by sampling all of the rows rather than the 1st 8. Range of values this property allows are from 0-16. So, we can have 1-16 rows, or all rows as permissible sample sizes.

The location of the key needing to changes is as follows:

HKEY_LOCAL_MACHINE -> SOFTWARE -> Wow6432Node -> Microsoft -> Jet -> 4.0 ->Engines -> Excel -> TypeGuessRows has the value 0. (It contains 8 by default)