sqlbulkcopy reads only 255 characters

Question

I am using sqlbulkcopy class to read excel in a table

 string connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + targetFileName + ";Extended Properties=Excel 12.0;";
    //OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + targetFileName + @";Extended Properties=""Excel 8.0;IMEX=1;ImportMixedTypes=Text;HDR=YES;""");
    OleDbConnection con = new OleDbConnection(connStr);
    OleDbCommand myCommand = new OleDbCommand("SELECT * FROM [Sheet1$]", con);


    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(System.Configuration.ConfigurationManager.ConnectionStrings["CrossMediaConnectionString"].ToString()))
        try
        {
            {
                #region SqlBulkCopy
                bulkCopy.DestinationTableName = "tbl_TempProductData";

                bulkCopy.ColumnMappings.Add("product_code", "ProductCode");
                bulkCopy.ColumnMappings.Add("product_name", "ProductName");
                bulkCopy.ColumnMappings.Add("category_relation_id", "RelatedCategoryId");
                bulkCopy.ColumnMappings.Add("short_description", "ProductDescription");



       con.Open();

               bulkCopy.WriteToServer(myCommand.ExecuteReader());

    }
}

but ProductDescription reads only 255 characters even for table tbl_TempProductData I have set length to max

possible duplicate of [Excel cell-values are truncated by OLEDB-provider](http://stackoverflow.com/questions/898513/excel-cell-values-are-truncated-by-oledb-provider) — Scott Chamberlain, Jan 15 '14 at 06:36

score 0 · Accepted Answer · edited May 23 '17 at 11:57

As rmoore said in this answer,

The OLEDB provider for excel will attempt to automatically determine the DataTypes based off of the first 8 rows of data, this can be set with the HDR=Yes/No property in the connection string. Additionally, there are multiple types that it can apply to text columns. The memo type holds over 255 characters, so if none of the first 8 rows have that then it will incorrectly set the data type.

The way to change this is by changing a registry setting called TypeGuessRows, as described here:Microsoft Support

NOTE: The valid range of values for the TypeGuessRows key is 0 to 16. However, if the value is 0, the number of source rows scanned is 16384. So if you have a very large file make sure the biggest rows are first.

score 0 · Answer 2 · answered Jan 15 '14 at 06:48

0

Here is my connection string. I think the extended property may help you..

var connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"", fileName);

answered Jan 15 '14 at 06:48

Shaiju Poozhikkunnu

96
8

sqlbulkcopy reads only 255 characters

2 Answers2