Import af CSV-fil til SQL Server

Jeg søger hjælp til at importere en .csv-fil til SQL Server ved hjælp af BULK INSERT, og jeg har nogle få grundlæggende spørgsmål.

Spørgsmål:

  1. CSV-filens data kan have , (komma) imellem (Ex: beskrivelse), så hvordan kan jeg importere disse data?

  2. Hvis kunden opretter CSV-filen fra Excel, så er de data, der har kommaer, indesluttet i "" (dobbelte anførselstegn) [som i nedenstående eksempel], så hvordan kan importen håndtere dette?

  3. Hvordan kan vi spore, om nogle rækker har dårlige data, som importen springer over? (importerer import rækker, der ikke kan importeres)

Her er CSV-eksemplet med overskrift:

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-anvisning til import:

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
Redigeret spørgsmål 2017ør september 2017 в 10:49
Programmering
sql csv sql-server bulk bulkinsert
2013ør marts 2013 в 8:53
16 visninger
Løsning

Baseret SQL Server CSV-import

  1. CSV-filens data kan have , (komma) imellem (f.eks: beskrivelse), så hvordan kan jeg lave import håndtering af disse data?

Løsning

Hvis du bruger , (komma) som afgrænser, så er der ingen måde at skelne mellem et komma som feltterminator og et komma i dine data. Jeg ville bruge en anden FIELDTERMINATOR som f.eks. ||. Koden ville se sådan ud, og dette vil håndtere komma og enkelt skråstreg perfekt.

  1. Hvis klienten oprette csv fra excel så de data, der har komma er indesluttet i " ... " (dobbelte anførselstegn) [som nedenstående eksempel] så hvordan kan importen kan håndtere dette?

Løsning

Hvis du bruger BULK insert, er der ingen måde at håndtere dobbelte anførselstegn på, data vil blive indsættes med dobbelte anførselstegn i rækker. Efter at have indsat dataene i tabellen kan du erstatte disse dobbelte anførselstegn med '` `'.

update table
set columnhavingdoublequotes = replace(columnhavingdoublequotes,'"','')
  1. Hvordan sporer vi, om nogle rækker har dårlige data, som import overspringer? (springer import over rækker, der ikke kan importeres)?

Solution

For at håndtere rækker, som ikke indlæses i tabellen på grund af ugyldige data eller format, kunne man håndtere ved hjælp af ERRORFILE-egenskab, angiv fejlfilens navn, det vil skrive rækkerne med fejl til fejlfilen. koden skal se sådan ud.

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
Redigeret svar 2017ør september 2017 в 3:48
Kommentarer (5)

Den bedste, hurtigste og nemmeste måde at løse problemet med kommaer i data på er at bruge Excel til at gemme en kommasepareret fil efter at have indstillet Windows' listeadskillelsesindstillingen til noget andet end et komma (f.eks. et rør). Dette vil derefter generere en pipe (eller hvad som helst) separeret fil til dig, som du derefter kan importere. Dette er beskrevet her.

Kommentarer (0)

Først skal du importere CSV-filen til datatabellen

Derefter kan du indsætte bulk rækker ved hjælp af 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
Redigeret svar 2013ør december 2013 в 4:40
Kommentarer (1)