Importer CSV-fil til SQL Server

Jeg leter etter hjelp til å importere en .csv-fil til SQL Server ved hjelp av BULK INSERT, og jeg har noen grunnleggende spørsmål.

Spørsmål:

  1. Dataene i CSV-filen kan ha , (komma) mellom (f.eks. beskrivelse), så hvordan kan jeg få importen til å håndtere disse dataene?

  2. Hvis klienten oppretter CSV-filen fra Excel, er dataene som har komma omsluttet av anførselstegn (som i eksemplet nedenfor), så hvordan kan importen håndtere dette?

  3. Hvordan sporer vi om noen rader har dårlige data, som importen hopper over? (hopper importen over rader som ikke kan importeres).

Her er et eksempel på en CSV-fil med topptekst:

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.

og SQL-setning for å importere:

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
)
Somnath Muluk
Redigerte spørsmål 22. september 2017 в 10:49
Programmering
sql csv sql-server bulk bulkinsert
6. mars 2013 в 8:53
12 visninger
Løsning

Basert på SQL Server CSV-import

  1. CSV-fildataene kan ha , (komma) mellom (f.eks: beskrivelse), så hvordan kan jeg få import til å håndtere disse dataene?

Løsning

Hvis du bruker , (komma) som skilletegn, er det ingen måte å skille mellom et komma som feltavslutning og et komma i dataene dine. Jeg ville bruke en annen FIELDTERMINATOR som ||. Koden vil se ut som og dette vil håndtere komma og enkelt skråstrek perfekt.

  1. Hvis klienten oppretter csv fra excel, er dataene som har komma er innkapslet i `" ... " (doble anførselstegn) [som nedenfor eksempel] så hvordan kan importen håndtere dette?

Løsning

Hvis du' bruker BULK insert, er det ingen måte å håndtere doble anførselstegn, data vil være satt inn med doble anførselstegn i rader. Etter at du har satt inn dataene i tabellen, kan du erstatte de doble anførselstegnene med '` `'.

update table
set columnhavingdoublequotes = replace(columnhavingdoublequotes,'"','')
  1. Hvordan sporer vi om noen rader har dårlige data, som importerer hopper over? (hopper import over rader som ikke kan importeres)?

Løsning

For å håndtere rader som ikke er lastet inn i tabellen på grunn av ugyldige data eller format, kan være håndtere ved hjelp av ERRORFILE property, spesifiser feilfilnavnet, det vil skrive radene. med feil til feilfilen. koden skal se ut som.

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
    )
sawa
Redigert svar 8. september 2017 в 3:48
Kommentarer (5)

Den beste, raskeste og enkleste måten å løse problemet med komma i data er å bruke Excel til å lagre en kommaseparert fil etter å ha satt Windows' listeseparatorinnstilling til noe annet enn et komma (for eksempel et rør). Dette vil da generere en pipe (eller hva som helst) separert fil for deg som du deretter kan importere. Dette er beskrevet her.

Kommentarer (0)

Først må du importere CSV-fil til datatabellen

Deretter kan du sette inn bulkrader ved hjelp av 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);
                }
            }
        }
    }
}
Brad Larson
Redigert svar 11. desember 2013 в 4:40
Kommentarer (1)