I've got some perfectly fine code that works well for importing data from an
Excel spreadsheet into a .NET DataTable, except I discovered today that it's
cutting off any cell that contains more than 250 characters at that
250-character limit.  Is that just the way it is, or is there some way around
it?

Thanks!

Are you sure your DataTable schema is correct?

Show quoteHide quote

I think so.  Everything works fine except that cells with more than 250
characters are truncated to 250 characters.  Here's the actual function code
(not mine, but pulled from somewhere on the web).  All variables that you
don't see declared are declared globally in the class.

Public Function GetDataTable(ByVal strSheetName As String) As DataTable

Dim strLocalSheetName, strRange, strSqlCommand As String

dtblExcel = New DataTable(strSheetName)
Try
  If (strSheetName.IndexOf("|") > 0) Then
    strLocalSheetName = strSheetName.Substring(0, strSheetName.IndexOf("|"))
    strRange = strSheetName.Substring(strSheetName.IndexOf("|") & 1)
    strSqlCommand = "SELECT * FROM [" & strLocalSheetName & "$" & strRange &
"]"
  Else
    strSqlCommand = "SELECT * FROM [" & strSheetName & "$]"
  End If

  dadExcel = New OleDbDataAdapter(strSqlCommand, conDBConnection)
  dadExcel.FillSchema(dtblExcel, SchemaType.Source)
  dadExcel.Fill(dtblExcel)
  conDBConnection.Close()

  Return dtblExcel
Catch
  Return Nothing
End Try

End Function

Show quoteHide quote

On Fri, 30 Jun 2006 17:43:02 -0700, Gary <G***@discussions.microsoft.com> wrote:

¤ I've got some perfectly fine code that works well for importing data from an
¤ Excel spreadsheet into a .NET DataTable, except I discovered today that it's
¤ cutting off any cell that contains more than 250 characters at that
¤ 250-character limit.  Is that just the way it is, or is there some way around
¤ it?

It's probably truncating at 255. Try adding the IMEX argument to your connection string.

        ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
         "Data Source=e:\My Documents\Book20.xls;Extended Properties=""Excel 8.0;HDR=NO;IMEX=1"""


Paul
~~~~
Microsoft MVP (Visual Basic)

Can't seem to speed up Rows.Add in DataTable
Cannot connect to MSSQL2005 (Local) Server?
Problems with IDataRecord.IsDBNull
Your kidding me right?
DataGrid in WinForm