SSIS package converting int's to text in Excel

[doofy]

I know this is more of a Sql Server question, but I haven't gotten a
response on that newsgroup as of yet.

I've got a SQL Server SSIS package that takes data from a SS table, and
sends it to an Excel destination.

When it lands in Excel, the numeric data is being saved as text, which
means the charts that I have pointing to those data areas are not
showing any values.

If the fields are int's in SS, why are they getting written as text in
Excel? How can I intercede? I've tried looking at the definition of
the porting mechanism, but it doesn't give me any handles to tweak.

I tried formatting the XL columns to numeric, but that doesn't work, as
I suspected it would not.

 

[Guest]

Hi,

If in your excel sheet you see a small green triangle in the left top of
cell that your number is in so when you active the cell you see a rectangle
in the left side of the cell with the symbol "!" just click on the rectangle
and you see a menu and choos convert to number also you can active all your
cells and do it once.

Thanks,

 

[doofy]

Hi,

If in your excel sheet you see a small green triangle in the left top of
cell that your number is in so when you active the cell you see a rectangle
in the left side of the cell with the symbol "!" just click on the rectangle
and you see a menu and choos convert to number also you can active all your
cells and do it once.

Thanks,

Thanks. That was somewhat helpful, but it doesn't totally get me there
yet. Let me explain a little more.

I've got maybe four worksheets with data. 3 of them have the triangles.
One does not.

Problem is, I'm trying to automate this report, so I don't want to futz
with it after the data is ported over.

Any other feedback? Any way to permanently format cells to accept only
numeric. This is one of my big gripes about spreadsheets in general.

 

[Peo Sjoblom]

I agree that it is annoying. You can convert multiple cells by copying an
empty cell (preferably preformatted with the number format you want), then
select all the imports and do edit>paste special and select add. You could
obviously do this and record a macro and run this macro whenever you import
data

 

[doofy]

I agree that it is annoying. You can convert multiple cells by copying an
empty cell (preferably preformatted with the number format you want), then
select all the imports and do edit>paste special and select add. You could
obviously do this and record a macro and run this macro whenever you import
data

Two issues:

When porting from SSIS, there could be an issue about it not putting the
data in the right spot if there's anything in the fields. I'm not
talking about values, but possibly formatting. I've ported data over,
then deleted the data (by cell range, not by deleting rows), and when it
ported the next bank of data, it did it below where the first bank was,
even though the cells were "empty". Not sure what it doesn't like.

Next issue is, it's supposed to be an automated report, and the client I
send it to doesn't want to have to run macros to bring things up to
snuff. They might have a company policy about disabling macros when
opening up a file.

So, not sure what to do.

I guess the other thing I could do, and it's really kludgy, is to
reference the text numbers by yet another worksheet. The "=" formula
will convert for me, then run my graphs off the reference worksheet.
This gets really unwieldy visually though, unless I hide worksheets.