Ik zoek hulp bij het importeren van een .csv
bestand in SQL Server met behulp van BULK INSERT
en ik heb een paar basis vragen.
Issues:
De CSV-bestand gegevens kunnen ,
(komma) tussen (Ex: beschrijving), dus hoe kan ik importeren omgaan met deze gegevens?
Als de klant maakt de CSV van Excel dan de gegevens die komma's hebben zijn ingesloten binnen ""
(dubbele aanhalingstekens) [zoals het onderstaande voorbeeld], dus hoe kan de import kan dit verwerken?
Hoe kunnen we bijhouden of sommige rijen hebben slechte gegevens, die de invoer overgeslagen? (slaat import rijen over die niet te importeren zijn)
Hier is het voorbeeld CSV met header:
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.
En SQL-instructie om te importeren:
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
)
Gebaseerd SQL Server CSV import
- De CSV-bestand gegevens kunnen hebben
,
(komma) tussen (Ex: description), dus hoe kan ik importeren omgaan met deze gegevens?
Oplossing
Als u gebruik maakt van ,
(komma) als scheidingsteken, dan is er geen manier om onderscheid te maken tussen een komma als een veld terminator en een komma in uw gegevens. Ik zou een andere FIELDTERMINATOR
gebruiken, zoals ||
. Code zou er zo uit zien en dit zal komma en enkele schuine streep perfect afhandelen.
- Als de klant de csv uit excel maakt dan worden de gegevens die komma zijn ingesloten binnen
" ... "
(dubbele aanhalingstekens) [zoals de onderstaande voorbeeld] dus hoe de invoer kan omgaan met dit?
Oplossing
Als u gebruik maakt van BULK invoegen dan is er geen manier om dubbele aanhalingstekens te verwerken, gegevens zullen worden ingevoegd met dubbele aanhalingstekens in rijen. Na het invoegen van de gegevens in de tabel zou u die dubbele aanhalingstekens kunnen vervangen door '`'.
update table
set columnhavingdoublequotes = replace(columnhavingdoublequotes,'"','')
- Hoe houden we bij of sommige rijen slechte data hebben, die import overslaat? (slaat import rijen over die niet te importeren zijn)?
Oplossing
Om rijen te behandelen die'niet in tabel worden geladen wegens ongeldige gegevens of formaat, zou kunnen worden afhandelen met ERRORFILE eigenschap, geef de fout bestandsnaam, zal het schrijven van de rijen met fout naar fout bestand. code moet eruit zien als.
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
)
De beste, snelste en gemakkelijkste manier om het probleem van de komma in de gegevens op te lossen is Excel te gebruiken om een door komma's gescheiden bestand op te slaan nadat u Windows' lijstscheidingsteken hebt ingesteld op iets anders dan een komma (zoals een pijp). Dit zal dan een door pijpen (of wat dan ook) gescheiden bestand voor u genereren dat u vervolgens kunt importeren. Dit wordt hier beschreven.
Eerst moet u CSV-bestand importeren in de gegevenstabel
Dan kunt u bulk rijen invoegen met behulp van 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);
}
}
}
}
}