Bulk Import CSV-Daten mit bcp Tool / Bulk import CSV data with bcp tool

(alle Versionen/all versions)

Freitag, 30. Juni 2023

Hintergrund / Background

"xp_cmdshell"

Import Struktur / Import structure

Beispieldaten / example file

Ziel-Tabelle zuerst erzeugen / Create target table first

Import Script

Fehlerauswertung wie? / Error evaluation how?

Fazit: Großer Vorteil und doch Nachteile / Concusion: Huge advantage but also disadvantages

Nützliche Links / useful links

Hintergrund / Background

Im vorliegenden Anwendungsfall mussten CSV-Rückgabedaten von dem Adressen-Sanktionslistenprüfprogramm “SanScreen” des Anbieters “BEX-Software” in den Microsoft SQL-Server importiert werden.

Aufgrund der Tatsache, dass die Dateien teilweise extrem groß sein und mehrere Hundertausende Datensätze beinhalten können, musste ein möglichst performanter SQL-Import umgesetzt werden.

Wir haben uns dazu entschlossen, die Daten mittels “bcp“ Importprogram des SQL-Servers durchzuführen.

Voraussetzung dafür ist, dass die Funktion “xp_cmdshell“ im SQL-Server freigegeben ist.

In this use case, CSV return data had to be imported into the Microsoft SQL server from the address-sanctions list check program "SanScreen" from the manufacturer "BEX-Software".

Because some files are extremely large and can contain several hundred thousand data CSV records, an SQL import with the highest possible performance had to be implemented.

We decided to import the data using the SQL server's "bcp" import program.

The prerequisite is that the "xp_cmdshell" function is enabled in the SQL server.

"xp_cmdshell"

EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE

EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE

GO

Import Struktur / Import structure

Die CSV-Importstruktur ist relativ einfach:

Es handelt sich um eine CSV-Datei.
Codierung in Codepage "SSO-8859-1" (=SQL Server Code 28591) oder UTF-8 (=SQL Server Code 65001).
Als Trennzeichen wird das Semikolon verwendet.
Zeilenumbruch ist LineFeed (Hexcode 0x0A).
Text ist in Anführungszeichen eingebettet (was wir erstmal ignorieren und bei uns mit einem Nachschaltprogramm auswerten).
Am besten keine Spaltenüberschriften/keine Feldnamen in erster Zeile!

Anmerkung: In der Praxis verwendet BEX-Software kein korrektes CSV-Format. Wurde für eine Adresse ein Treffer in einer Sanktionsliste ausgelöst, so wird ab der zweiten Spalte die vollständige ursprüngliche Prüfadresse mit übergeben!👎😕 Für unseren Anwendungsfall haben wir daher noch ein Konvertierungsprogramm zwischengeschaltet, welches die Daten "bereinigt":

https://www.officium-inservio.com/ms-net-1/fastfilehandlingexample

The CSV import structure is relatively simple:

It is a CSV file.
Encoding in code page "SSO-8859-1" (=SQL Server code 28591) or UTF-8 (=SQL Server code 65001).
The semicolon is used as a separator.
Newline character is LineFeed (Hexcode 0x0A).
Text is embedded in quotation marks (which we ignore for the time being and evaluate later in our workflow, using an addon program).
Avoid column headers/prefer no field names in first row!

Note: In practice, BEX software does not use the correct CSV format. If a hit in a sanctions list was triggered for an address, the complete original test address is also transferred from the second column onwards! 👎😕For our application, we have therefore interposed a conversion program that "cleans" the data:

https://www.officium-inservio.com/ms-net-1/fastfilehandlingexample

Beispieldaten / example file

"123_62_L";"NO_MATCH"
"123_5_L";"NO_MATCH"
"123_48_L";"NO_MATCH"
"123_8_L";"NO_MATCH"
"123_9_L";"NO_MATCH"
"123_3_L";"NO_MATCH"
"123_6_L";"NO_MATCH"
"123_7_L";"NO_MATCH"
"123_44_L";"NO_MATCH"
"123_1_L";"NO_MATCH"
"123_61_L";"NO_MATCH"
"123_4_L";"NO_MATCH"

Ziel-Tabelle zuerst erzeugen / Create target table first

Für die Verwendung des Importprogramms muss die Ziel-Tabelle bereits existieren und bezüglich Datenfelder muss die Anordnung, Datentyp etc. alles genau zur Importstruktur passen!

To use the import program, the target table must already exist and the arrangement, data type, etc. of the data fields must exactly all match the import structure!

IF NOT EXISTS ( SELECT TOP 1 [Name] FROM dbo.sysobjects 
                WHERE id = OBJECT_ID(N'[dbo].[tSanScreenCSVTest]') 
                AND OBJECTPROPERTY(id, N'IsUserTable') = 1 )
