Zaimportuj plik CSV do programu SQL Server

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
)

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
    )

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):

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
)

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 .

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);
                }
            }
        }
    }
}

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.

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

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.

3. 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.

4. 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 .

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

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

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”.

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.

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”.

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.

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 .

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