Zaimportuj plik CSV do programu SQL Server


185

Szukam pomocy w zaimportowaniu .csvpliku do programu SQL Server BULK INSERTi mam kilka podstawowych pytań.

Zagadnienia:

  1. Dane pliku CSV mogą zawierać ,(przecinek) pomiędzy (np. Opis), więc jak mogę dokonać importu obsługującego te dane?

  2. Jeśli klient utworzy plik CSV z Excela, dane zawierające przecinek są ujęte w ""(podwójny cudzysłów) [jak w poniższym przykładzie], więc jak import może sobie z tym poradzić?

  3. Jak śledzić, czy niektóre wiersze zawierają złe dane, które importują pomijają? (czy import pomija wiersze, których nie można importować)

Oto przykładowy plik 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.

I instrukcja SQL do zaimportowania:

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
)

Może to być SSMS: pomoc w importowaniu (kopiowaniu / wklejaniu) danych z programu Excel (jeśli nie chcesz ich używać BULK NSERTlub nie masz do nich uprawnień).
Denis

Odpowiedzi:


169

Oparty na SQL Server import CSV

1) Dane pliku CSV mogą zawierać ,(przecinek) pomiędzy (Np .: opis), więc jak mogę dokonać importu obsługującego te dane?

Rozwiązanie

Jeśli używasz ,(przecinka) jako separatora, nie ma sposobu, aby odróżnić przecinek jako terminator pola od przecinka w danych. Chciałbym użyć innego FIELDTERMINATORpodobnego ||. Kod wyglądałby tak, a idealnie poradzi sobie z przecinkiem i pojedynczym ukośnikiem.

2) Jeśli klient utworzy plik CSV z programu Excel, dane zawierające przecinek są ujęte w " ... "(podwójne cudzysłowy) [jak w poniższym przykładzie], więc jak import może sobie z tym poradzić?

Rozwiązanie

Jeśli używasz wstawiania BULK, nie ma sposobu na obsługę podwójnych cudzysłowów, dane zostaną wstawione z podwójnymi cudzysłowami do wierszy. po wstawieniu danych do tabeli można zamienić te podwójne cudzysłowy na „ ”.

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

3) Jak śledzić, czy niektóre wiersze zawierają złe dane, które importują pomijają? (czy import pomija wiersze, których nie można importować)?

Rozwiązanie

Aby obsłużyć wiersze, które nie zostały załadowane do tabeli z powodu niepoprawnych danych lub formatu, można je obsłużyć za pomocą właściwości ERRORFILE , podaj nazwę pliku błędu, zapisuje wiersze z błędem do pliku błędu. kod powinien wyglądać.

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
    )

1
Dzięki za pomoc. Reg rozwiązanie nr 1: Czy możemy stworzyć || plik wartości oddzielony od Excela? Ponieważ około 20% plików źródłowych jest tworzonych przy użyciu programu Excel przez klienta.
Prabhat,

@Prabhat Jak ładujesz pliki Excela do SQL Server?
Vishwanath Dalvi

To nie są pliki programu Excel, które ładuję. Klient używa programu Excel do tworzenia plików .CSV (dla 20% danych źródłowych importowanych przez naszą aplikację). I pytałem, czy tworzymy pliki csv za pomocą Excela, jak możemy mieć || jako separator wartości kolumn?
Prabhat,

