November 23, 2010 at 7:28 am
I have a fixed-length file I need to import and then validate the number data to make sure it's a number.
I use this for validation:
Update CapitationSTG
Set Error = 1
Where
IsNumeric(IsNull(SAK_CAPITATION,0)) = 0
Or
IsNumeric(IsNull(DTE_CAPITATION,0)) = 0
Or
IsNumeric(IsNull(DTE_CAP_TXN,0)) = 0
Or
IsNumeric(IsNull(AMT_CAP_PAID,0)) = 0
Or
IsNumeric(IsNull(DTE_PAYMENT_BEGIN,0)) = 0
Or
IsNumeric(IsNull(DTE_PAYMENT_END,0)) = 0
Or
IsNumeric(IsNull(NUM_CAP_DAYS,0)) = 0
Or
IsNumeric(IsNull(DTE_PAYMENT_ISSUE,0)) = 0
Records with an Error field valued at 1 are then moved to an error table.
For this file though, I have encountered an odd behavior from IsNull, in that when two (2) null values come through in a 2 character field, IsNull returns nothing, as in NullNull, or 00 00.
Select CapID, IsNumeric(IsNull(NUM_CAP_DAYS,0)), IsNull(NUM_CAP_DAYS,0), Len(NUM_CAP_DAYS)
from dbo.CapitationSTG
Returns this:
F1 F2 F3 F4
21030 2
What do you suggest I do to handle this situation? I am trying to figure out something simple, without a replace or other complicated coding. Nothing has really come to mind yet.
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
November 23, 2010 at 8:37 am
Been messing with it a while, and this is what I have come up with. Taking other ideas though.
IsNumeric(IsNull(Replace(NUM_CAP_DAYS, Char(00), 0),0)) = 0
This returns a True for IsNumeric if the field contains one or two Nulls, or contains an actual number.
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
November 23, 2010 at 9:09 am
In importing from delimited text files, there's an option to treat consecutive delimiters as a single column, or something to that effect. You probably have that turned on.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 23, 2010 at 12:23 pm
Well the point in this step of the package I'm developing is to verify that the fixed-length records in this file (no delimiters) have only numerals in the locations of several fields that are set up as Numeric, Integer, or Decimal fields in the DB, because they are used for calculations.
After this step I need to transform the field into zero (0) if it is 2 Nulls, so it can be deposited into the data warehouse. It is also in this step I use a transformation in the SSIS package to convert the data type and get the decimals into the correct position, so I need not worry about converting data types in the previous step.
I am trying to redesign this package to be much faster, as it is loading 3.3M records at a time. This is why I would rather not have to run through the file with another language, if I can get away with it.
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
November 23, 2010 at 1:59 pm
Turns out SSIS didn't like the double Nulls and would not cooperate with my various attempts to remove the Nulls.
Here's how I handled it.
1. Load all data into a text field in the DB.
2. As this is fixed-length, add computed columns of VarChar(x) data type. Change the problem field into computed column that replaces the Nulls it finds with zeros, since most other records have at least one digit and a Null in it.
3. Validate the data through those computed columns, removing any data that does not convert to a number. (stored procedure)
4. Load the data from the staging table to the Prod table.
It's working, with no errors, and loaded 3.26M records in 14:10, on my WS, with no RAID. That seems pretty good to me.
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply