Import data from excel to sql returning NULL

Question

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();
    }
}

dario · Accepted Answer · 2015-03-11 13:23:47Z

1

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.

answered Mar 11, 2015 at 13:23

dario

5,27912 gold badges32 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

LuxuryWaffles Over a year ago

It's still imported as NULL

dario Over a year ago

@BloopieBloops check your connection string again. When this happens it's a connection string related problem.

LuxuryWaffles Over a year ago

Right now, I'm using this: Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0 Xml;HDR={1};IMEX=1

Collectives™ on Stack Overflow

Import data from excel to sql returning NULL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related