Jeśli masz wpływ na sposób, w jaki klient tworzy pliki CSV z Excela, możesz nauczyć ich, jak ustawiać znak separatora w Excelu (i cóż, nie jest to już plik oddzielony przecinkami, to byłby oddzielony potokiem (|), na przykład. Biorąc pod uwagę obręcze, przez które przeskakujesz w tym celu, a jeśli masz SSIS - zalecam sprawdzenie. Wersje SQL Server 2012 i późniejsze mają bardzo solidny projektant SSIS (również w VS 2012 i późniejszych), który by
umożliw

Nie jestem pewien, czy jest to całkowicie dokładne. Podwójne cudzysłowy można obsługiwać w SQL Bulk Insert. W tym temacie występuje Przepełnienie stosu i można użyć plików formatu, aby nauczyć różne wstawianie znaczników luzem. stackoverflow.com/questions/25726385/... advancesharp.com/blog/1083/...
DtechNet

33

Najpierw musisz utworzyć tabelę w bazie danych, w której będziesz importować plik CSV. Po utworzeniu tabeli wykonaj poniższe czynności.

• Zaloguj się do bazy danych za pomocą SQL Server Management Studio

• Kliknij bazę danych prawym przyciskiem myszy i wybierz Tasks -> Import Data...

• Kliknij Next >przycisk

• W polu Źródło danych wybierz Flat File Source. Następnie użyj przycisku Przeglądaj, aby wybrać plik CSV. Poświęć trochę czasu na skonfigurowanie sposobu importowania danych, zanim klikniesz Next >przycisk.

• W polu Miejsce docelowe wybierz poprawnego dostawcę bazy danych (np. Dla SQL Server 2012 można użyć SQL Server Native Client 11.0). Wpisz nazwę serwera. Sprawdź Use SQL Server Authenticationprzycisk opcji. Wprowadź nazwę użytkownika, hasło i bazę danych przed kliknięciem Next >przycisku.

• W oknie Wybierz tabele źródłowe i widoki możesz edytować mapowania przed kliknięciem Next >przycisku.

• Zaznacz Run immediatelypole wyboru i kliknij Next >przycisk.

• Kliknij Finishprzycisk, aby uruchomić pakiet.

Powyższe znaleziono na tej stronie (użyłem go i przetestowałem):


30
Byłoby miło, gdybyś podał informację o stronie, na której skopiowałeś /
wkleiłeś

1
Nie ma potrzeby wstępnego tworzenia tabeli, można ją utworzyć podczas procesu importowania
obok

1
Uwielbiam to, że po prostu wycinasz i wklejasz ze strony internetowej za pomocą tak przydatnego wiersza „Poświęć trochę czasu na skonfigurowanie sposobu importowania danych” . To było wszystko, co szukam: I nie wydają się być w stanie skonfigurować go w ogóle!
Auspex

Aha, i „Sprawdź przycisk radiowy Użyj uwierzytelniania programu SQL Server” jest niepoprawny, ponieważ możesz bardzo chcieć korzystać z uwierzytelniania systemu Windows. To, co dla Ciebie działa.
Auspex

dzięki znalazłem procedurę krok po kroku ze zdjęciami do implementacji powyższej procedury, warto zajrzeć: qawithexperts.com/article/sql/…
user3559462

23

2) Jeśli klient utworzy plik CSV z programu Excel, dane zawierające przecinek są ujęte w „...” (podwójne cudzysłowy) [jak w poniższym przykładzie], więc jak import może sobie z tym poradzić?

Powinieneś użyć opcji FORMAT = „CSV”, FIELDQUOTE = „” ”:

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

1
Zauważ, że specyfikator FORMAT jest dostępny tylko od SQL Server 2017.
Kristianp

13

Najlepszym, najszybszym i najłatwiejszym sposobem rozwiązania problemu z przecinkiem w danych jest użycie programu Excel do zapisania pliku rozdzielanego przecinkami po ustawieniu separatora listy systemu Windows na coś innego niż przecinek (np. Potok). Spowoduje to wygenerowanie pliku oddzielonego potokiem (lub cokolwiek innego), który można następnie zaimportować. Jest to opisane tutaj .


4

Najpierw musisz zaimportować plik CSV do tabeli danych

Następnie możesz wstawić wiersze zbiorcze za pomocą 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);
                }
            }
        }
    }
}

być może bardziej przyjazne dla użytkownika opakowanie klas BulkCopy busybulkcopy.codeplex.com
busytools

3

Oto jak bym to rozwiązał:

  1. Wystarczy zapisać plik CSV jako arkusz XLS w programie Excel (Dzięki temu nie będziesz musiał się martwić ogranicznikami. Format arkusza kalkulacyjnego Excela zostanie odczytany jako tabela i zaimportowany bezpośrednio do tabeli SQL)

  2. Zaimportuj plik za pomocą SSIS

  3. Napisz niestandardowy skrypt w menedżerze importu, aby pominąć / zmodyfikować dane, których szukasz. (Lub uruchom skrypt główny, aby sprawdzić dane, które chcesz usunąć)

Powodzenia.


3
Uwaga: import plików XLS za pomocą SSIS jest okropny. SSIS spróbuje odgadnąć typy danych Excela, ale może zgadnąć źle i nic nie możesz na to poradzić. Znacznie lepiej korzystać z CSV.
NReilingh

