Excel oledb字段被截断为255 [英] excel oledb fields truncated at 255

问题描述

我正在读取具有以下代码的excel文件:

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

长度超过255个字符的单元格被截断了，我不确定是否有办法轻松停止它?

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

更新:仅当我选择Distinct时，截断似乎才发生.如果我关闭Distinct，则显示完整的单元格.

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

推荐答案

如果使用Jet驱动程序将备注类型的字段(列)更改为文本类型的字段，则备注字段(列)将被截断.可能可以使用子查询来获取不同的记录并避免不同.

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.

此参考用于Access，但仍是Jet，因此几乎所有内容都适用:备注"字段的截断

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

这篇关于Excel oledb字段被截断为255的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！