Reading Data From Excel Via ADO.NET

[Kyle Novak]

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?

Thanks,

Kyle

 

[Adrian Moore]

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.

 

[Paul Clement]

¤ 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)