Cóż, proponuję również csv, ale jeśli przeczytałeś scenariusz OP, miał on specjalne scenariusze, szczególnie z ogranicznikami, które nie są problemem w arkuszach xls. Zwykle specjalne scenariusze przypadków, takie jak te, nie wymagają obszernego rozwiązania, ale poprawki, która zachowuje dane. Podczas przesyłania pliku SSIS pozwala wybrać mapowanie danych między tabelami źródłowymi i docelowymi, co ponownie zmniejsza nakład pracy. Dlatego właśnie tę metodę zaproponowano jako szybki hack.
Zee,

1
SSIS może już obsługiwać ograniczniki tekstu CSV. Jeśli i tak używasz SSIS, problem z zapisaniem CSV jako XLS po prostu wydaje mi się, że mogę dodać potencjalne uszkodzenie bez żadnego powodu.
NReilingh

Ponadto rutynowo mam pliki CSV zbyt duże dla programu Excel.
Auspex

3

Ponieważ nie używają kreatora importu SQL, kroki byłyby następujące:

wprowadź opis zdjęcia tutaj

  1. Kliknij bazę danych prawym przyciskiem myszy w opcjach zadań importowania danych,

  2. Po otwarciu kreatora wybieramy typ danych, które mają być sugerowane. W tym przypadku byłoby to

Płaskie źródło pliku

Wybieramy plik CSV, możesz skonfigurować typ danych tabel w CSV, ale najlepiej jest przynieść go z CSV.

  1. Kliknij przycisk Dalej i wybierz ostatnią opcję, która jest

Klient SQL

W zależności od naszego rodzaju uwierzytelnienia wybieramy go, gdy to nastąpi, pojawia się bardzo ważna opcja.

  1. Możemy zdefiniować identyfikator tabeli w CSV (zaleca się, aby kolumny CSV były nazywane tak samo jak pola w tabeli). W opcji Edytuj odwzorowania możemy zobaczyć podgląd każdej tabeli z kolumną arkusza kalkulacyjnego, jeśli chcemy, aby kreator domyślnie wstawił identyfikator, pozostawiamy opcję niezaznaczoną.

Włącz wstawianie identyfikatora

(zwykle nie zaczynając od 1), zamiast tego, jeśli mamy kolumnę z identyfikatorem w pliku CSV, wybieramy opcję wstawiania włączania identyfikatora, następnym krokiem jest zakończenie działania kreatora, możemy przejrzeć zmiany tutaj.

Z drugiej strony, w kolejnym oknie mogą pojawić się alerty lub ostrzeżenia, idealnym rozwiązaniem jest zignorowanie tego, tylko jeśli pozostawiają one błąd, należy zwrócić uwagę.

Ten link zawiera obrazy .


0

Zaimportuj plik do Excela, najpierw otwierając program Excel, a następnie przechodząc do DANYCH, importując z pliku TXT, wybierz rozszerzenie csv, które zachowa 0 prefiksowanych wartości, i zapisz tę kolumnę jako TEKST, ponieważ w przeciwnym razie program Excel usunie początkowe 0 (NIE podwójnie kliknąć aby otworzyć w programie Excel, jeśli masz dane liczbowe w polu rozpoczynającym się od 0 [zero]). Następnie po prostu zapisz jako plik tekstowy rozdzielany tabulatorami. Podczas importowania do programu Excel masz opcję zapisania jako OGÓLNE, TEKSTOWE itp. Wybierz TEKST, aby cytaty w środku ciągu w polu takim jak TwojaFirma, LLC były również zachowane ...

BULK INSERT dbo.YourTableName
FROM 'C:\Users\Steve\Downloads\yourfiletoIMPORT.txt'
WITH (
FirstRow = 2, (if skipping a header row)
FIELDTERMINATOR = '\t',
ROWTERMINATOR   = '\n'
)

Chciałbym móc korzystać z funkcji FORMAT i Fieldquote, ale wydaje się, że nie jest to obsługiwane w mojej wersji SSMS


0

Wiem, że są akceptowane odpowiedzi, ale mimo to chcę podzielić się moim scenariuszem, który może pomóc komuś rozwiązać problem NARZĘDZIA

  • ASP.NET
  • PODEJŚCIE DO KODU EF
  • SSMS
  • PRZEWYŻSZAĆ

