Hi everyone,
i need to split a string in different columns in my database.
But now i m using the len method to seperate my data, this works well with date and time function cause its static.
But when there's a name in the string it will give problems cause the LEN method is not flexibel.
So i try to use the split function, but i dont know where to put in my following query:
Declare @.fileline Nvarchar(100)
Declare @.Datum nvarchar(100), @.tijd nvarchar(100)
Declare @.Count INT
Createtable #h(s varchar(100))
bulkinsert #h from'c:\Logfile.txt'
Declare Log_cursor cursor
ForSelect s from #h
Open Log_cursor
Set @.count = 0
Fetchnextfrom Log_cursor into @.fileline
While@.@.fetch_status= 0
Begin
Select @.count = @.count + 1
If @.count = 1
Begin
Select @.Datum =CAST(Left(@.fileline,len 10 -Charindex(' ,',@.fileline))asnvarchar(100))
end
elseif @.Count = 2
Begin
Select @.Tijd =CAST(left(@.fileline,10 -Charindex(' ',@.fileline))asnvarchar(100))
Insertinto Logon (Datum,Tijd)
Values(@.datum,@.Tijd)
Select @.datum =Null
Select @.Count=0
END
Fetchnextfrom Log_cursor into @.fileline
END
CLOSE log_cursor
Deallocate log_cursor
Droptable #h
This is wat my result now is:
I have read many topics about split so pls dont post any links.
I tryied something and it doesnt work thats why i am posting this.
Tnx
XXX-Sheila
One way to apply the SPLIT function if you are using SQL 2005 is to use the CROSS APPLY join in something like:
Code Snippet
select datum,
b.OccurenceId as col#,
b.splitValue
from ( select '01-04-2007,08:24:05,D01-TS-503,wsmeel,Logon' as datum union all
select '01-04-2007,08:24:05,D01-TS-503,wsmeel,Logon' as datum
) a
cross apply split(datum, ',') b
/*
datum col# splitValue
- --
01-04-2007,08:24:05,D01-TS-503,wsmeel,Logon 1 01-04-2007
01-04-2007,08:24:05,D01-TS-503,wsmeel,Logon 2 08:24:05
01-04-2007,08:24:05,D01-TS-503,wsmeel,Logon 3 D01-TS-503
01-04-2007,08:24:05,D01-TS-503,wsmeel,Logon 4 wsmeel
01-04-2007,08:24:05,D01-TS-503,wsmeel,Logon 5 Logon
01-04-2007,08:24:05,D01-TS-503,wsmeel,Logon 1 01-04-2007
01-04-2007,08:24:05,D01-TS-503,wsmeel,Logon 2 08:24:05
01-04-2007,08:24:05,D01-TS-503,wsmeel,Logon 3 D01-TS-503
01-04-2007,08:24:05,D01-TS-503,wsmeel,Logon 4 wsmeel
01-04-2007,08:24:05,D01-TS-503,wsmeel,Logon 5 Logon
*/
The other thing to consider is to replace your cursor with a set based process. Also, this looks to me like a good candidate to use an SSIS package to do the work. If you make a package you shouldn't need to SPLIT function and you might be able to eliminate the step of loading the data into the temp table.
|||This looks like a candidate for BCP or BULK INSERT processing...
But beyond that, this function may help:
Code Snippet
IFEXISTS(
SELECT*FROMsys.objects
WHEREobject_id=OBJECT_ID(N'[Util].[list2set]')
ANDtypein(N'FN', N'IF', N'TF', N'FS', N'FT')
)
DROPFUNCTION [Util].[list2set];
GO
CREATEFUNCTION Util.list2set( @.list nvarchar(max), @.delim nvarchar(10))
RETURNS @.resultset TABLE( pos intidentity, item nvarchar(max))
AS
BEGIN
IFlen(@.list)<1 RETURN;
DECLARE @.xList XML;
-- no validity tests are performed, depending input this could fail
SET @.xList =Convert(XML,'<list><item>'+REPLACE(@.list, @.delim,'</item><item>')+'</item></list>')
INSERTINTO @.resultset
SELECT data.listitem.value('.','nvarchar(max)')as item
FROM @.xList.nodes('/list/item') data(listitem)
RETURN
END
GO
INSERT INTO Logon
SELECT * FROMutil.list2set(@.fileline, N',')
|||Guys very thnx for the reactions.
But the whole SSIS story looks a bit complicated somebody got some weblink so i can understand it.
M really a noob doing this but my will is to learn it.
DaleJ i really dont understand your code, and dont know how to put my own values into it.
Code Snippet
IFEXISTS(
SELECT*FROMsys.objects This should be my database?
WHEREobject_id=OBJECT_ID(N'[Util].[list2set]')
ANDtypein(N'FN', N'IF', N'TF', N'FS', N'FT') Wat means the (N'FN?
)
DROPFUNCTION [Util].[list2set]; Whats the util or listset?
GO
CREATEFUNCTION Util.list2set( @.list nvarchar(max), @.delim nvarchar(10)) Creating delimeter i understand
RETURNS @.resultset TABLE( pos intidentity, item nvarchar(max)) @.resulset should me my database and pos means the position
AS
BEGIN
IFlen(@.list)<1 RETURN;
DECLARE @.xList XML;
-- no validity tests are performed, depending input this could fail
SET @.xList =Convert(XML,'<list><item>'+REPLACE(@.list, @.delim,'</item><item>')+'</item></list>') i dont understand the above line
INSERTINTO @.resultset
SELECT data.listitem.value('.','nvarchar(max)')as item what should be the data.listitem?
FROM @.xList.nodes('/list/item') data(listitem)
RETURN
END
GO
INSERT INTO Logon
SELECT * FROMutil.list2set(@.fileline, N',') why the list2set?
Second thing where i must read in the txt file?
Sorry being not so smart as you guys.
|||Look this is my scenario:
This my logfile:
Datum,Tijd,Computernaam,Username,Actie
01-04-2007,08:24:05,D01-TS-S03,wsmeel,Logon
02-05-2007,06:23:05,D02-TS-S04,atest,Logoff
This need to be inserted in my database
Il show the database structure:
Now i have the following query:
Code Snippet
Declare @.fileline Nvarchar(100)
Declare @.Datum INT
Declare @.Count INT
Createtable #e(s varchar(100))
bulkinsert #e from'c:\Logfile.txt'
Declare Log_cursor cursor
ForSelect s from #e
Open Log_cursor
Set @.count = 0
Fetchnextfrom Log_cursor into @.fileline
While@.@.fetch_status= 0
Begin
Select @.count = @.count + 1
If @.count = 1
Begin
Select @.Datum =CAST(Right(@.fileline,len(@.fileline)-Charindex(' ',@.fileline))asINT)
Insertinto Logon (Datum)
Values(@.datum)
Select @.datum =Null
Select @.Count=0
END
Fetchnextfrom Log_cursor into @.fileline
END
CLOSE log_cursor
Deallocate log_cursor
Droptable #e
when is do this all the data from the logfile comes in 1 column, but it needs to split over different columns.
Here the solution,
Code Snippet
/*
Alter function split(@.input varchar(max), @.delimter varchar(10))
returns @.data table (OccurenceId int, SplitValue Varchar(max))
as
Begin
Declare @.Numbers Table(Number int);
Declare @.i as int;
Set @.i = 1
Set @.input= @.delimter + @.input + @.delimter;
While (@.i < 100)
Begin
Insert Into @.Numbers Values(@.i);
Set @.i = @.i +1;
End
Insert Into @.data
Select Row_Number() Over (Order By Number),Data From (Select Number,Substring(@.input,Number,CharIndex(@.delimter,@.input,Number) - Number) Data from @.Numbers Where Number <= Len(@.input) And Substring(@.input,Number-1,1)= @.delimter) as Data
Return;
End
*/
Select
UniqueId,
Max(Case When b.OccurenceId=1 Then b.splitValue End) Datum,
Max(Case When b.OccurenceId=2 Then b.splitValue End) Tijd,
Max(Case When b.OccurenceId=3 Then b.splitValue End) ComputerNaam,
Max(Case When b.OccurenceId=4 Then b.splitValue End) Username,
Max(Case When b.OccurenceId=5 Then b.splitValue End) Actie
from
(
Select *,Row_Number() Over (Order By Datum) as UniqueId
From
(
Select '01-04-2007,08:24:05,D01-TS-503,wsmeel,Logon' as datum
Union All
Select '01-04-2007,08:24:05,D01-TS-503,wsmeel,Logon' as datum
) Data
) A
Cross Apply
Split(datum, ',') B
Group BY
UniqueId
|||Sheila,
Try this:
Code Snippet
createtable dbo.Logon(Datum datetime, Tijd varchar(50), Computernaam varchar(50),
Username varchar(50), Actie varchar(50))
BULKINSERT dbo.Logon
FROM'C:\logfile.txt'
WITH
(
FIELDTERMINATOR=',',
ROWTERMINATOR='\n'
)
select*
from dbo.Logon
Since your table already exists, you can remove the 'create table' statement.
|||Yeah its working really tnx
Sheila,
No comments:
Post a Comment