Problem
Eine große CSV Datei soll in eine Tabelle importiert werden. Hierbei sollen nicht alle Felder importiert werden.
Ansatz
Die Verwendung des Kommandos BULK INSERT wurde von Microsoft für sehr große Dateien vorgesehen, um den SQL Server ohne die Verwendung von langsamen Cursor-Iterationen zu füllen
Lösung
Vorgehensweise
Erstellen einer Tabelle mit CREATE TABLE in die importiert wird
Zunächst erstellt man eine Tabelle in die importiert werden soll, hier wird eine fiktive Tabelle RAWBEIL erstellt.
CREATE TABLE [dbo].[rawbeil]( [gruppe] [varchar](255) NULL, [bezeichnung] [varchar](255) NULL, [feldname] [varchar](255) NULL, [sapcolumn] [varchar](50) NULL, [saptable] [varchar](50) NULL )
Erstellen einer IMPORT-Datei für die Feldzuweisung
Der RECORD-Tag muss den kompletten Aufbau der CSV-Datei enthalten; der ROW-Teil darf enthält die Tabellenspalten, in die importiert wird. FIELD ID wird auf COLUMN SOURCE gemappt. ROW/COLUMN SOURCE kann auch Lücken enthalten, wenn man Felder aus der CSV Datei weglassen möchte..
<?xml version="1.0" ?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="255"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="255" />
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="255" />
<FIELD ID="4" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="255" />
<FIELD ID="5" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="255" />
<FIELD ID="6" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="255" />
<FIELD ID="7" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="255" />
<FIELD ID="8" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="255" />
<FIELD ID="9" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="255" />
<FIELD ID="10" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="255" />
<FIELD ID="11" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="255" />
<FIELD ID="12" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="255" />
<FIELD ID="13" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="255" />
<FIELD ID="14" xsi:type="CharTerm" TERMINATOR="\r\n"
MAX_LENGTH="255" />
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="gruppe" xsi:type="SQLVARYCHAR" />
<COLUMN SOURCE="2" NAME="bezeichnung" xsi:type="SQLVARYCHAR" />
<COLUMN SOURCE="3" NAME="feldname" xsi:type="SQLVARYCHAR" />
<COLUMN SOURCE="4" NAME="sapcolumn" xsi:type="SQLVARYCHAR" />
<COLUMN SOURCE="5" NAME="saptable" xsi:type="SQLVARYCHAR" />
</ROW>
</BCPFORMAT>
SQL Anweisung
CODEPAGE wird verwendet um das Umlaute-Problem zu beheben, FIRSTROW ist die erste Spalte ab der aus CSV importiert wird (meistens will man den Header ausschließen).
DECLARE @sql nvarchar(4000) SET @sql = 'BULK INSERT rawbeil FROM ''D:\web\karpbjde\import\db.csv'' WITH ( FORMATFILE = ''D:\web\karpbjde\import\beilmade.fmt'', CODEPAGE=''raw'', FIRSTROW = 2 )' EXEC(@sql)
BULK INSERT mit Typecast in einen bestimmten Datentyp
/* Bulk-Import von einer .CSV-Datei in eine Tabelle incl. der Datentypen
* es werden Zahlen im Vormat
* xxx,yy
* xxx.yy
* -xxx,yy
* -xxx.yy
* xxx,yy-
* xxx.yy
* unterstützt. Diese werden als Money-Datentyp convertiert
* Bulk-Import
*
* gewidmet an Björn K.von Nico Gerbig
*/
-- Zuerst als Varchar lesen, dann richtig
create table ordersbulk (col1 varchar(50), col2 varchar(50), col3 varchar(50));
create table ordersbulk2 (col1 varchar(50), col2 money, col3 money)
-- Eintragen in Tabelle mit Varchars
BULK INSERT OrdersBulk
FROM 'c:\test.csv.txt'
WITH
(
--FIRSTROW = 2,
FIELDTERMINATOR = ';',
ROWTERMINATOR = '\n'
)
-- und Kopieren in die Tabelle mit den richtigen Datentypen
insert into ordersbulk2 (col1, col2, col3)
select col1,
dbo.ConvertVarcharToMoney(col2),
dbo.ConvertVarcharToMoney(col3)
from ordersbulk
go
-- testen
select * from ordersbulk2
go
-- dafür notwendige funktion (c) -=e!ninio=-
create function ConvertVarcharToMoney(@abc varchar(255))
returns money
as begin
declare @invert money;
declare @index int;
declare @newabc money;
set @invert = 1;
set @abc = Replace(@abc,',','.')
set @abc = Replace(@abc,' ','')
set @index = CHARINDEX('-',@abc)
if @index > 0
begin
set @invert = -1;
set @abc = Replace(@abc, '-', '')
end
set @newabc = CAST(@abc as money) * @invert
return @newabc
end
go
-- Test für die Funktion selbst
select dbo.ConvertVarcharToMoney('13,41')
select dbo.ConvertVarcharToMoney('13.41')
select dbo.ConvertVarcharToMoney('-13,41')
select dbo.ConvertVarcharToMoney('13,41-')
select dbo.ConvertVarcharToMoney('- 13,41')
select dbo.ConvertVarcharToMoney('13,41 -')
