Discussion:
Importing from formatted Excel cells using SQL Server 2000 DTS Iss
(too old to reply)
Darren
2008-12-12 15:05:00 UTC
Permalink
We have an Excel workbook where certain cells are formatted not to show the
decimal places (formatted as number with zero decimal places).

As a result of the formatting, the data imported is also without decimal
places and this gives issues with the final results of my SQL queries due to
value rounding at the source.

Is it possible to import the true figures contained within the workbook
without the need to re-format the cells to show the decimals prior to import?
Todd C
2008-12-12 16:53:02 UTC
Permalink
Are you using DTS or SSIS?
If SSIS, Try an OLE Db connection for Jet 4.0. In the Connection String
Extended Properties, add the following:

Excel 8.0;IMEX=1;HDR=Yes

The IMEX=1 delays type validation.

HTH
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]
Post by Darren
We have an Excel workbook where certain cells are formatted not to show the
decimal places (formatted as number with zero decimal places).
As a result of the formatting, the data imported is also without decimal
places and this gives issues with the final results of my SQL queries due to
value rounding at the source.
Is it possible to import the true figures contained within the workbook
without the need to re-format the cells to show the decimals prior to import?
Darren
2008-12-13 18:56:00 UTC
Permalink
I am using SQL 2000 DTS and I hadalready added IMEX=1 to the extended
properties for the Excel connection.
Post by Todd C
Are you using DTS or SSIS?
If SSIS, Try an OLE Db connection for Jet 4.0. In the Connection String
Excel 8.0;IMEX=1;HDR=Yes
The IMEX=1 delays type validation.
HTH
--
Todd C
[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]
Post by Darren
We have an Excel workbook where certain cells are formatted not to show the
decimal places (formatted as number with zero decimal places).
As a result of the formatting, the data imported is also without decimal
places and this gives issues with the final results of my SQL queries due to
value rounding at the source.
Is it possible to import the true figures contained within the workbook
without the need to re-format the cells to show the decimals prior to import?
Loading...