CODEWITHSUNDEEP
c#sqlasp.netimport-from-excel
LuxuryWaffles
LuxuryWaffles

Reputation: 1708

Import data from excel to sql returning NULL

I'm trying to import data from an excel sheet into my database, however some data would be imported as NULL. Which includes

1) Something that contains '-'. eg. 12345678-9

2) A number that has a warning on excel that says number stored as text, and when the option convert this to number, it goes from 811123123121014259 to 8.96503E+17

I am importing the data via ASP.NET

using (OleDbConnection excelConnection = new OleDbConnection(conStr))
{
    //Create OleDbCommand to fetch data from Excel 
    using (OleDbCommand cmd = new OleDbCommand("SELECT * From [" + SheetName + "]", excelConnection))
    {
        excelConnection.Open();
        using (OleDbDataReader dReader = cmd.ExecuteReader())
        {
            using (SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection))
            {
                //Give your Destination table name 
                sqlBulk.DestinationTableName = "TableName";
                sqlBulk.WriteToServer(dReader);
            }
        }
        excelConnection.Close();
    }
}

Upvotes: 1

Views: 209

Answers (1)

dario
dario

Reputation: 5269

You have to change your connection string.

Use this one when you want to treat all data in the file as text, overriding Excels column type "General" to guess what type of data is in the column.

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1";

More informations here.

Upvotes: 1

Related Questions