DTS Numbers export to Excel ends up as text

Question

DTS Numbers export to Excel ends up as text

StefanJ

SSCarpal Tunnel

Points: 4713
More actions
November 21, 2003 at 8:49 am

#107412

If I create a new spreadsheet in DTS numbers are exported correctly.
However if I delete all rows except the titles, or copy a template, the numbers end up as text (left aligned) in Excel.
I guess that the formatting is taken from the text titles. Is there a workaround for this?
November 24, 2003 at 8:00 am

This was removed by the editor as SPAM

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

StefanJ SSCarpal Tunnel Points: 4713 More actions · Answer 1

My workround was to add a blank row below the titles and format this in the way I wanted. The only downside is there is then one blank row below the titles but I guess I will have to live with this if noone has a better solution.

philcart SSC-Forever Points: 47796 More actions · Answer 2

Do you need the blank row for the formatting? Can't you apply the formatting to the title row?

Hope this helps

Phill Carter

--------------------

Colt 45 - the original point and click interface

--------------------
Colt 45 - the original point and click interface

StefanJ SSCarpal Tunnel Points: 4713 More actions · Answer 3

quote:
Do you need the blank row for the formatting? Can't you apply the formatting to the title row?

No this does not work. If you have text headings, then the numbers will be left aligned. If the headings are bold all the subsequent rows will also be bold.

quote:
Simply hide the blank row. It still works as the formatting source for rows inserted below, but does not affect the appearance of the report.

Now why didn't I think of that!!!!

All I need now, for life to be perfect, is for someone to tell me how I get commas in the thousands e.g. 1,000,000. Don't understand why it picks up that I don't want a decimal place but does not pick up #,##0 type formatting

philcart SSC-Forever Points: 47796 More actions · Answer 4

quote:
All I need now, for life to be perfect, is for someone to tell me how I get commas in the thousands e.g. 1,000,000. Don't understand why it picks up that I don't want a decimal place but does not pick up #,##0 type formatting

What is the output format of the values from SQL? Have you tried using the type parameter of the CONVERT statement to format the number with commas?

Hope this helps

Phill Carter

--------------------

Colt 45 - the original point and click interface

--------------------
Colt 45 - the original point and click interface

data_czar Ten Centuries Points: 1147 More actions · Answer 5

I posted this under another Excel question and it works pretty slick. You can apply formatting (Inserting rows, Titles, formatting to the detail, etc) to the excel file and it will be maintained even after the excel table is dropped and recreated.

================================================================

Use an Execute SQL task against the Excel connection:

DROP TABLE table_name

Then In a second Execute SQL task against the same connection:

CREATE TABLE `table_name` (

`ColumnName1` VarChar (8) ,

`ColumnName2` DateTime )

You can also apply formatting to this Excel file after the first load and it will maintain the formatting even with the DROP and CREATE.

Hope this helps.

-Corey

================================================================

StefanJ SSCarpal Tunnel Points: 4713 More actions · Answer 6

Corey, that's amazing. Did you find this documented anywhere. I would like to explore this further.

Regards

Stefan

data_czar Ten Centuries Points: 1147 More actions · Answer 7

quote:
Corey, that's amazing. Did you find this documented anywhere. I would like to explore this further.
Regards
Stefan

Actually I stumbled onto the formatting through trial and wow that's pretty cool(!) type work. The drop and recreate was borrowed from a previous employee's package in our company.

So basically I do not have a good source for you to investigate. I would be interested though if you find one.

ZenDada SSCarpal Tunnel Points: 4637 More actions · Answer 8

Okay, I tried this several different ways and could not produce your successful results. DTS was only able to successfully run the drop/create statements against an existing page$, not newly created table statements on the DTS side, and not on pre-created named ranges on the Excel side. And each time the package was re-run, the transformation would begin it's data insert at the first line BELOW the where the last dataset was prior to the drop statement. But the headers were still at the top of the page.

Any advice?

Jules

[font="Courier New"]ZenDada[/font]

philcart SSC-Forever Points: 47796 More actions · Answer 9

Don't use the '$' character in the name.

Hope this helps

Phill Carter

--------------------

Colt 45 - the original point and click interface

--------------------
Colt 45 - the original point and click interface

data_czar Ten Centuries Points: 1147 More actions · Answer 10

Was the data dropped from the prior run? If not, then the Drop was not successful.

I did not setup a named range in Excel before using the file in DTS.

I agree with Phill, do not use a $ in the table name.

robstp Old Hand Points: 384 More actions · Answer 11

How lucky I came in looking for a solution of clearing data from an Excel file that is repopulated on a weekly basis. Since Excel does not support a Delete function I was pulling my hair out thinking of a solution.

Thanks Corey