excel oledb fields truncated at 255

Question

I'm reading in an excel file with the following code:

Function Read_Excel(ByVal sFile As String) As ADODB.Recordset
    On Error GoTo fix_err
    Dim rs As ADODB.Recordset
    rs = New ADODB.Recordset
    Dim sconn As String

    rs.CursorLocation = ADODB.CursorLocationEnum.adUseServer 
    rs.CursorType = ADODB.CursorTypeEnum.adOpenStatic 
    rs.LockType = ADODB.LockTypeEnum.adLockReadOnly 
    sconn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sFile & ";Extended Properties=""Excel 12.0;HDR=YES;IMEX=1"";"
    rs.Open("SELECT CStr([RPOCode]), Description FROM [sheet1$]", sconn)
    tot += rs.RecordCount
    rs.Close()
    rs.Open("SELECT Distinct RPOCode, Description FROM [sheet1$] ORDER BY RPOCode", sconn)
    Read_Excel = rs
    rs = Nothing
    Exit Function
fix_err:
    Debug.Print(Err.Description + " " + _
                Err.Source, vbCritical, "Import")
    Err.Clear()
End Function

Cells longer than 255 chars are getting truncated, and I'm not sure if there is a way to stop it easily?

Update: The truncation only seems to happen if I select Distinct. If I leave the Distinct off it shows the full cell.

Answer 1

My method's a bit different - opening excel files via OleDbAdapter, but I've solved your issue before using it. It's C# but should be easily transposed to vb.net. Try this OleDBAdapter Excel QA I posted via stack overflow.

I have a worksheet cell (Rows[0][4]) w/ 445 characters and it worked fine... Add this to the end of the code for the output

// DataSet cell debug/output:          
Object row0Col3 = ds.Tables["xlsImport"].Rows[0][2];
Object row0Col4 = ds.Tables["xlsImport"].Rows[0][4];

string rowZeroColumn3 = row0Col3.ToString();
string rowZeroColumn4 = row0Col4.ToString();

Console.WriteLine("Row 0, Col 4 string length: {0} " + Environment.NewLine + "Excel content: {1}", rowZeroColumn4.Length, rowZeroColumn4);           

Answer 2

Memo-type fields (columns) will be truncated if you do anything that changes them to text-type fields with the Jet driver. It may be possible to use a sub-query to get distinct records and avoid Distinct.

This reference is for Access, but it is still Jet, so nearly everything applies: Truncation of Memo fields