BULK INSERT Text CSV File into SQL Server Database
Vediamo un esempio di importazione di file di testo in formato csv in una tabella di un Database SQL Server mediante l’istruzione TSQL BULK INSERT.
Supponiamo di avere la seguente tabella StagingOrders nel database Northwind:
CREATE TABLE StagingOrders ( CustomerID INT, CustomerName VARCHAR(32), OrderID INT, OrderDate SMALLDATETIME )
e di voler importare i seguenti dati CSV:
CustomerID;CustomerName;OrderID;OrderDate 1;Carl;9;20091201 2;Jean;11;20091205 3;Niko;21;20091208
Lo statement da utilizzare per importare i dati è il seguente:
BULK INSERT StagingOrders FROM 'C:\StagingOrdersFile.csv' WITH ( FIRSTROW = 2, MAXERRORS = 0, FIELDTERMINATOR = ';', ROWTERMINATOR = '\n' )
dove
FIRSTROW = 2 indica che la prima riga da considerare è la 2 (in base 1) poichè la prima è di header.
MAXERRORS = 0 indica che al 1° errore la procedura deve terminare, altrimenti importa i dati fino a che non si verifica il numero di errori specificato.
FIELDTERMINATOR = ‘;’ indica il carattere separatore di colonna.
ROWTERMINATOR = ‘\n‘ indica il carattere separatore di riga. Spesso viene usato anche ‘\r\n’.
E’ possibile inoltre specificare il mapping delle colonne csv con le colonne della tabella specificando nel blocco WITH la seguente istruzione:
FORMATFILE ='C:\StagingOrderMapping.xml');
Per creare il file di formato per il mapping delle colonne da importare è possibile utilizzare uno strumento bcp da riga di comando
bcp Northwind..StagingOrders format nul -c -t, -x -f C:\StagingOrderMapping.Xml -T
Nello specifico, per il mapping dell’esempio corrente, il file XML di output sarà il seguente:
<?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="12"/> <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="32" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="12"/> <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="24"/> </RECORD> <ROW> <COLUMN SOURCE="1" NAME="CustomerID" xsi:type="SQLINT"/> <COLUMN SOURCE="2" NAME="CustomerName" xsi:type="SQLVARYCHAR"/> <COLUMN SOURCE="3" NAME="OrderID" xsi:type="SQLINT"/> <COLUMN SOURCE="4" NAME="OrderDate" xsi:type="SQLDATETIM4"/> </ROW></BCPFORMAT>
Spesso può ritornarvi utile anche la seguente SELECT che restituisce tutte le occorrenze:
SELECT * FROM OPENROWSET ( BULK 'C:\StagingOrdersFile.csv', FORMATFILE= 'C:\StagingOrderMapping.xml', FIRSTROW = 2, MAXERRORS = 0 ) AS T
Enjoy snippet!
1 commento