dataissue(Resolved) - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

dataissue(Resolved)

Author

Topic

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-07-18 : 13:53:28

I have a data file as below

I have imported this data to a Raw table with Id as identity and Raw_data containing all the above

Table->Raw_tab(Id,Raw_data)
Table->Normal(Path,File,Level,Act,Status,Date,Time)--all varchar columns here

I want the output to be as in the Normal table, I have liberty to clean it in any either by script or by writing sp, tmp table/cursor etc anything will do, Below first line represents the column name of target( Normal) table


Path               File		Level    	Act    	status   Date  		Time
---------------------------------------------------------------------
Path: /usr/lib/kpc Ora14.sdk	1.4.2.20   	COMMIT  COMPLETE    01/25/12     13:02:59
Path: /usr/lib/kpc Tivoli.rte	3.7.1.0 	COMMIT  COMPLETE    01/25/12     12:32:14
.
.
.
Path: /usr2/lib/kpc Tivoli.rte 3.7.1.0   	COMMIT  COMPLETE     01/25/12     12:32:14
.
.

-Neil

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-07-18 : 15:55:09

Are you sure you can't perform the parsing as part of the initial load from the file to the raw table? What is the format of the file? typically it is saved as "delimited" or "fixed width". Looks like it may be fixed width.

Be One with the Optimizer
TG

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-07-19 : 03:53:07

It is space delimited, with multiple spaces, I can convert it to single space. as the data is not having spaces in it

-Neil

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-07-19 : 04:40:33

Cursor is taking lot of time for this..

-Neil

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-07-19 : 05:16:46

ID is it an identity ?

ID = 1 will contain the Path line
ID = 2, 4, 6 etc, even number will be the fileline
ID = 3, 5, 7 etc, will be the Level, Act, Status


select e.Raw_data, o.Raw_data
from   Raw_tab e
       inner join Raw_tab o on e.ID = o.ID - 1
where  e.ID % 2 = 0

then just do some string parsing on the e.Raw_data and o.Raw_data to extract the required information

KH
[spoiler]Time is always against us[/spoiler]

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-07-19 : 06:50:36

Yes odd even would have worked here only if the path hav not come in the file

-Neil

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-07-19 : 08:41:31

quote:
Originally posted by aakcse

Yes odd even would have worked here only if the path hav not come in the file

-Neil

It will still works. The INNER JOIN "on e.ID = o.ID - 1" and the WHERE "e.ID % 2 = 0" would have excluded ID = 1

You only need another query (or sub query to get the Path line)


select Path = (select Raw_data from Raw_tab where ID = 1),
       e.Raw_data, o.Raw_data
from   Raw_tab e
       inner join Raw_tab o on e.ID = o.ID - 1
where  e.ID % 2 = 0

KH
[spoiler]Time is always against us[/spoiler]

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-07-19 : 15:25:34

Thanks KH, if you see the data the path is coming multiple times and it can come at any line number, which might disturb the odd even combination

-Neil

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-08-05 : 15:43:59

hmm now here came the challenge to me.. few records are as below


File         Level     Act       Status       Date         Time
----------------------------------------------------------------------------
Path: /usr/lib/kpc
Ora14.sdk
1.4.2.20   COMMIT       COMPLETE     01/25/12     13:02:59

Tivoli.rte
3.7.1.0   COMMIT       COMPLETE     01/25/12     12:32:14
3.7.1.0   COMMIT       COMPLETE     01/25/12     12:32:14

Commit Complete are common in all these rows which are coming back 2 back, I didnt find more than this level like 3 rows below Tivoli.rte
there are very few records which have rows as above in entire file.

-Neil

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-08-05 : 15:55:12

out put for the second row should be of as below


Path: /usr/lib/kpc  Tivoli.rte  3.7.1.0   COMMIT       COMPLETE     01/25/12     12:32:14
Path: /usr/lib/kpc  Tivoli.rte  3.7.1.0   COMMIT       COMPLETE     01/25/12     12:32:14

-Neil

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-08-05 : 19:23:01

We can split this into 3 tables one with Path other with headers like 'Path: /usr/lib/kpc' and last one containing all commit comment lines.. commit & Comment is present in all lines except the one which has headers and path, hence this can be used in where clause...

now could anyone help me in joining these 3 tables and getting the data as below


Path: /usr/lib/kpc  Tivoli.rte  3.7.1.0   COMMIT       COMPLETE     01/25/12     12:32:14
Path: /usr/lib/kpc  Tivoli.rte  3.7.1.0   COMMIT       COMPLETE     01/25/12     12:32:14

-Neil

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources