All data is text in ADO Update to Excel...

  • Thread starter Thread starter Vagabond Software
  • Start date Start date
V

Vagabond Software

I am updating several excel worksheets using ADO.NET (OLEDB objects). Everything is working fine except all the data is inserted as text despite being typed in the DataTable object and when the Excel worksheet was actually created.

If the user-specified Excel Workbook does not exist, one is created and worksheets are added using "CREATE TABLE" statements with type-specific data columns. The data type for each column is specified in the DataTable object as well as the actual "CREATE TABLE" SQL statement.

However, if the user-specified workbook already exists, the worksheets will be updated correctly, but all data will be inserted as text values regardless of how it is typed in the DataTable.

Any help, suggestions, or advice would be greatly appreciated.

- Carl
 
Hello Carl,

From Excel automation, I recall the actual value of any particular cell was
inherently a string. The only thing that made it appear as a number (or
date, or currency, etc.) was the formatting of the cell.

It sounds like Excel does not maintain any detailed schema for its columns
(i.e. fields), therefore, it falls back and generates the values as string.
When you create the table, the schema comes from the .net dataset to the
Excel file vs. the other way around.

Without diving into the business of Excel automation... try experimenting
with exact syntax of the Update statement,

Good fun,

Chris




I am updating several excel worksheets using ADO.NET (OLEDB objects).
Everything is working fine except all the data is inserted as text despite
being typed in the DataTable object and when the Excel worksheet was
actually created.

If the user-specified Excel Workbook does not exist, one is created and
worksheets are added using "CREATE TABLE" statements with type-specific data
columns. The data type for each column is specified in the DataTable object
as well as the actual "CREATE TABLE" SQL statement.

However, if the user-specified workbook already exists, the worksheets will
be updated correctly, but all data will be inserted as text values
regardless of how it is typed in the DataTable.

Any help, suggestions, or advice would be greatly appreciated.

- Carl
 
Back
Top