April 10, 2018 at 9:11 am
hi ,
I have a package right now which loads data from Table to a .csv flatfile. When loaded to excel it shows -2.56. I wanted it to show (2.56) instead. How can we do that in SSIS package?
April 10, 2018 at 9:30 am
komal145 - Tuesday, April 10, 2018 9:11 AMhi ,
I have a package right now which loads data from Table to a .csv flatfile. When loaded to excel it shows -2.56. I wanted it to show (2.56) instead. How can we do that in SSIS package?
I tried to change the column in TSQL ( storedprocedure which loads that table) .....something like below. But it keeps throwing me error like "Cannot convert a char value to money. The char value has incorrect syntax."
case when Amount<0 THEN '(' + amount + ')'
ELSE Amount End AS Amount
April 10, 2018 at 9:43 am
komal145 - Tuesday, April 10, 2018 9:11 AMhi ,
I have a package right now which loads data from Table to a .csv flatfile. When loaded to excel it shows -2.56. I wanted it to show (2.56) instead. How can we do that in SSIS package?
That's configured in the cell format in Excel. It has nothing to do with the import process
April 10, 2018 at 10:08 am
komal145 - Tuesday, April 10, 2018 9:30 AMkomal145 - Tuesday, April 10, 2018 9:11 AMhi ,
I have a package right now which loads data from Table to a .csv flatfile. When loaded to excel it shows -2.56. I wanted it to show (2.56) instead. How can we do that in SSIS package?I tried to change the column in TSQL ( storedprocedure which loads that table) .....something like below. But it keeps throwing me error like "Cannot convert a char value to money. The char value has incorrect syntax."
case when Amount<0 THEN '(' + amount + ')'
ELSE Amount End AS Amount
As Luis indicates, you'd have to format the Excel spreadsheet with the necessary number format in order to "see it" that way. Such a format is not a default. You could automate the process by adding another step to your ETL process to open that file in Excel, and then use the Excel object model to programmatically make the format change to the relevant column, but that's a lot of work for something so easily done by the recipient..
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 10, 2018 at 10:15 am
sgmunson - Tuesday, April 10, 2018 10:08 AMkomal145 - Tuesday, April 10, 2018 9:30 AMI tried to change the column in TSQL ( storedprocedure which loads that table) .....something like below. But it keeps throwing me error like "Cannot convert a char value to money. The char value has incorrect syntax."case when Amount<0 THEN '(' + amount + ')'
ELSE Amount End AS AmountAs Luis indicates, you'd have to format the Excel spreadsheet with the necessary number format in order to "see it" that way. Such a format is not a default. You could automate the process by adding another step to your ETL process to open that file in Excel, and then use the Excel object model to programmatically make the format change to the relevant column, but that's a lot of work for something so easily done by the recipient..
Considering the OP has stated they're exporting to a CSV, then this isn't going to be possible anyway. Any formatting preferences will be lost if you open the file in Excel and save it again as a CSV, or you'll need to save the file as an xlsx (and then it isn't a XSV anymore).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 10, 2018 at 10:18 am
Thom A - Tuesday, April 10, 2018 10:15 AMsgmunson - Tuesday, April 10, 2018 10:08 AMkomal145 - Tuesday, April 10, 2018 9:30 AMI tried to change the column in TSQL ( storedprocedure which loads that table) .....something like below. But it keeps throwing me error like "Cannot convert a char value to money. The char value has incorrect syntax."case when Amount<0 THEN '(' + amount + ')'
ELSE Amount End AS AmountAs Luis indicates, you'd have to format the Excel spreadsheet with the necessary number format in order to "see it" that way. Such a format is not a default. You could automate the process by adding another step to your ETL process to open that file in Excel, and then use the Excel object model to programmatically make the format change to the relevant column, but that's a lot of work for something so easily done by the recipient..
Considering the OP has stated they're exporting to a CSV, then this isn't going to be possible anyway. Any formatting preferences will be lost if you open the file in Excel and save it again as a CSV, or you'll need to save the file as an xlsx (and then it isn't a XSV anymore).
Yep. Spoke too fast, so to speak. If the OP wants to see that value formatted, then they'll have to stop using a CSV file format and instead save it in Excel.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 10, 2018 at 12:22 pm
sgmunson - Tuesday, April 10, 2018 10:18 AMThom A - Tuesday, April 10, 2018 10:15 AMsgmunson - Tuesday, April 10, 2018 10:08 AMkomal145 - Tuesday, April 10, 2018 9:30 AMI tried to change the column in TSQL ( storedprocedure which loads that table) .....something like below. But it keeps throwing me error like "Cannot convert a char value to money. The char value has incorrect syntax."case when Amount<0 THEN '(' + amount + ')'
ELSE Amount End AS AmountAs Luis indicates, you'd have to format the Excel spreadsheet with the necessary number format in order to "see it" that way. Such a format is not a default. You could automate the process by adding another step to your ETL process to open that file in Excel, and then use the Excel object model to programmatically make the format change to the relevant column, but that's a lot of work for something so easily done by the recipient..
Considering the OP has stated they're exporting to a CSV, then this isn't going to be possible anyway. Any formatting preferences will be lost if you open the file in Excel and save it again as a CSV, or you'll need to save the file as an xlsx (and then it isn't a XSV anymore).
Yep. Spoke too fast, so to speak. If the OP wants to see that value formatted, then they'll have to stop using a CSV file format and instead save it in Excel.
I tried different ways in TSQL. Finally, sent the files to user and they said everything is good even with the negative values . Thanks everyone for taking your time and replying the question.
April 11, 2018 at 10:05 pm
Just to add in case anyone is interested, You If you really wanted to have the value come through with the parenthesis to your text file , you can convert the column to nvarchar in your ETL and add the parenthesis only if it is negative. If opened in Excel the application will recognise it as a number and you can apply formatting as you wish thereafter to the column.
----------------------------------------------------
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply