T-SQL MSSQL Reading CSV

Aus Software Entwicklung Projekte
Wechseln zu: Navigation, Suche

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
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

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.

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  
)