Kyle,

Unfortunately, you'll have to iterate over the table column in the and call
TRIM on its value.

For Each r as DataRow in mDsExcel.Tables(0).Rows
 r(0) = TRIM(r(0))
Next

Ad.

¤ I have an application reading data from an Excel file and filling a SQL
¤ Server table.  When I read the Excel file, I want to be able to trim
¤ trailing spaces off the data as it's read into the dataset.  I'm connecting
¤ to the Excel file via an OleDbDataAdapter & OleDbCommand object.  All the
¤ code compiles and runs fine - I just want to trim any trailing spaces off of
¤ the data.
¤
¤ Here is a portion of the code I'm using:
¤
¤       Dim oCmdSelect As New OleDbCommand(moImportFormat.SelectFromStatement,
¤ moConnExcel)
¤       'Get the values in the Excel file and verify
¤       mDSExcel = New DataSet()
¤
¤       Dim oDA As New OleDbDataAdapter()
¤       oDA.AcceptChangesDuringFill = False
¤       oDA.SelectCommand = oCmdSelect
¤       oDA.Fill(mDSExcel, sTableName)
¤
¤ The SQL statement stored in moImportFormat.SelectFromStatement is:
¤
¤ SELECT STORENAME FROM [Sheet1$]
¤
¤ The trick, I believe, is to add a "trim" statement as in TRIM(STORENAME) or
¤ RTRIM(STORENAME).  However when I tried TRIM & RTRIM, I receive an error
¤ message.  When I take out the TRIM or RTRIM it works fine.  So the $1
¤ million dollar question is, is there a function that can "trim" data when
¤ being read in with an OleDbDataAdapter / OleDbCommand?

You didn't mention the error message, but if you're using the Jet OLEDB Provider w/the Excel ISAM
then the Trim functions should be available:

http://support.microsoft.com/default.aspx?scid=kb;en-us;239482

Paul
~~~~
Microsoft MVP (Visual Basic)