August 27, 2014 at 1:51 pm
I am trying to import data into a database from an excel CSV file and some of the columns' rows contains values like (123.45) and (678.90) and they should be negative values. I need to transform that data to save in the table as -123.45 and -678.90. The columns in the destination table are in decimal(19,2) data type.
I have tried the following in conditional split transformation editor and failed
REPLACE([QuantityUnits],"(","-")
REPLACE(QuantityUnits,')','')
I have tried the following in Derived Column Transformation editor and failed
REPLACE(REPLACE([QuantityUnits],”(“,”-”),”)“,””)
SUBSTRING([QuantityUnits],1,1) == "(" ? REPLACE(REPLACE([QuantityUnits],"(","-"),")","") : [QuantityUnits]
LEFT([QuantityUnits],1) == "(" ? REPLACE(REPLACE([QuantityUnits],"(","-"),")","") : [QuantityUnits]
LEFT([QuantityUnits],1) == "(" ? -1*(SUBSTRING([QuantityUnits],2,(CHARINDEX(")", [QuantityUnits])-1))) : [QuantityUnits]
CASE WHEN LEFT(QuantityUnits,1) = '(' THEN -1 ELSE 0 END * CAST(REPLACE(REPLACE(QuantityUnits,'(',''),')','') AS DECIMAL(19,2))
FINDSTRING("(", QuantityUnits, 1) <> 0 : SUBSTRING(QuantityUnits, FINDSTRING ("(", QuantityUnits, 1) + 1, FINDSTRING (")", QuantityUnits,1) - 1 - LEN(FINDSTRING ("(", QuantityUnits,1) ))
I have tried changing the data type in the flat file connection manager to string data type and that doesnt help
The csv file's problem columns have negative values that are represented like (2.5) and values with over 3 digits represented like " 1,074 ". The quotes are handled by my specification of the text qualifier but I am stuck on converting the negative values.
I used a Flat File Connection Manager with one file to verify i have the setup correct. When I go to Preview, my data lines up fine. However, I am trying to use a Multiple Flat Files Connection Manager because I have several files that I need to move from one folder
August 27, 2014 at 2:38 pm
Can you upload the sample CSV file. I am not able to reproduce the problem.
August 27, 2014 at 2:52 pm
Quick question, why the non-standard representation of negative numbers? Just open the file in Excel and change the number formatting.
😎
August 27, 2014 at 3:34 pm
i am not sure how this will show up after changing the file extension. i wasnt able to upload as .csv
also, i am not going to be opening up the hundreds of files to edit each instance before running the package. furthermore, the package will be running overnight, pulling the data from the source .csv files that i receive.
August 27, 2014 at 10:10 pm
errolthomasql (8/27/2014)
i am not sure how this will show up after changing the file extension. i wasnt able to upload as .csvalso, i am not going to be opening up the hundreds of files to edit each instance before running the package. furthermore, the package will be running overnight, pulling the data from the source .csv files that i receive.
Point taken;-)
Another suggestion, use a derived column to add a sign column for each of the value columns, assign -1 if the value column has the parenthesis and 1 if it hasn't. In the same derived column, simply remove the parentheses from the values and convert the column to numeric.
Alternative would be import the whole thing into a staging table and manipulate it in T-SQL.
😎
August 28, 2014 at 1:51 am
I also like the derived column idea.
Replace ')' with '' (empty string).
Replace '(' with '-' (minus)
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
August 28, 2014 at 8:51 am
I have tried the following in Derived Column Transformation editor and failed
REPLACE(REPLACE([QuantityUnits],”(“,”-”),”)“,””)
I don't understand whythat is not working for you in the Derived Column Transformation .
I used exactly the same thing and works fine
any way attached is the package for the sample data that you provided.
After downlaoding, rename the package to .dtsx. I built this on VS2008
Below is the table structure
CREATE TABLE dbo.[Sample_CSV] (
[Co] int,
[Company Name] varchar(50),
[Customer Number]int,
[Customer Name] varchar(50),
[Invoice Date] datetime,
[Invoice] int,
[Units] int,
[ Invoice Price ] decimal(18,0),
[ Total Invoice ] decimal(18,0),
[ Accrual ] decimal(18,0),
[ Total Accrual ] decimal(18,0)
)
August 28, 2014 at 9:13 am
all of you provided very helpful feedback which aligned with most of what i was trying. i finally figured out my problem. since i cant change the data type for existing columns in the Derived Column transformation, i went to the advanced editor of the Flat File Source task. On the Input and OUtput Properties tab, under Flat File Source Output > Output Columns, I had to change the data type properties there. Only then would all of your suggestions (and some of my failed attempts) work. Thank you all
August 28, 2014 at 9:41 am
errolthomasql (8/28/2014)
since i cant change the data type for existing columns in the Derived Column transformation
We cann't change the data type for existing columns but you can always use the <add as new column> instead of replace in the derived column transformation.
August 29, 2014 at 6:52 am
Okay, I will add new columns and alter the mappings in my OLE DB destination. thank you
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply