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 «««34567»»»

Counting the characters in a string before a space Expand / Collapse
Author
Message
Posted Thursday, June 12, 2014 3:54 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:15 PM
Points: 3,564, Visits: 7,702
Did you even try to change the SSIS package to avoid all these complications?


Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1580288
Posted Thursday, June 12, 2014 3:57 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 1:00 PM
Points: 63, Visits: 160
No I figured I would get the tsql working first, and then attempt that change.
Post #1580289
Posted Thursday, June 12, 2014 4:01 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:15 PM
Points: 3,564, Visits: 7,702
cbrammer1219 (6/12/2014)
No I figured I would get the tsql working first, and then attempt that change.

If you fix the data import, you don't need to work that hard on the t-sql.



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1580290
Posted Saturday, June 14, 2014 7:17 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:36 AM
Points: 36,995, Visits: 31,524
Luis Cazares (6/12/2014)
cbrammer1219 (6/12/2014)
No I figured I would get the tsql working first, and then attempt that change.

If you fix the data import, you don't need to work that hard on the t-sql.


+1 GAZILLION-TRILLION-BILLION!!!!

At the time I first looked at it, only 2 people had looked at the text file that the OP posted. As with most telephone systems, it's nothing more than a mostly simple fixed-field-format text file that can be easily resolved/imported/parsed either by using a BULK INSERT of the entire row and using SUBSTRING to parse the fields, or by using BULK INSERT with a BCP format file to do the parsing and most of the data validation for you. For the most part, there's nothing complicated about importing this file in a columnar fashion to a staging table for validation.

The proverbial fly-in-the-ointment is in the 5th field of the file. It contains many different types of data depending on (I would imagine) the nature of the CDR (Call Detail Record, which is what the file contains). Here's a sample of what those damned things look like and only someone with the "book" on what the record layout and field definitions of the CDRs would be able to interpret. Even though there are spaces in this field, they should NOT be interpreted as column delimiters in the overall columnar import of the CDRs. They are, however, column delimiters within the field and will need a bit of post processing. Such post processing would be determined by what "the book" says about the content of this 5th field.

1403 17815051438          
2422 16176660248
T7818592700 2074
T2074 15088720422
T2074 915088720422
P105 101 101
T8*029 8*042705 2704 91781
1 17818592702



I'll be back...



--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1580839
Posted Saturday, June 14, 2014 9:21 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 10:21 PM
Points: 1,795, Visits: 4,681
Jeff Moden (6/14/2014)
I'll be back...


It certainly looks, smells and tastes like a CDR, question is what is the originating system, record type and record format? Many CDR formats are slightly tricky, multi record type, combination of fixed and variable fields or even mix of text and binary such as some ASN.1.

Stay tuned....
Post #1580843
Posted Saturday, June 14, 2014 9:39 AM This worked for the OP Answer marked as solution


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:34 AM
Points: 1,914, Visits: 19,149
if as OP was specific about "spaces"...heres a first cut ....may be a start??

--drop table #tempcall
SELECT
ID = IDENTITY(INT, 1, 1),
RTRIM(LTRIM(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING(calldata,0,7),'-',''),'%',''),'+',''),'/','-'))) as cDate,
RTRIM(LTRIM(SUBSTRING(calldata,7,9))) as cStartTime,
RTRIM(LTRIM(SUBSTRING(calldata,19,9))) as cDuration,
RTRIM(LTRIM(SUBSTRING(calldata,29,6))) as callingparty,
RTRIM(LTRIM(SUBSTRING(calldata,41,6))) as origCall,
RTRIM(LTRIM(SUBSTRING(calldata,47,19))) as cDestination,
REPLACE(RTRIM(LTRIM(SUBSTRING(calldata,34,7))),'*','') as cTimetoAnswer,
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(RTRIM(LTRIM(SUBSTRING(calldata,41,28))),'A',''),'B',''),'I',''),'E',''),'T',''),'*','') as cDigitsDialed,
RTRIM(LTRIM(SUBSTRING(calldata,69,6))) as calledparty,
RTRIM(LTRIM(SUBSTRING(calldata,108,3))) as cSystemID,
RTRIM(LTRIM(SUBSTRING(calldata,112,19))) as cANI,
RTRIM(LTRIM(SUBSTRING(calldata,124,21))) as cDNIS,
RTRIM(LTRIM(SUBSTRING(calldata,148,15))) as cCallID,
RTRIM(LTRIM(SUBSTRING(calldata,162,2))) as cCallIDSeq
into #tempcall
FROM [dbo].[MitelCallTrace]
WHERE len(calldata) <> 0 AND SUBSTRING(calldata, 0, 7) <> ''
;

--select * from #tempcall
WITH cdsplit as (
SELECT ID
, MAX(CASE WHEN ItemNumber = 1 THEN Item END) p1
, MAX(CASE WHEN ItemNumber = 2 THEN Item END) p2
, MAX(CASE WHEN ItemNumber = 3 THEN Item END) p3
, MAX(CASE WHEN ItemNumber = 4 THEN Item END) p4
FROM #tempcall
CROSS APPLY dbo.DelimitedSplit8K(cDestination, ' ') split
GROUP BY ID
)


SELECT tc.ID
, tc.cDate
, tc.cStartTime
, tc.cDuration
, tc.callingparty
, tc.origCall
, tc.cdestination
, ISNULL(cdsplit.p1, '') AS cd1
, ISNULL(cdsplit.p2, '') AS cd2
, ISNULL(cdsplit.p3, '') AS cd3
, ISNULL(cdsplit.p4, '') AS cd4
, tc.cTimetoAnswer
, tc.cDigitsDialed
, tc.calledparty
, tc.cSystemID
, tc.cANI
, tc.cDNIS
, tc.cCallID
, tc.cCallIDSeq
FROM [#tempcall] AS tc
INNER JOIN cdsplit
ON tc.ID = cdsplit.ID





______________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Post #1580844
Posted Saturday, June 14, 2014 10:52 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 1:00 PM
Points: 63, Visits: 160
I will give this a try in a little while, I appreciate all that have stuck through this post. I will mark answered if this is a solution or even close.
Post #1580848
Posted Saturday, June 14, 2014 1:46 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:36 AM
Points: 36,995, Visits: 31,524
Ok... first of all, I made no real attempt to figure out what the names of the fields are. Somewhere, someone has a RECORD LAYOUT of what the actual fields are and so my example is just that... an example.

Second, someone needs to crown whomever decided that the date of the call should only have month and day. Why did they do that? To save 2 characters? I recommend that someone talk with the person that's providing this data and get that huge mistake repaired right away.

Shifting gears, we don't actually need SSIS for this. Yes, it's one of the tools that you could use for this but I never do. I've always done these types of simple imports and parses using T-SQL and never got out of the habit.

Shifting to yet another gear, there are a dozen different ways to import and parse this data. Since the OP appears (I could certainly be mistaken there) to be just starting out in the world of doing such imports, I'm taking the super simple mode in a fashion similar to what JLS did. Again, I didn't hazard a guess as to what the field names are. There are also some fields that I may have missed parsing on because some of what could be 2 fields looked like just one. You need the RECORD LAYOUT to tell and you'll also need the RECORD LAYOUT to figure out how Col05 should be split.

So, here we go. Step 1 is to import the data. This could be done using a BCP format file but, again, I'm trying to keep it simple until someone actually finds the RECORD LAYOUT so we can be more definitive. My testing was done by saving the file (that the OP provided) in the C:\Temp directory on my desktop box and the BULK INSERT will import from there. Obviously you'll need to change a bit of code for that and you'll need to make the dynamic if you want to make the code handle different files, but all that is comparatively easy to do once you have the hard-coding done and tested.

Here's a simple method to import the data...
 CREATE TABLE #MyHead (CDRData VARCHAR(300))

BULK INSERT #MyHead
FROM 'C:\Temp\T20140503.txt'
WITH (
FIELDTERMINATOR ='',
ROWTERMINATOR =''
)
;

Since we're importing whole rows, I forced an empty FIELDTERMINATOR. Yeah, you could have used the BLOB option here. Like a said, there's a dozen differnent ways to do this. Pick one.

Something else to notice is the double "newline" ROWTERMINATOR. That actually happens in the text file and makes it look like there's a blank line between the CDRs. Using the double newline terminator makes it so those blank lines disappear during the import.

Next, is to do the split of the fixed-field-length data like JLS did. To reiterate, some of the fields might need additional splitting but I don't know what they are because I don't have the RECORD LAYOUT. We do know that what I'm calling Col05 is going to need some additional post-process attention as will the call date and duration fields. As soon as the OP coughs up the RECORD LAYOUT, we can do more pretty easily using DelimitedSplit8K on that particular field.

 SELECT  CallDT         = RTRIM(SUBSTRING(CDRData,  2,15))
,CallDuration = RTRIM(SUBSTRING(CDRData, 18,10))
,Col03 = RTRIM(SUBSTRING(CDRData, 29, 8))
,Col04 = RTRIM(SUBSTRING(CDRData, 37, 5))
,Col05 = RTRIM(SUBSTRING(CDRData, 42,26))
,Col06 = RTRIM(SUBSTRING(CDRData, 68, 1))
,Col07 = RTRIM(SUBSTRING(CDRData, 69, 1))
,Col08 = RTRIM(SUBSTRING(CDRData, 70,38))
,Col09 = RTRIM(SUBSTRING(CDRData,108, 6))
,Col10 = RTRIM(SUBSTRING(CDRData,114,21))
,Col11 = RTRIM(SUBSTRING(CDRData,135,19))
,Col12 = RTRIM(SUBSTRING(CDRData,154, 9))
,Col13 = RTRIM(SUBSTRING(CDRData,163, 2))
,Col14 = RTRIM(SUBSTRING(CDRData,165,99))
FROM #MyHead
;

That's pretty much it except maybe for some datatype conversions. This is also the way I'll typically start an import project because it's easy to troubleshoot. Once I have this type of import running, I'll generally convert it to use a BCP format file and a staging table that has the actual columns I need along with the correct datatypes.

JLS may be spot on with his field names... I just didn't want to presume. He also took a shot at Col05 using DelimitedSplit8K but (IMHO) the OP needs to get the RECORD LAYOUT and the "Content Description" of what that field can actually contain before he can be certain of what to get out of that field.

The reason why I keep going on about the RECORD LAYOUT is because what the OP provided and what JLS used doesn't appear to actually line up with the data in the text file the OP provided. He also appears to have missed the fact that the file contains at least one other field that starts at character 165.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1580867
Posted Saturday, June 14, 2014 2:00 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:34 AM
Points: 1,914, Visits: 19,149
Jeff...+1 many times

______________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Post #1580868
Posted Saturday, June 14, 2014 2:03 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 1:00 PM
Points: 63, Visits: 160
Tell me about it I have told them before any projects are started the data needs cleaned up, these tables don't even have Pk or fk, they b Ave me joining on tables with no relation at all and I try to tell them they are reporting incorrectly. They won't listen. You should see the customer and contact tables, there is no data validation when input to the database. It's a nightmare. ...
Post #1580869
« Prev Topic | Next Topic »

Add to briefcase «««34567»»»

Permissions Expand / Collapse