SCENARIUSZ ładowałem zestaw danych w formacie CSV, który później był wyświetlany w widoku. Próbowałem użyć ładowania zbiorczego, ale nie mogę załadować tak, jak BULK LOADkorzystałem

FIELDTERMINATOR = ','

i komórka Excel również używała, , jednak nie mogłem również używać Flat file sourcebezpośrednio, ponieważ używałem Code-First Approachi robiłem to tylko w modelu SSMS DB, a nie w modelu, z którego musiałem później korzystać z właściwości.

ROZWIĄZANIE

  1. Użyłem płaskiego źródła i utworzyłem tabelę DB z pliku CSV ( kliknij prawym przyciskiem myszy DB w SSMS -> Importuj płaski plik -> wybierz ścieżkę CSV i wykonaj wszystkie ustawienia zgodnie z zaleceniami )
  2. Utworzono klasę modelu w programie Visual Studio (MUSISZ ZACHOWAĆ wszystkie typy danych i nazwy takie same jak w przypadku pliku CSV załadowanego do sql)
  3. używać Add-Migrationw konsoli pakietu NuGet
  4. Zaktualizuj bazę danych

0

Wiem, że nie jest to dokładne rozwiązanie powyższego pytania, ale dla mnie był to koszmar, gdy próbowałem skopiować dane z jednej bazy danych znajdującej się na oddzielnym serwerze do mojego lokalnego.

Próbowałem to zrobić, najpierw eksportując dane z serwera, CSV/txta następnie importując je do mojej tabeli lokalnej.

Oba rozwiązania: zapisanie zapytania w celu zaimportowania CSVlub użycie kreatora importu danych SSMS zawsze powodowało błędy (błędy były bardzo ogólne, mówiąc, że występuje problem z analizą). I chociaż nie robiłem nic specjalnego, po prostu eksportowałem do, CSVa następnie próbowałem zaimportować CSV do lokalnego DB, błędy zawsze były.

Próbowałem spojrzeć na sekcję mapowania i podgląd danych, ale zawsze był duży bałagan. Wiem, że główny problem pochodził z jednej z tablekolumn, która zawierała JSONi SQLparser źle to traktował.

W końcu wymyśliłem inne rozwiązanie i chcę się nim podzielić na wypadek, gdyby ktoś miał podobny problem.


Zrobiłem to, że użyłem Kreatora eksportu na serwerze zewnętrznym.

Oto kroki, aby powtórzyć ten sam proces:
1) Kliknij bazę danych prawym przyciskiem myszy i wybierzTasks -> Export Data...

2) Gdy Kreator się otworzy, wybierz Dalej, a zamiast „Źródło danych:” wybierz „SQL Server Native Client”.

wprowadź opis zdjęcia tutaj

W przypadku zewnętrznego serwera najprawdopodobniej będziesz musiał wybrać „Użyj uwierzytelniania serwera SQL” dla „Trybu uwierzytelnienia:”.

3) Po wciśnięciu Dalej musisz wybrać Miejsce docelowe .
W tym celu wybierz ponownie „SQL Server Native Client”.
Tym razem możesz podać swój lokalny (lub inny zewnętrzny DB) DB.

wprowadź opis zdjęcia tutaj

4) Po naciśnięciu przycisku Dalej masz dwie opcje, aby skopiować całą tabelę z jednej DBdo drugiej lub zapisać zapytanie, aby określić dokładne dane do skopiowania. W moim przypadku nie potrzebowałem całej tabeli (była zbyt duża), ale tylko jej część, więc wybrałem „Napisz zapytanie, aby określić dane do przesłania”.

wprowadź opis zdjęcia tutaj

Sugeruję zapisanie i przetestowanie zapytania w osobnym edytorze zapytań przed przejściem do Wizard.

5) I na koniec musisz określić tabelę docelową, w której zostaną wybrane dane.

wprowadź opis zdjęcia tutaj

Sugeruję pozostawić ją jako [dbo].[Query]lub inną niestandardową Tablenazwę na wypadek, gdyby wystąpiły błędy podczas eksportowania danych lub jeśli nie jesteś pewien danych i chcesz je przeanalizować przed przejściem do dokładnie takiej tabeli, którą chcesz.

A teraz przejdź od razu do końca kreatora, naciskając przyciski Dalej / Zakończ .


Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.