Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SSIS package converting int's to text in Excel
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
SSIS package converting int's to text in Excel
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, -- Farhad Hodjat "doofy" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
SSIS package converting int's to text in Excel
Farhad wrote:
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
SSIS package converting int's to text in Excel
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 editpaste special and select add. You could obviously do this and record a macro and run this macro whenever you import data -- Regards, Peo Sjoblom "doofy" wrote in message ... Farhad wrote: 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
SSIS package converting int's to text in Excel
Peo Sjoblom wrote:
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 editpaste 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loosing format when converting from Excel to Text.. | Excel Discussion (Misc queries) | |||
excel chart rendering problem when using apache java Poi package. | Excel Discussion (Misc queries) | |||
converting excel to text or prn, losing format | Excel Discussion (Misc queries) | |||
Converting text to excel | Excel Discussion (Misc queries) | |||
Excel 2000 post-Service Pack 3 hotfix package Q841881 | Excel Discussion (Misc queries) |