BEGIN

 CREATE TABLE [dbo].[tSanScreenCSVTest] 
 (
  [Reference] [nvarchar](50) NOT NULL
  ,[CheckResult] [nvarchar](50) NOT NULL
  ) 
  ON [PRIMARY] 

END

GO

Import Script

DECLARE @sServer VARCHAR(128)
DECLARE @sDatabase VARCHAR(128)
DECLARE @sDbAndTableName VARCHAR(512)
DECLARE @sFieldTerminator VARCHAR(1)
DECLARE @sImportFileName VARCHAR(512)
DECLARE @sCommand VARCHAR(4000)

-- No brackets in the server name!
SET @sServer = 'W2019SVSAGDEV\MAIN'

-- No brackets in any part of the fully qualified name to the target table!
-- Note that you also must set the database name
SET @sDbAndTableName = 'OLDemoReweAbfDR4741.dbo.tSanScreenCSVTest'

-- It should be clear that the import file path must be accessible from the perspective of the SQL server!
-- UNC paths might work, depending on the security right situation in the network.
SET @sImportFileName = 'c:\Test\Adressen_20230623230007.res'

SET @sFieldTerminator = ';'

-- Notes about "bcp": 

-- Parameter "T" (capital letter!) indicates a trusted connection
-- This implies that the executing login context, e.g. SQL Server agent account, got sufficient access rights to all relevant databases!

-- The 0x0A is the Hex code for line feed!

-- The "C" parameter (capital letter!) indicates the unicode id of the charset. 65001 is UTF-8 (see additional web links)

SET @sCommand = 'bcp ' + @sDbAndTableName + ' in "' + @sImportFileName + '" -S ' + @sServer + ' -T -c -C65001 -t"' + @sFieldTerminator + '" -r "0x0A"'

EXECUTE master.dbo.xp_cmdshell @sCommand

GO

Fehlerauswertung wie? / Error evaluation how?

Es stellt sich die Frage, wie man an eventuelle Fehlermeldungen kommt, falls die Ausführung des externen “bcp“ Tools fehlschlägt. Schließlich handelt es sich um eine Shell-Ausführung, d.h. kein “normales“ Fehlermanagement des MS-SQL-Servers wie bei sonstigen SQL-Aktionen. Das nachfolgende Script zeigt einen möglichen Ansatz für die Ermittlung aller Fehlermeldungen des Tools.

The question arises as to how you can get to any error messages if the execution of the external "bcp" tool fails. After all, it is a shell execution, i.e. no "normal" error management of the MS SQL server as with other SQL actions. The following script shows a possible approach for determining all error messages from the tool.

DECLARE @tblCommandResult TABLE ( [ID] int IDENTITY, [ResultText] VARCHAR(MAX) NULL )

DECLARE @lResultCode INT

INSERT @tblCommandResult EXEC @lResultCode = master.dbo.xp_cmdshell @sCommand
  
IF @lResultCode <> 0 
BEGIN
 
  DECLARE @sMsg VARCHAR(MAX)

  SET @sMsg = ''
    
  -- Select all messages from temp. table
  SELECT @sMsg = @sMsg + ' ' + [ResultText] FROM @tblCommandResult WHERE NOT [ResultText] IS NULL

  SELECT @sMsg
      
END

Fazit: Großer Vorteil und doch Nachteile / Concusion: Huge advantage but also disadvantages

Großer Vorteil:

Der Import ist extrem schnell.

Der Import von mehren Hunderttausend Datensätzen dauert nur wenige Sekunden!

Nachteile:

Es handelt sich um ein Programm, welches in der Shell außerhalb der normalen SQL-Server-Umgebung aufgerufen werden muss (diese Funktion muss vorher freigeschaltet sein).
Gewöhnungsbedürftige Parameter-Syntax.
Fehlermanagement aufwändiger.
Die Importstrukturen müssen exakt abgestimmt sein.
Die CSV-Importdatei muss durchgängig denselben Aufbau haben.
Verwendung von CSV-Steuerzeichen in den Daten selber (z.B. LineFeed in Datenfeldern) führen zu Problemen beim Import.

Huge advantage:

The import is extremely fast!

Importing hundreds of thousands of records takes just a few seconds.

Disadvantages:

The program must be called in the shell outside the normal SQL server environment (this function must be enabled beforehand).
Parameter syntax that takes some getting used to.
Error management more complex.
The import structures must be precisely coordinated.
The CSV import file must have the same structure throughout.
Using CSV control characters in the data itself (e.g. LineFeed in data fields) leads to problems during import.