SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


failing to convert numbers in parenthesis within a CSV file, to a SQL database as negative numbers,...


failing to convert numbers in parenthesis within a CSV file, to a SQL database as negative numbers, via SSIS

Author
Message
errolthomasql
errolthomasql
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 27
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
rxm119528
rxm119528
Mr or Mrs. 500
Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)

Group: General Forum Members
Points: 512 Visits: 804
Can you upload the sample CSV file. I am not able to reproduce the problem.
Eirikur Eiriksson
Eirikur Eiriksson
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15296 Visits: 18612
Quick question, why the non-standard representation of negative numbers? Just open the file in Excel and change the number formatting.
Cool
errolthomasql
errolthomasql
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 27
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.
Attachments
sampleCSV.txt (29 views, 1.00 KB)
Eirikur Eiriksson
Eirikur Eiriksson
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15296 Visits: 18612
errolthomasql (8/27/2014)
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.


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.
Cool
Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18738 Visits: 20452
I also like the derived column idea.

Replace ')' with '' (empty string).

Replace '(' with '-' (minus)


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
rxm119528
rxm119528
Mr or Mrs. 500
Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)

Group: General Forum Members
Points: 512 Visits: 804
[b]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)
)


Attachments
Package.txt (159 views, 86.00 KB)
errolthomasql
errolthomasql
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 27
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
rxm119528
rxm119528
Mr or Mrs. 500
Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)

Group: General Forum Members
Points: 512 Visits: 804
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.
errolthomasql
errolthomasql
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 27
Okay, I will add new columns and alter the mappings in my OLE DB destination. thank you
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search