{"id":52,"date":"2010-07-02T08:47:10","date_gmt":"2010-07-02T06:47:10","guid":{"rendered":"http:\/\/www.capri-soft.de\/blog\/?p=52"},"modified":"2016-02-17T15:06:26","modified_gmt":"2016-02-17T14:06:26","slug":"massenimport-von-daten-uber-ms-sql-server-management-studio","status":"publish","type":"post","link":"https:\/\/www.capri-soft.de\/blog\/?p=52","title":{"rendered":"Massenimport von Daten \u00fcber MS SQL Server Management Studio"},"content":{"rendered":"

Problem<\/h2>\n

Eine gro\u00dfe CSV Datei soll in eine Tabelle importiert werden. Hierbei sollen nicht alle Felder importiert werden.<\/p>\n

Ansatz<\/h2>\n

Die Verwendung des Kommandos BULK INSERT wurde von Microsoft f\u00fcr sehr gro\u00dfe Dateien vorgesehen, um den SQL Server ohne die Verwendung von langsamen Cursor-Iterationen zu f\u00fcllen<\/p>\n

L\u00f6sung<\/h2>\n

Vorgehensweise<\/h3>\n

Erstellen einer Tabelle mit CREATE TABLE in die importiert wird
\nZun\u00e4chst erstellt man eine Tabelle in die importiert werden soll, hier wird eine fiktive Tabelle RAWBEIL erstellt.<\/p>\n

\r\nCREATE TABLE [dbo].[rawbeil](\r\n\t[gruppe] [varchar](255) NULL,\r\n\t[bezeichnung] [varchar](255) NULL,\r\n\t[feldname] [varchar](255) NULL,\r\n\t[sapcolumn] [varchar](50) NULL,\r\n\t[saptable] [varchar](50) NULL\r\n)\r\n<\/pre>\n
Erstellen einer IMPORT-Datei f\u00fcr die Feldzuweisung<\/h3>\n
Der RECORD-Tag muss den kompletten Aufbau der CSV-Datei enthalten; der ROW-Teil darf enth\u00e4lt die Tabellenspalten, in die importiert wird. FIELD ID wird auf COLUMN SOURCE gemappt. ROW\/COLUMN SOURCE kann auch L\u00fccken enthalten, wenn man Felder aus der CSV Datei weglassen m\u00f6chte..<\/p>\n
\r\n&lt;?xml version="1.0" ?>\r\n&lt;BCPFORMAT xmlns="http:\/\/schemas.microsoft.com\/sqlserver\/2004\/bulkload\/format" \r\n                   xmlns:xsi="http:\/\/www.w3.org\/2001\/XMLSchema-instance">\r\n\t&lt;RECORD>\r\n\t\t&lt;FIELD ID="1" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="255"\/>\r\n\t\t&lt;FIELD ID="2" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="255" \/>\r\n\t\t&lt;FIELD ID="3" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="255" \/>\r\n\t\t&lt;FIELD ID="4" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="255" \/>\r\n\t\t&lt;FIELD ID="5" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="255" \/>\r\n\t\t&lt;FIELD ID="6" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="255" \/>\r\n\t\t&lt;FIELD ID="7" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="255" \/>\r\n\t\t&lt;FIELD ID="8" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="255" \/>\r\n\t\t&lt;FIELD ID="9" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="255" \/>\r\n\t\t&lt;FIELD ID="10" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="255" \/>\r\n\t\t&lt;FIELD ID="11" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="255" \/>\r\n\t\t&lt;FIELD ID="12" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="255" \/>\r\n\t\t&lt;FIELD ID="13" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="255" \/>\r\n\t\t&lt;FIELD ID="14" xsi:type="CharTerm" TERMINATOR="\\r\\n" \r\n                  MAX_LENGTH="255" \/>\r\n\t&lt;\/RECORD>\r\n\t&lt;ROW>\r\n\t\t&lt;COLUMN SOURCE="1" NAME="gruppe" xsi:type="SQLVARYCHAR" \/>\r\n\t\t&lt;COLUMN SOURCE="2" NAME="bezeichnung" xsi:type="SQLVARYCHAR" \/>\r\n\t\t&lt;COLUMN SOURCE="3" NAME="feldname" xsi:type="SQLVARYCHAR" \/>\r\n\t\t&lt;COLUMN SOURCE="4" NAME="sapcolumn" xsi:type="SQLVARYCHAR" \/>\r\n\t\t&lt;COLUMN SOURCE="5" NAME="saptable" xsi:type="SQLVARYCHAR" \/>\r\n\t&lt;\/ROW>\r\n&lt;\/BCPFORMAT>\r\n<\/pre>\n
SQL Anweisung<\/h3>\n
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\u00dfen).<\/p>\n
\r\nDECLARE @sql nvarchar(4000)\r\nSET @sql =\r\n'BULK INSERT rawbeil FROM ''D:\\web\\karpbjde\\import\\db.csv''\r\nWITH (\r\nFORMATFILE = ''D:\\web\\karpbjde\\import\\beilmade.fmt'',\r\nCODEPAGE=''raw'',\r\nFIRSTROW = 2\r\n)'\r\nEXEC(@sql)\r\n<\/pre>\n
BULK INSERT mit Typecast in einen bestimmten Datentyp<\/h2>\n
\r\n\/* Bulk-Import von einer .CSV-Datei in eine Tabelle incl. der Datentypen\r\n* es werden Zahlen im Vormat\r\n* xxx,yy\r\n* xxx.yy\r\n* -xxx,yy\r\n* -xxx.yy\r\n* xxx,yy-\r\n* xxx.yy\r\n* unterst\u00fctzt. Diese werden als Money-Datentyp convertiert\r\n* Bulk-Import\r\n*\r\n* gewidmet an Bj\u00f6rn K.von Nico Gerbig\r\n*\/ \r\n\r\n-- Zuerst als Varchar lesen, dann richtig\r\ncreate table ordersbulk (col1 varchar(50), col2 varchar(50), col3 varchar(50));\r\ncreate table ordersbulk2 (col1 varchar(50), col2 money, col3 money) \r\n\r\n-- Eintragen in Tabelle mit Varchars\r\nBULK INSERT OrdersBulk\r\n    FROM 'c:\\test.csv.txt'\r\n    WITH\r\n    (\r\n        --FIRSTROW = 2,\r\n        FIELDTERMINATOR = ';',\r\n        ROWTERMINATOR = '\\n'\r\n    ) \r\n\r\n-- und Kopieren in die Tabelle mit den richtigen Datentypen\r\ninsert into ordersbulk2 (col1, col2, col3)\r\n    select col1,\r\n          dbo.ConvertVarcharToMoney(col2),\r\n          dbo.ConvertVarcharToMoney(col3)\r\n    from ordersbulk\r\ngo\r\n-- testen\r\nselect * from ordersbulk2\r\ngo \r\n\r\n-- daf\u00fcr notwendige funktion (c)  -=e!ninio=-\r\ncreate function ConvertVarcharToMoney(@abc varchar(255))\r\n    returns money\r\nas begin\r\n    declare @invert money;\r\n    declare @index int;\r\n    declare @newabc money;\r\n    set @invert = 1;\r\n    set @abc = Replace(@abc,',','.')\r\n    set @abc = Replace(@abc,' ','')\r\n    set @index = CHARINDEX('-',@abc)\r\n    if @index &gt; 0\r\n    begin\r\n        set @invert = -1;\r\n        set @abc = Replace(@abc, '-', '')\r\n    end\r\n    set @newabc = CAST(@abc as money) * @invert\r\n    return @newabc\r\nend\r\ngo \r\n\r\n-- Test f\u00fcr die Funktion selbst\r\nselect dbo.ConvertVarcharToMoney('13,41')\r\nselect dbo.ConvertVarcharToMoney('13.41')\r\nselect dbo.ConvertVarcharToMoney('-13,41')\r\nselect dbo.ConvertVarcharToMoney('13,41-')\r\nselect dbo.ConvertVarcharToMoney('- 13,41')\r\nselect dbo.ConvertVarcharToMoney('13,41 -')\r\n<\/pre>\n