Number of characters in "SELECT * from ExcelSheetName"

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!
 
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
 
¤ 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)
 
Back
Top