Änderungen
Die Seite wurde neu angelegt: „== Aufgabe == Ein VARCHAR beinhaltet einen CSV String, der innerhalb des SQL Servers ausgewertet und in eine Tabelle umgewandelt werden soll. Die Funktion OPE…“
== Aufgabe ==
Ein VARCHAR beinhaltet einen CSV String, der innerhalb des SQL Servers ausgewertet und in eine Tabelle umgewandelt werden soll. Die Funktion OPENROWSET oder ähnliche können nur auf Dateien im Dateisystem zugreifen.
Das Skript selber sollte aber nur für kleine Datenmengen verwendet werden, da es im Vergleich sehr langsam ist. Bei grösseren Datenmengen sollte entweder auf die CLR oder auf einen Bulk Import von einer Datei im Dateisystem umgestellt werden.
=== Skript SplitCSV ===
Mit diesem Skript wird ein VARCHAR, welches einen CSV String enthält, in eine Tabelle mit 3 Spalten umgewandelt. Mit einer weiteren Funktion können diese Zeilen dann entsprechend in eine Tabelle zusammengefasst werden. Ggf. kann das auch bereits in einem Schritt erfolgen und dadurch die Laufzeit verkürzt werden. So ist es aber allgemeiner.
* @CSVContent : Der String mit dem CSV Input
* @ColumnDelimeter : Spaltentrennzeichen
* @RowDelimeter : Zeilentrennzeichen
Ausgegeben wird eine Tabelle:
* value varchar(max) not null : Der Wert des einzelnen Feldes
* rownr int not null : Die Nummer der Zeile, in der der Wert im CSV String steht
* colnr int not null : Die Nummer der Spalte, in der der Wert im CSV String steht
<source lang="tsql">
ALTER FUNCTION [dbo].[SplitCSV]
(
@CSVContent varchar(max),
@ColumnDelimeter char,
@RowDelimeter char
)
RETURNS @temptable
TABLE
(
-- Add the column definitions for the TABLE variable here
value varchar(max) not null,
rownr int not null,
colnr int not null
,unique clustered (rownr, colnr)
)
AS
BEGIN
declare @rowcounter int;
declare @colcounter int;
declare @idx int;
declare @length int;
declare @startidx int;
declare @rowidx int;
declare @startrowidx int;
--declare @element varchar(max);
-- init
select @idx = 1;
select @rowcounter = 1;
select @colcounter = 1;
-- make sure there is data
set @length = len(@CSVContent);
if @length<1 or @CSVContent is null return
-- and now just go through the content
while @idx!= 0
begin
set @startidx = @idx;
set @idx = charindex(@RowDelimeter,@CSVContent, @startidx);
if @idx=0 -- that is the end of the file
set @idx = @length
-- now I've got the row
if((@idx - @startidx)>0)
begin
-- row is not empty
set @colcounter = 1;
set @rowidx = @startidx;
while (@rowidx < (@idx-1))
begin
set @startrowidx = @rowidx;
set @rowidx = charindex(@ColumnDelimeter,@CSVContent, @startrowidx);
if (@rowidx > @idx)
set @rowidx = @idx;
if @rowidx=0 -- that is the end of the file, not found anything anymore
set @rowidx = @idx + 1;
--set @element= substring(@CSVContent,@startrowidx, @rowidx - @startrowidx);
if (@rowidx - @startrowidx) > 0
insert into @temptable(value, rownr, colnr) values(substring(@CSVContent,@startrowidx, @rowidx - @startrowidx) , @rowcounter, @colcounter);
set @colcounter = @colcounter + 1;
set @rowidx = @rowidx + 1;
end
end
set @rowcounter = @rowcounter + 1;
-- at the end
if @length = @idx break
-- not the end, next line please
set @idx = @idx + 1;
end
return
END
</source>
=== Skript Pivot ===
Mit diesem Skript wird die Ausgabe der SplitCSV Funktion in eine Tabelle umgewandelt. Diese Funktion ist abhängig von den Daten, die in dem CSV Skript stehen und in welche Datentypen die einzelnen Inhalte. Das Skript dient nur als Beispiel, wie es gemacht werden könnte. Ggf. kann das ganze auch direkt in das SplitCSV Skript eingebaut werden. Das würde den Vorgang nochmal beschleunigen. Falls die Daten aber zu Gross werden, ist eine T-SQL Lösung so oder so nicht die richtige Wahl.
<source lang="tsql">
ALTER FUNCTION [dbo].[GetContent]
(
@CSVContent varchar(max),
@ColumnDelimeter char,
@RowDelimeter char
)
RETURNS TABLE
AS
RETURN
(
SELECT convert(varchar(10), [1]) as feld1,
convert(varchar(20), [2]) as feld2,
convert(varchar(100), [3]) as feld3,
convert(varchar(5), [4]) as feld4
FROM
(SELECT value, rownr, colnr
FROM [dbo].[SplitCSV] (@CSVContent,@ColumnDelimeter,@RowDelimeter)
) AS SourceTable
PIVOT
(
min(value)
FOR colnr IN ([1], [2], [3],[4])
) AS PivotTable
)
</source>
Ein VARCHAR beinhaltet einen CSV String, der innerhalb des SQL Servers ausgewertet und in eine Tabelle umgewandelt werden soll. Die Funktion OPENROWSET oder ähnliche können nur auf Dateien im Dateisystem zugreifen.
Das Skript selber sollte aber nur für kleine Datenmengen verwendet werden, da es im Vergleich sehr langsam ist. Bei grösseren Datenmengen sollte entweder auf die CLR oder auf einen Bulk Import von einer Datei im Dateisystem umgestellt werden.
=== Skript SplitCSV ===
Mit diesem Skript wird ein VARCHAR, welches einen CSV String enthält, in eine Tabelle mit 3 Spalten umgewandelt. Mit einer weiteren Funktion können diese Zeilen dann entsprechend in eine Tabelle zusammengefasst werden. Ggf. kann das auch bereits in einem Schritt erfolgen und dadurch die Laufzeit verkürzt werden. So ist es aber allgemeiner.
* @CSVContent : Der String mit dem CSV Input
* @ColumnDelimeter : Spaltentrennzeichen
* @RowDelimeter : Zeilentrennzeichen
Ausgegeben wird eine Tabelle:
* value varchar(max) not null : Der Wert des einzelnen Feldes
* rownr int not null : Die Nummer der Zeile, in der der Wert im CSV String steht
* colnr int not null : Die Nummer der Spalte, in der der Wert im CSV String steht
<source lang="tsql">
ALTER FUNCTION [dbo].[SplitCSV]
(
@CSVContent varchar(max),
@ColumnDelimeter char,
@RowDelimeter char
)
RETURNS @temptable
TABLE
(
-- Add the column definitions for the TABLE variable here
value varchar(max) not null,
rownr int not null,
colnr int not null
,unique clustered (rownr, colnr)
)
AS
BEGIN
declare @rowcounter int;
declare @colcounter int;
declare @idx int;
declare @length int;
declare @startidx int;
declare @rowidx int;
declare @startrowidx int;
--declare @element varchar(max);
-- init
select @idx = 1;
select @rowcounter = 1;
select @colcounter = 1;
-- make sure there is data
set @length = len(@CSVContent);
if @length<1 or @CSVContent is null return
-- and now just go through the content
while @idx!= 0
begin
set @startidx = @idx;
set @idx = charindex(@RowDelimeter,@CSVContent, @startidx);
if @idx=0 -- that is the end of the file
set @idx = @length
-- now I've got the row
if((@idx - @startidx)>0)
begin
-- row is not empty
set @colcounter = 1;
set @rowidx = @startidx;
while (@rowidx < (@idx-1))
begin
set @startrowidx = @rowidx;
set @rowidx = charindex(@ColumnDelimeter,@CSVContent, @startrowidx);
if (@rowidx > @idx)
set @rowidx = @idx;
if @rowidx=0 -- that is the end of the file, not found anything anymore
set @rowidx = @idx + 1;
--set @element= substring(@CSVContent,@startrowidx, @rowidx - @startrowidx);
if (@rowidx - @startrowidx) > 0
insert into @temptable(value, rownr, colnr) values(substring(@CSVContent,@startrowidx, @rowidx - @startrowidx) , @rowcounter, @colcounter);
set @colcounter = @colcounter + 1;
set @rowidx = @rowidx + 1;
end
end
set @rowcounter = @rowcounter + 1;
-- at the end
if @length = @idx break
-- not the end, next line please
set @idx = @idx + 1;
end
return
END
</source>
=== Skript Pivot ===
Mit diesem Skript wird die Ausgabe der SplitCSV Funktion in eine Tabelle umgewandelt. Diese Funktion ist abhängig von den Daten, die in dem CSV Skript stehen und in welche Datentypen die einzelnen Inhalte. Das Skript dient nur als Beispiel, wie es gemacht werden könnte. Ggf. kann das ganze auch direkt in das SplitCSV Skript eingebaut werden. Das würde den Vorgang nochmal beschleunigen. Falls die Daten aber zu Gross werden, ist eine T-SQL Lösung so oder so nicht die richtige Wahl.
<source lang="tsql">
ALTER FUNCTION [dbo].[GetContent]
(
@CSVContent varchar(max),
@ColumnDelimeter char,
@RowDelimeter char
)
RETURNS TABLE
AS
RETURN
(
SELECT convert(varchar(10), [1]) as feld1,
convert(varchar(20), [2]) as feld2,
convert(varchar(100), [3]) as feld3,
convert(varchar(5), [4]) as feld4
FROM
(SELECT value, rownr, colnr
FROM [dbo].[SplitCSV] (@CSVContent,@ColumnDelimeter,@RowDelimeter)
) AS SourceTable
PIVOT
(
min(value)
FOR colnr IN ([1], [2], [3],[4])
) AS PivotTable
)
</source>
