Szukam pomocy w zaimportowaniu pliku .csv
do SQL Server przy użyciu BULK INSERT
i mam kilka podstawowych pytań.
Zagadnienia:
Dane w pliku CSV mogą mieć ,
(przecinek) pomiędzy (np.: opis), więc jak mogę wykonać import obsługujący te dane?
Jeśli klient tworzy CSV z Excela to dane, które mają przecinki są zamknięte w ""
(cudzysłów) [jak poniższy przykład] więc jak import może sobie z tym poradzić?
Jak możemy śledzić, czy niektóre wiersze mają złe dane, które import pomija? (czy import pomija wiersze, które nie nadają się do importu)
Oto przykładowy CSV z nagłówkiem:
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.
Oraz instrukcja SQL do importu:
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
)
Import CSV z serwera SQL
- Dane w pliku CSV mogą mieć
,
(przecinek) pomiędzy (np: opis), więc jak mogę zrobić import obsługujący te dane?
Rozwiązanie
Jeśli używasz ,
(przecinka) jako separatora, to nie ma sposobu na rozróżnienie pomiędzy przecinkiem jako terminatorem pola a przecinkiem w danych. Użyłbym innego FIELDTERMINATORA
jak ||
. Kod wyglądałby jak i to będzie obsługiwać przecinki i pojedyncze ukośniki idealnie.
- Jeśli klient tworzy csv z excela to dane, które mają przecinek są zamknięte w
" ... "
(podwójne cudzysłowy) [jak poniżej przykład], więc jak import może sobie z tym poradzić?
Rozwiązanie
Jeśli używasz BULK insert to nie ma sposobu na obsługę podwójnych cudzysłowów, dane będą wstawione z podwójnymi cudzysłowami do wierszy. Po wstawieniu danych do tabeli mógłbyś zastąpić te podwójne cudzysłowy przez '`'.
update table
set columnhavingdoublequotes = replace(columnhavingdoublequotes,'"','')
- Jak możemy śledzić, czy niektóre wiersze mają złe dane, które import pomija? (czy import pomija wiersze, które nie są możliwe do zaimportowania)?
Rozwiązanie
Aby obsłużyć wiersze, które nie są'załadowane do tabeli z powodu nieprawidłowych danych lub formatu, można by obsłużyć używając właściwość ERRORFILE, podając nazwę pliku błędu, spowoduje to zapisanie wierszy z błędem do pliku z błędami. kod powinien wyglądać tak.
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
)
Najlepszym, najszybszym i najprostszym sposobem na rozwiązanie problemu przecinków w danych jest użycie programu Excel do zapisania pliku oddzielonego przecinkami po ustawieniu w systemie Windows' ustawienia separatora listy na coś innego niż przecinek (np. rura). Spowoduje to wygenerowanie pliku rozdzielonego za pomocą rury (lub dowolnego innego), który można następnie zaimportować. Jest to opisane tutaj.
Najpierw należy zaimportować plik CSV do tabeli danych.
Następnie możesz wstawić większą ilość wierszy używając 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);
}
}
}
}
}