none
truncate at 255 characters with xlsx files. RRS feed

  • Question

  • Hi All,

    I am using C#.net program to read out information from an xlsx file by using the Microsoft.ACE.OLEDB.12.0 provider.

    Problem is that all long text data is truncated after 255 chars from a cell.

    Environment details: visual studio 2008, windows 8.1 64 bit OS and Office 2013 64 bit .

    code

     

    Is there any way to solve this problem? 

     OleDbConnection conn = new OleDbConnection();
                OleDbCommand cmd = new OleDbCommand();
                OleDbDataAdapter da = new OleDbDataAdapter();
                DataSet ds = new DataSet();
                string query = null;
                string connString = "";

                connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\\TestFiles\\SPL_3212.xls;Extended Properties=\"Excel 8.0;HDR=Yes;\"";
                query = "SELECT * FROM [Sheet1$]";

                //Create the connection object
                conn = new OleDbConnection(connString);
                //Open connection
                if (conn.State == ConnectionState.Closed) conn.Open();
                //Create the command object
                cmd = new OleDbCommand(query, conn);
                da = new OleDbDataAdapter(cmd);
                ds = new DataSet();
                da.Fill(ds);


    Wednesday, June 10, 2015 10:57 AM

Answers

  • This is because the column is being interpreted as TEXT, which is limited to 255 characters. The database engine looks at the first eight rows by default when determining the data type for a column, so if they are less than 255 characters in length the data type will be TEXT. You can change the default behavior by modifying the TypeGuessRows entry in the Registry for the ACE OLEDB Provider to 0, so that it will look at all rows in the Worksheet.

    Since there will be entries for the both the 32-bit and 64-bit ACE database engine I would recommend changing both of them.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, June 10, 2015 3:33 PM