Importați fișierul CSV în SQL Server (Programare, Sql, Server Sql, Csv, Bulkinsert, Bulk)

Prabhat a intrebat.

Caut ajutor pentru a importa un fișier .csv fișier în SQL Server folosind BULK INSERT și am câteva întrebări de bază.

Probleme:

  1. Datele din fișierul CSV pot avea , (virgulă) între ele (Ex: descriere), deci cum pot face importul manipulând aceste date?

  2. Dacă clientul creează fișierul CSV din Excel, atunci datele care au virgulă sunt incluse în "" (ghilimele duble) [ca în exemplul de mai jos], deci cum se poate face ca importul să gestioneze acest lucru?

  3. Cum putem urmări dacă unele rânduri au date eronate, pe care importul le ignoră? (importă rândurile care nu sunt importabile)

Iată exemplul CSV cu antet:

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.

Și instrucțiunea SQL pentru a importa:

BULK INSERT SchoolsTemp
FROM 'C:CSVDataSchools.csv'
WITH
(
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',  --CSV field delimiter
    ROWTERMINATOR = '
',   --Use to shift the control to next row
    TABLOCK
)

Comentarii

  • Poate că SSMS: How to import (Copy/Paste) data from excel poate fi de ajutor (dacă nu doriți să utilizați BULK NSERT sau nu aveți permisiuni pentru aceasta). –  > Por Denis.
12 răspunsuri
Vishwanath Dalvi

Bazat pe SQL Server CSV Import CSV

1) Datele din fișierul CSV pot avea , (virgulă) între ele (Ex: descriere), deci cum pot face importul manipulând aceste date?

Soluție

Dacă utilizați , (virgulă) ca delimitator, atunci nu există nicio modalitate de a face diferența între o virgulă ca terminator de câmp și o virgulă în datele dumneavoastră. Eu aș folosi un alt FIELDTERMINATOR cum ar fi ||. Codul ar arăta ca și acest lucru va gestiona perfect virgula și slash-ul simplu.

2) Dacă clientul creează csv-ul din excel, atunci datele care au virgulă sunt incluse în " ... " (ghilimele duble) [ca în exemplul de mai jos], deci cum se poate gestiona acest lucru în cadrul importului?

Soluție

Dacă folosiți BULK insert, atunci nu există nicio modalitate de a gestiona ghilimelele duble, datele vor fi inserate cu ghilimele duble în rânduri. după inserarea datelor în tabel, ați putea înlocui acele ghilimele duble cu‘.

update table
set columnhavingdoublequotes = replace(columnhavingdoublequotes,'"','')

3) Cum urmărim dacă unele rânduri au date proaste, pe care importul le sare? (importul sare peste rândurile care nu sunt importabile)?

Soluție

Pentru a gestiona rândurile care nu sunt încărcate în tabel din cauza datelor sau a formatului nevalabil, se poate face acest lucru folosind proprietatea ERRORFILE, specificați numele fișierului de eroare și se vor scrie rândurile cu erori în fișierul de erori. codul ar trebui să arate astfel.

BULK INSERT SchoolsTemp
    FROM 'C:CSVDataSchools.csv'
    WITH
    (
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',  --CSV field delimiter
    ROWTERMINATOR = '
',   --Use to shift the control to next row
    ERRORFILE = 'C:CSVDATASchoolsErrorRows.csv',
    TABLOCK
    )

