Question

i use SqlBulkCopy to insert data from OleDbDataReader (contains data from xls) to mssql-2005 i have a cloumn on the OleDbDataReader that contains number stored as text (in the xls)

when i look into the mssql data i see null in that column all other columns are move ok.

link text

1
0
8/16/2011 4:27:33 AM

Accepted Answer

you need to map columns like in the code...

using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destConnection))
        {
            bulkCopy.ColumnMappings.Add("ID", "ID");
            bulkCopy.ColumnMappings.Add("Email", "Email");
            bulkCopy.DestinationTableName = "tableName";
            bulkCopy.WriteToServer(ExcelReader);
        }
3
12/1/2009 11:49:58 AM

Popular Answer

When one bulk copies from SQL server to SQL Server, the source is able to indicate very well the data types to the destination. With Excel, the source makes guesses about the data type. You may have the Excel formatted as text, but the source (ODBC?) might look at the first 50 rows and guess that it's a numeric datatype. Even if the column names match, if the data types are different I suspect the column doesn't get populated.



Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow