Sto cercando aiuto per importare un file .csv
in SQL Server usando BULK INSERT
e ho alcune domande di base.
Problemi:
I dati del file CSV possono avere ,
(virgola) in mezzo (Es: descrizione), quindi come posso fare l'importazione gestendo questi dati?
Se il cliente crea il CSV da Excel, allora i dati che hanno la virgola sono racchiusi in ""
(doppie virgolette) [come l'esempio qui sotto] quindi come faccio l'importazione può gestire questo?
Come facciamo a rintracciare se alcune righe hanno dati sbagliati, che l'importazione salta? (l'importazione salta le righe che non sono importabili)
Ecco il CSV di esempio con l'intestazione:
Name,Class,Subject,ExamDate,Mark,Description
Prabhat,4,Math,2/10/2013,25,Test data for prabhat.
Murari,5,Science,2/11/2013,24,"Test data for his's test, where we can test 2nd ROW, Test."
sanjay,4,Science,,25,Test Only.
E l'istruzione SQL per importare:
BULK INSERT SchoolsTemp
FROM 'C:\CSVData\Schools.csv'
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR = ',', --CSV field delimiter
ROWTERMINATOR = '\n', --Use to shift the control to next row
TABLOCK
)
Importazione CSV basata su SQL Server
,
(virgola) in mezzo (es:descrizione), quindi come posso fare l'importazione gestendo questi dati?
Soluzione
Se stai usando ,
(virgola) come delimitatore, allora non c'è modo di distinguere tra una virgola come terminatore di campo e una virgola nei tuoi dati. Io userei un diverso FIELDTERMINATOR
come ||
. Il codice sarebbe simile e questo gestirà perfettamente la virgola e la barra singola.
virgola sono racchiusi in
" ... "
(doppi apici) [come il seguente esempio] quindi come fa l'importazione a gestire questo?
Soluzione
Se stai usando BULK insert allora non c'è modo di gestire i doppi apici, i dati saranno inseriti con le doppie virgolette nelle righe. Dopo aver inserito i dati nella tabella si potrebbero sostituire quei doppi apici con '` `'.
update table
set columnhavingdoublequotes = replace(columnhavingdoublequotes,'"','')
- Come tracciamo se alcune righe hanno dati cattivi, che l'importazione salta? (l'importazione salta le righe che non sono importabili)?
Soluzione
Per gestire le righe che non vengono caricate nella tabella a causa di dati o formato non validi, potrebbe essere gestire usando proprietà ERRORFILE, specificare il nome del file di errore, scriverà le righe che hanno errore al file di errore. il codice dovrebbe assomigliare a.
BULK INSERT SchoolsTemp
FROM 'C:\CSVData\Schools.csv'
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR = ',', --CSV field delimiter
ROWTERMINATOR = '\n', --Use to shift the control to next row
ERRORFILE = 'C:\CSVDATA\SchoolsErrorRows.csv',
TABLOCK
)
Il modo migliore, più veloce e più semplice per risolvere il problema della virgola nei dati è quello di utilizzare Excel per salvare un file separato da virgola dopo aver impostato Windows su qualcosa di diverso da una virgola (come un tubo). Questo genererà quindi un file separato da virgola (o qualsiasi altra cosa) per voi che potrete poi importare. Questo è descritto qui.
Prima è necessario importare il file CSV nella tabella dei dati
Poi è possibile inserire righe di massa utilizzando SQLBulkCopy
using System;
using System.Data;
using System.Data.SqlClient;
namespace SqlBulkInsertExample
{
class Program
{
static void Main(string[] args)
{
DataTable prodSalesData = new DataTable("ProductSalesData");
// Create Column 1: SaleDate
DataColumn dateColumn = new DataColumn();
dateColumn.DataType = Type.GetType("System.DateTime");
dateColumn.ColumnName = "SaleDate";
// Create Column 2: ProductName
DataColumn productNameColumn = new DataColumn();
productNameColumn.ColumnName = "ProductName";
// Create Column 3: TotalSales
DataColumn totalSalesColumn = new DataColumn();
totalSalesColumn.DataType = Type.GetType("System.Int32");
totalSalesColumn.ColumnName = "TotalSales";
// Add the columns to the ProductSalesData DataTable
prodSalesData.Columns.Add(dateColumn);
prodSalesData.Columns.Add(productNameColumn);
prodSalesData.Columns.Add(totalSalesColumn);
// Let's populate the datatable with our stats.
// You can add as many rows as you want here!
// Create a new row
DataRow dailyProductSalesRow = prodSalesData.NewRow();
dailyProductSalesRow["SaleDate"] = DateTime.Now.Date;
dailyProductSalesRow["ProductName"] = "Nike";
dailyProductSalesRow["TotalSales"] = 10;
// Add the row to the ProductSalesData DataTable
prodSalesData.Rows.Add(dailyProductSalesRow);
// Copy the DataTable to SQL Server using SqlBulkCopy
using (SqlConnection dbConnection = new SqlConnection("Data Source=ProductHost;Initial Catalog=dbProduct;Integrated Security=SSPI;Connection Timeout=60;Min Pool Size=2;Max Pool Size=20;"))
{
dbConnection.Open();
using (SqlBulkCopy s = new SqlBulkCopy(dbConnection))
{
s.DestinationTableName = prodSalesData.TableName;
foreach (var column in prodSalesData.Columns)
s.ColumnMappings.Add(column.ToString(), column.ToString());
s.WriteToServer(prodSalesData);
}
}
}
}
}