Comentarii

  • Vă mulțumim pentru ajutor. Reg the Solution#1: Putem crea un fișier cu valori separate || din Excel? Pentru că aproximativ 20% din fișierele sursă sunt create folosind Excel de către client. –  > Por Prabhat.
  • @Prabhat Cum încărcați fișierele Excel în SQL Server? –  > Por Vishwanath Dalvi.
  • Acestea nu sunt fișiere Excel pe care le încarc. Clientul folosește Excel pentru a crea fișiere .CSV (pentru 20% din datele sursă pe care aplicația noastră le importă). Și mă întrebam dacă creăm fișiere csv folosind Excel cum putem avea || ca separator de valori de coloană? –  > Por Prabhat.
  • Dacă aveți influență asupra modului în care clientul creează fișiere CSV din Excel, îl puteți învăța cum să seteze caracterul separator în Excel (și bine, nu mai este un fișier separat prin „virgulă”, ci ar fi separat prin pipe (|), de exemplu. Având în vedere cercurile pe care le faci pentru asta, și dacă ai SSIS – îți recomand să verifici. Versiunile de SQL Server 2012 și ulterioare au un designer SSIS foarte robust (de asemenea, în VS 2012 și ulterior) care ar permite clientului dvs. să vă trimită pur și simplu fișierele excel în loc de csv. –  > Por qxotk.
  • Fișierul trebuie să fie PE SERVER. Nu pe mașina locală. –  > Por Jess.
Zd8n8k

Mai întâi trebuie să creați un tabel în baza de date în care veți importa fișierul CSV. După ce tabelul este creat, urmați pașii de mai jos.

– Conectați-vă la baza de date utilizând SQL Server Management Studio

– Faceți clic dreapta pe baza dvs. de date și selectați Tasks -> Import Data...

– Faceți clic pe Next > butonul

– Pentru Sursa de date, selectați Flat File Source. Apoi, utilizați butonul Browse (Răsfoiește) pentru a selecta fișierul CSV. Petreceți ceva timp pentru a configura modul în care doriți ca datele să fie importate înainte de a face clic pe butonul Next > buton.

– Pentru Destination (Destinație), selectați furnizorul de baze de date corect (de exemplu, pentru SQL Server 2012, puteți utiliza SQL Server Native Client 11.0). Introduceți numele serverului. Verificați opțiunea Use SQL Server Authentication butonul radio. Introduceți numele de utilizator, parola și baza de date înainte de a face clic pe butonul Next > buton.

– În fereastra Select Source Tables and Views (Selectați tabelele și vizualizările sursă), puteți edita Mappings (Modificări de corespondență) înainte de a face clic pe butonul Next > buton.

– Verificați opțiunea Run immediately caseta de selectare și faceți clic pe butonul Next > buton.

– Faceți clic pe butonul Finish pentru a rula pachetul.

Datele de mai sus au fost găsite pe acest site site-ul (l-am folosit și l-am testat):

Comentarii

    36

  • Ar fi frumos dacă ați da atribuire la pagina de unde ați copiat/lipit acest răspuns… –  > Por SierraOscar.
  • Nu este necesară pre-crearea tabelului, acesta poate fi creat în timpul procesului de import –  > Por bside.
  • Îmi place că tocmai ai tăiat & paste de pe o pagină web cu linia atât de utilă „Petreceți ceva timp pentru a configura modul în care doriți ca datele să fie importate”. Asta a fost totul Am căutat: Se pare că nu reușesc să o configurez deloc! –  > Por Auspex.
  • Oh, și „Bifați butonul radio Utilizare autentificare SQL Server” este greșit, deoarece este foarte posibil să doriți să utilizați autentificarea Windows. Este vorba de ceea ce funcționează pentru dumneavoastră. –  > Por Auspex.
  • mulțumiri a găsit o procedură pas cu pas cu imagini pentru a pune în aplicare procedura de mai sus, merită o privire: qawithexperts.com/article/sql/… –  > Por Vikas Lalwani.
Oleg

2) Dacă clientul creează csv-ul din excel, atunci datele care au virgulă sunt incluse în ” … ” (ghilimele duble) [ca în exemplul de mai jos], deci cum se poate gestiona acest lucru în cadrul importului?

Ar trebui să folosiți opțiunile FORMAT = ‘CSV’, FIELDQUOTE = ‘””:

BULK INSERT SchoolsTemp
FROM 'C:CSVDataSchools.csv'
WITH
(
    FORMAT = 'CSV', 
    FIELDQUOTE = '"',
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',  --CSV field delimiter
    ROWTERMINATOR = '
',   --Use to shift the control to next row
    TABLOCK
)

Comentarii

  • Rețineți că specificatorul FORMAT este disponibil numai începând cu SQL Server 2017. –  > Por kristianp.
Sachin Kainth

Cea mai bună, cea mai rapidă și cea mai simplă modalitate de a rezolva problema virgulei în date este să utilizați Excel pentru a salva un fișier separat prin virgulă după ce ați setat setarea separatorului de listă din Windows la altceva decât o virgulă (cum ar fi o țeavă). Acest lucru va genera apoi un fișier separat de tip pipe (sau orice altceva) pentru dvs. pe care îl puteți importa apoi. Acest lucru este descris aici.

kombsh

În primul rând, trebuie să importați fișierul CSV în tabelul de date

Apoi, puteți introduce rânduri în vrac utilizând 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);
                }
            }
        }
    }
}

Comentarii

Zee

Iată cum aș rezolva eu problema:

  1. Doar salvați fișierul CSV ca o foaie XLS în excel(Făcând acest lucru, nu ar trebui să vă faceți griji cu privire la delimitări. Formatul foii de calcul din Excel va fi citit ca un tabel și va fi importat direct într-un tabel SQL)

  2. Importați fișierul utilizând SSIS

  3. Scrieți un script personalizat în managerul de import pentru a omite/modifica datele pe care le căutați (sau rulați un script principal pentru a examina cu atenție datele pe care doriți să le eliminați)

Mult noroc.

Comentarii

  • Downvote: Importul de fișiere XLS cu SSIS este teribil. SSIS va încerca să ghicească tipurile de date ale datelor din Excel, dar poate ghici greșit și nu puteți face nimic în acest sens. Este mult mai bine să folosiți CSV. –  > Por NReilingh.
  • Ei bine, și eu aș sugera csv, dar dacă ați fi citit scenariul lui OP, acesta avea câteva scenarii speciale, în special cu delimitatorii, care nu sunt o problemă cu foile xls. De obicei, scenariile speciale de genul acesta nu necesită o soluție extinsă, ci o remediere care să păstreze datele. În timpul încărcării fișierului, SSIS vă permite să alegeți maparea datelor între tabelele sursă și destinație, ceea ce, din nou, ușurează efortul implicat. Acesta este motivul pentru care această metodă a fost sugerată ca un hack rapid. –  > Por Zee.
  • SSIS poate gestiona deja delimitatorii de text CSV. Dacă oricum folosiți SSIS, dacă vă chinuiți să salvați mai întâi CSV-ul ca un XLS, mi se pare că adăugați o potențială defecțiune fără niciun motiv. –  > Por NReilingh.
  • De asemenea, am în mod obișnuit fișiere CSV prea mari pentru Excel. –  > Por Auspex.
jarvis24

Deoarece nu folosesc expertul de import SQL, pașii ar fi următorii:

  1. Faceți clic dreapta pe baza de date în opțiunea sarcini pentru a importa date,

  2. Odată ce asistentul este deschis, selectăm tipul de date care urmează să fie implicate. În acest caz, ar fi

Sursa fișierului plat

Selectăm fișierul CSV, puteți configura tipul de date al tabelelor din CSV, dar cel mai bine este să le aduceți din CSV.

  1. Facem clic pe Next și selectăm în ultima opțiune care este

SQL client

În funcție de tipul nostru de autentificare îl selectăm, odată făcut acest lucru, vine o opțiune foarte importantă.

  1. Putem defini id-ul tabelului din CSV (se recomandă ca coloanele din CSV să se numească la fel ca și câmpurile din tabel). În opțiunea Edit Mappings putem vedea previzualizarea fiecărui tabel cu coloana din foaia de calcul, dacă dorim ca asistentul să insereze id-ul în mod implicit lăsăm opțiunea nebifată.

Enable id insert (Activează inserarea id-ului)

(de obicei nu începând de la 1), în schimb dacă avem o coloană cu id-ul în CSV selectăm Enable id insert, următorul pas este să încheiem expertul, putem revizui modificările aici.

Pe de altă parte, în următoarea fereastră pot apărea alerte, sau avertismente, ideal este să le ignorăm, doar dacă acestea lasă erori este necesar să acordăm atenție.

Acest link are imagini.

Arsen Khachaturyan

Știu că aceasta nu este soluția exactă la întrebarea de mai sus, dar pentru mine a fost un coșmar atunci când am încercat să Copiați datele dintr-o bază de date aflată pe un server separat pe serverul meu local.

Am încercat să fac asta prin prima dată export datele de pe server pe CSV/txt și apoi import în tabelul meu local.

Ambele soluții: cu scrierea interogării pentru a importa CSV sau utilizând SSMS Import date wizard a produs întotdeauna erori (erorile erau foarte generale, spunând că există o problemă de analiză). Și, deși nu făceam nimic special, doar exportați la CSV și apoi încercam să importa CSV în local DB, , erorile au fost întotdeauna prezente.

Am încercat să mă uit la secțiunea de cartografiere și la previzualizarea datelor, dar era întotdeauna o mare încurcătură. Și știu că principala problemă venea de la una dintre table coloane, care conținea JSON și SQL parserul o trata greșit.

Așa că, în cele din urmă, am găsit o soluție diferită și vreau să o împărtășesc în cazul în care altcineva va avea o problemă similară.


Ceea ce am făcut este că am folosit Exporting Wizard (Expertul de export) pe serverul extern.

Iată care sunt pașii pentru a repeta același proces:
1) Faceți clic dreapta pe baza de date și selectați Tasks -> Export Data...

2) Când se va deschide Wizard, selectați Next (Următorul), iar în locul „Data Source:” (Sursa de date:) alegeți „SQL Server Native Client”.

În cazul serverului extern, cel mai probabil va trebui să alegeți „Use SQL Server Authentication” la „Authentication Mode:”.

3) După ce ați apăsat Next (Următorul), , trebuie să selectați Destionare.
Pentru aceasta, selectați din nou „SQL Server Native Client”.
De data aceasta, puteți să furnizați datele locale (sau alte date externe). DB) DB.

4) După ce ați apăsat butonul Next, aveți două opțiuni, fie să copiați întreaga tabelă de la un DB la alta, fie să scrieți interogarea pentru a specifica datele exacte care trebuie copiate. În cazul meu, nu aveam nevoie de întregul tabel (era prea mare), ci doar de o parte din el, așa că am ales „Scrieți o interogare pentru a specifica datele de transferat”.

V-aș sugera să scrieți și să testați interogarea pe un editor de interogări separat înainte de a trece la Wizard.

5) Și, în cele din urmă, trebuie să specificați tabelul de destinație în care vor fi selectate datele.

Vă sugerez să o lăsați ca [dbo].[Query] sau un alt nume personalizat Table nume personalizat în cazul în care veți avea erori la exportul de date sau dacă nu sunteți sigur de date și doriți să le analizați în continuare înainte de a le muta în tabelul exact pe care îl doriți.

Și acum mergeți direct la sfârșitul Expertului apăsând pe Next/Finish butoane.

Steve Yo

Importați fișierul în Excel deschizând mai întâi Excel, apoi mergeți la DATA, import from TXT File, alegeți extensia csv care va păstra valorile cu prefixul 0 și salvați coloana respectivă ca TEXT, deoarece altfel Excel va renunța la 0 de început (NU faceți dublu clic pentru a deschide cu Excel dacă aveți date numerice într-un câmp care începe cu 0 [zero]). Apoi, salvați ca fișier text delimitat de tabulație. Când importați în Excel, aveți opțiunea de a salva ca GENERAL, TEXT etc. Alegeți TEXT pentru ca ghilimelele din mijlocul unui șir de caractere într-un câmp precum YourCompany,LLC să fie păstrate…

BULK INSERT dbo.YourTableName
FROM 'C:UsersSteveDownloadsyourfiletoIMPORT.txt'
WITH (
FirstRow = 2, (if skipping a header row)
FIELDTERMINATOR = 't',
ROWTERMINATOR   = '
'
)

Aș vrea să pot folosi funcționalitatea FORMAT și Fieldquote, dar se pare că aceasta nu este acceptată în versiunea mea de SSMS.

Chameleon

Știu că există un răspuns acceptat, dar, totuși, vreau să împărtășesc scenariul meu care poate ajuta pe cineva să își rezolve problema.INSTRUMENTE

  • ASP.NET
  • EF CODE-FIRST APPROACH
  • SSMS
  • EXCEL

SCENARIUÎncărcam setul de date în format CSV, care urma să fie afișat ulterior în vizualizare, am încercat să folosesc încărcarea în masă, dar nu am reușit să îl încarc deoarece BULK LOAD folosea

FIELDTERMINATOR = ','

iar celula Excel folosea de asemenea , cu toate acestea, nu am putut utiliza nici Flat file source direct, deoarece foloseam Code-First Approach și făcând acest lucru am făcut doar modelul în baza de date SSMS, nu în modelul din care trebuia să folosesc proprietățile mai târziu.

SOLUȚIE

  1. Am folosit sursa flat-file și am făcut tabelul DB din fișierul CSV (click dreapta pe DB în SSMS -> Import Flat FIle -> selectați calea CSV și faceți toate setările conform indicațiilor)
  2. Am creat o clasă model în Visual Studio (TREBUIE să păstrați toate tipurile de date și numele identice cu cele din fișierul CSV încărcat în sql)
  3. utilizați Add-Migration în consola pachetului NuGet
  4. Update DB

Denis

Poate că SSMS: How to import (Copy/Paste) data from excel vă poate ajuta (Dacă nu doriți să utilizați BULK INSERT sau nu aveți permisiuni pentru aceasta).

William Herrmann

Toate răspunsurile de aici funcționează foarte bine dacă datele dvs. sunt „curate” (fără încălcări ale constrângerilor de date etc.) și aveți acces la punerea fișierului pe server. Unele dintre răspunsurile furnizate aici se opresc la prima eroare (încălcarea PK, eroare de pierdere de date etc.) și vă oferă câte o eroare pe rând dacă folosiți sarcina de import încorporată în SSMS. Dacă doriți să adunați toate erorile deodată (în cazul în care doriți să îi spuneți persoanei care v-a dat fișierul .csv să își curețe datele), vă recomand următorul răspuns. Acest răspuns vă oferă, de asemenea, o flexibilitate completă, deoarece „scrieți” singur SQL-ul.

Notă: Voi presupune că folosiți un sistem de operare Windows și că aveți acces la Excel și SSMS. În caz contrar, sunt sigur că puteți modifica acest răspuns pentru a se potrivi nevoilor dumneavoastră.

  1. Folosind Excel, deschideți fișierul .csv. Într-o coloană goală, veți scrie o formulă care va construi o formulă individuală INSERTdeclarații de genul =CONCATENATE("INSERT INTO dbo.MyTable (FirstName, LastName) VALUES ('", A1, "', '", B1,"')", CHAR(10), "GO") unde A1 este o celulă care are datele despre prenume și A2 are datele despre numele de familie, de exemplu.

    • CHAR(10) adaugă un caracter newline la rezultatul final și GO ne va permite să rulăm acest INSERT și să trecem la următoarea chiar dacă există erori.
  2. Evidențiați celula cu =CONCATENATION() formula

  3. Shift + End pentru a evidenția aceeași coloană în restul rândurilor dvs.

  4. În panglica > Home > Editing > Fill > Click jos

    • Acest lucru aplică formula până jos în toată foaia, astfel încât nu trebuie să copiați-lipiți, să trageți etc. în jos potențial mii de rânduri de mână
  5. Ctrl + C pentru a copia SQL-ul formulat INSERT formulate

  6. Lipiți în SSMS

  7. Veți observa că Excel, probabil în mod neașteptat, a adăugat ghilimele duble în jurul fiecăreia dintre INSERT și GO comenzi. Acesta este un „caracteristică” (?) de copiere a valorilor de mai multe rânduri din Excel. Puteți pur și simplu să găsiți și să înlocuiți "INSERT și GO" cu INSERT și GO respectiv pentru a curăța acest lucru.

  8. În cele din urmă, sunteți gata să executați procesul de import

  9. După finalizarea procesului, verificați dacă există erori în fereastra Messages (Mesaje). Puteți selecta tot conținutul (Ctrl + A) și îl puteți copia în Excel și puteți utiliza un filtru de coloană pentru a elimina mesajele reușite și veți rămâne cu toate și toate erorile.

Acest proces va dura cu siguranță mai mult decât alte răspunsuri de aici, dar dacă datele dvs. sunt „murdare” și pline de încălcări SQL, puteți cel puțin să adunați toate erorile deodată și să le trimiteți persoanei care v-a dat datele, dacă acesta este scenariul dvs.