Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

failing to convert numbers in parenthesis within a CSV file, to a SQL database as negative numbers, via SSIS Expand / Collapse
Author
Message
Posted Wednesday, August 27, 2014 1:51 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 29, 2014 12:42 PM
Points: 7, 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
Post #1608011
Posted Wednesday, August 27, 2014 2:38 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, November 24, 2014 2:49 PM
Points: 300, Visits: 670
Can you upload the sample CSV file. I am not able to reproduce the problem.
Post #1608037
Posted Wednesday, August 27, 2014 2:52 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:54 AM
Points: 2,420, Visits: 6,726
Quick question, why the non-standard representation of negative numbers? Just open the file in Excel and change the number formatting.
Post #1608046
Posted Wednesday, August 27, 2014 3:34 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 29, 2014 12:42 PM
Points: 7, 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.


  Post Attachments 
sampleCSV.txt (10 views, 1.04 KB)
Post #1608063
Posted Wednesday, August 27, 2014 10:10 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:54 AM
Points: 2,420, Visits: 6,726
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.
Post #1608124
Posted Thursday, August 28, 2014 1:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:02 PM
Points: 5,259, Visits: 12,193
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1608172
Posted Thursday, August 28, 2014 8:51 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, November 24, 2014 2:49 PM
Points: 300, Visits: 670
[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)
)



  Post Attachments 
Package.txt (6 views, 86.73 KB)
Post #1608315
Posted Thursday, August 28, 2014 9:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 29, 2014 12:42 PM
Points: 7, 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
Post #1608321
Posted Thursday, August 28, 2014 9:41 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, November 24, 2014 2:49 PM
Points: 300, Visits: 670
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.
Post #1608325
Posted Friday, August 29, 2014 6:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 29, 2014 12:42 PM
Points: 7, Visits: 27
Okay, I will add new columns and alter the mappings in my OLE DB destination. thank you
Post #1608582
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse