Excel Destination Number Conversion Error

In my SSIS project I am populating an Excel spreadsheet with several worksheets. When I define a worksheet in Excel Destination Editor, I use Create Table SQL statement to create a worksheet. Most of the fields are defined as numbers, however when job runs my numeric fields appear as text in Excel with yellow warning sign - 'The number in this cell is formatted as text or preceeded by an apostrophe'. I need my numbers to appear as numbers. Is there a workaround for this?

Are you specifying Import Mode by using IMEX=1 in your connection string? This forces all data to text.

The Excel driver does store what it believes to be text data preceded by the apostrophe, to force the Excel application in turn to recognize it without question as text values. This implies to me that the columns in question contain at least some values that the driver is interpreting as text.

The BOL topic on the Excel Source contains some information that explains some of the behaviors of the Jet Excel driver.

-Doug

|||

Doug,

Thanks a lot for advice. I tried to put IMEX=0 (2) into a connection string, but it did not solve the problem. I wonder what the default is if IMEX parameter is not specified?

Sergey

|||

Sergey,

I was not encouraging the use of Import Mode...on the contrary, this forces everything to text and would presumably add the opening apostrophe everywhere.

You may want to try allowing the Import and Export Wizard to build your "base" package and add necessary Data Conversion tasks etc. for you. Then you can customize the package afterward.

Be careful to check that the driver is recognizing your numeric columns as numeric, by examining the data types of columns in the various components.

-Doug

Examples for these.

Have come across the following guidelines in a few sites:
-> Avoid using data conversion functions in the WHERE clause of a query.
-> Use of a derived query in place of a cursor.
Although the reasoning is allright, can someone please provide examples
that can be used to illustrate these?
Thank you
sqlcatz
sql
SELECT * FROM Orders WHERE OrderDate <'20040101' AND OrderDate>='19970704'
SELECT * FROM
(
SELECT OrderID,CONVERT(CHAR(8),OrderDate,112) AS OrderDate
FROM Orders
) AS Deriv
WHERE OrderDate <'20040101' AND OrderDate>='19970704'
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:01B15B2B-322A-4EB4-9429-F58EE5B797AE@.microsoft.com...
> Have come across the following guidelines in a few sites:
> -> Avoid using data conversion functions in the WHERE clause of a query.
> -> Use of a derived query in place of a cursor.
> Although the reasoning is allright, can someone please provide examples
> that can be used to illustrate these?
> Thank you
> sqlcatz
>
|||Uri,
Thank you for the quick response!
What I was looking out for was something along these lines:
Write a Query that uses a data conversion function in the where clause - it
gives a particluar resultset.
How can I get the same resultset without using a data conversion function in
the where clause?
I want to illustrate the fact that you can achieve the same result without
having a data conversion function in the where clause.
Same holds true for the Cursor - derived table...
Cheers!
|||SQL
>How can I get the same resultset >without using a data conversion >function
in
>the where clause?
SELECT * FROM
(
SELECT OrderID,CONVERT(CHAR(8),OrderDate,112) AS OrderDate
FROM Orders
) AS Deriv
WHERE OrderDate <'20040101' AND OrderDate>='19970704'
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:AF1ED9E1-6C47-4789-B589-B2D9460DC80E@.microsoft.com...
> Uri,
> Thank you for the quick response!
> What I was looking out for was something along these lines:
> Write a Query that uses a data conversion function in the where clause -
it
> gives a particluar resultset.
> How can I get the same resultset without using a data conversion function
in
> the where clause?
> I want to illustrate the fact that you can achieve the same result without
> having a data conversion function in the where clause.
> Same holds true for the Cursor - derived table...
> Cheers!
>
|||> Write a Query that uses a data conversion function in the where clause -
it
> gives a particluar resultset.
> How can I get the same resultset without using a data conversion function
in
> the where clause?
What "same resultset"?
http://www.aspfaq.com/
(Reverse address to reply.)
|||One example you can find in my article on datetime: http://www.karaszi.com/SQLServer/info_datetime.asp
Search for below query:
SELECT c1, dt FROM #dts WHERE CONVERT(char(8), dt, 112) = '20040305'
And you'll find reasoning behind it and finally my suggested query, which doesn't do a CONVERT on the column
side.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:AF1ED9E1-6C47-4789-B589-B2D9460DC80E@.microsoft.com...
> Uri,
> Thank you for the quick response!
> What I was looking out for was something along these lines:
> Write a Query that uses a data conversion function in the where clause - it
> gives a particluar resultset.
> How can I get the same resultset without using a data conversion function in
> the where clause?
> I want to illustrate the fact that you can achieve the same result without
> having a data conversion function in the where clause.
> Same holds true for the Cursor - derived table...
> Cheers!
>