CSV-bestand importeren in SQL Server

Ik zoek hulp bij het importeren van een .csv-bestand in SQL Server met behulp van BULK INSERTen ik heb enkele basisvragen.

Problemen:

1. De gegevens van het CSV-bestand kunnen ,(komma) tussen hebben (bijvoorbeeld: beschrijving), dus hoe kan ik deze gegevens importeren?

2. Als de client de CSV vanuit Excel maakt, worden de gegevens met komma’s tussen ""(dubbele aanhalingstekens) [zoals in het onderstaande voorbeeld] ingesloten, dus hoe kan de import hiermee omgaan ?

3. Hoe houden we bij of sommige rijen slechte gegevens bevatten, welke import overslaat? (Import slaat rijen over die niet importeerbaar zijn)

Hier is de voorbeeld-CSV met koptekst:

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.

En SQL-statement om te importeren:

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
)

Antwoord 1, autoriteit 100%

Op basis van SQL Server CSV-import

1) De gegevens van het CSV-bestand kunnen ,(komma) tussen hebben (Bijv.:
beschrijving), dus hoe kan ik deze gegevens importeren?

Oplossing

Als u ,(komma) als scheidingsteken gebruikt, is er geen manier om onderscheid te maken tussen een komma als veldterminator en een komma in uw gegevens. Ik zou een andere FIELDTERMINATORgebruiken, zoals ||. De code zou er zo uitzien en dit zal perfect omgaan met komma’s en enkele slash.

2) Als de client de csv van Excel maakt, dan zijn de gegevens die:
komma’s staan tussen " ... "(dubbele aanhalingstekens) [zoals hieronder
voorbeeld] dus hoe kan de import hiermee omgaan?

Oplossing

Als u BULK-invoeging gebruikt, is er geen manier om dubbele aanhalingstekens te verwerken, gegevens worden
ingevoegd met dubbele aanhalingstekens in rijen.
na het invoegen van de gegevens in de tabel zou je die dubbele aanhalingstekens kunnen vervangen door ‘‘.

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

3) Hoe houden we bij of sommige rijen slechte gegevens bevatten, welke import overslaat?
(slaat importeren rijen over die niet kunnen worden geïmporteerd)?

Oplossing

Het afhandelen van rijen die niet in de tabel zijn geladen vanwege ongeldige gegevens of indeling, kan zijn:
afhandelen met behulp van ERRORFILE-eigenschap, specificeer de naam van het foutbestand, het zal de rijen
met een fout naar een foutbestand. code eruit zou moeten zien.

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
    )

Antwoord 2, autoriteit 22%

U moet eerst een tabel in uw database maken waarin u het CSV-bestand gaat importeren. Volg de onderstaande stappen nadat de tabel is gemaakt.

• Log in op uw database met SQL Server Management Studio

• Klik met de rechtermuisknop op uw database en selecteer Tasks -> Import Data...

• Klik op de knop Next >

• Selecteer voor de gegevensbron Flat file source. Gebruik vervolgens de knop Bladeren om het CSV-bestand te selecteren. Besteed wat tijd aan het configureren van hoe u de gegevens wilt importeren voordat u op de knop Next >klikt.

• Selecteer voor de bestemming de juiste databaseprovider (bijvoorbeeld voor SQL Server 2012 kunt u SQL Server Native Client 11.0 gebruiken). Voer de servernaam in. Vink het keuzerondje Use SQL Server Authenticationaan. Voer de gebruikersnaam, het wachtwoord en de database in voordat u op de knop Next >klikt.

• In het venster Brontabellen en -weergaven selecteren kunt u toewijzingen bewerken voordat u op de knop Next >klikt.

• Vink het vakje Run immediatelyaan en klik op de knop Next >.

• Klik op de knop Finishom het pakket uit te voeren.

Het bovenstaande is gevonden op deze website(ik heb het gebruikt en getest):

Antwoord 3, autoriteit 14%

2) Als de client de csv maakt vanuit Excel, dan zijn de gegevens die:
komma’s staan tussen ” … ” (dubbele aanhalingstekens) [zoals hieronder
voorbeeld] dus hoe kan de import hiermee omgaan?

U moet de opties FORMAT = ‘CSV’, FIELDQUOTE = ‘”‘ gebruiken:

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
)

4, Autoriteit 8%

De beste, de snelste en gemakkelijkste manier om de komma in data-probleem op te lossen, is om Excel te gebruiken om een ​​door komma’s gescheiden bestand op te slaan nadat u de instelling van Windows ‘List Separator hebt ingesteld op iets anders dan een komma (zoals een pijp). Dit genereert dan een pijp (of welk) gescheiden bestand voor u die u kunt importeren. Dit wordt beschreven hier .

5, Autoriteit 2%

FIRS U moet het CSV-bestand invoeren in de gegevenstabel

Dan kunt u bulkrijen invoegen met behulp van 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);
                }
            }
        }
    }
}

Antwoord 6, autoriteit 2%

Zo zou ik het oplossen:

1. Sla uw CSV-bestand gewoon op als een XLS-blad in Excel (Hierdoor hoeft u zich geen zorgen te maken over scheidingstekens. Het spreadsheetformaat van Excel wordt gelezen als een tabel en rechtstreeks geïmporteerd in een SQL-tabel)

2. Importeer het bestand met SSIS

3. Schrijf een aangepast script in de importmanager om de gegevens die u zoekt weg te laten/aan te passen. (Of voer een hoofdscript uit om de gegevens die u wilt verwijderen te onderzoeken)

Veel succes.

Antwoord 7, autoriteit 2%

Omdat ze de SQL-importwizard niet gebruiken, zijn de stappen als volgt:

1. Klik met de rechtermuisknop op de database in de optie taken om gegevens te importeren,

2. Zodra de wizardis geopend, selecteren we het type gegevens dat moet worden geïmpliceerd. In dit geval zou het de

. zijn

Platte bestandsbron

We selecteren het CSV-bestand, u kunt het gegevenstype van de tabellen in de CSV configureren, maar het is het beste om het uit de CSV te halen.

3. Klik op Volgende en selecteer in de laatste optie die is

SQL-client

Afhankelijk van ons type authenticatie die we selecteren, komt er een zeer belangrijke optie als dit is gedaan.

4. We kunnen de id van de tabel in de CSV definiëren (het wordt aanbevolen dat de kolommen van de CSV dezelfde naam hebben als de velden in de tabel). In de optie Bewerk toewijzingen kunnen we het voorbeeld van elke tabel zien met de kolom van de spreadsheet, als we willen dat de wizard de id standaard invoegt, laten we de optie uitgeschakeld.

ID invoegen inschakelen

(meestal niet beginnend bij 1), in plaats daarvan, als we een kolom met de id in de CSV hebben, selecteren we de enable id insert, de volgende stap is het beëindigen van de wizard, we kunnen de wijzigingen hier bekijken.

Aan de andere kant kan in het volgende venster waarschuwingen, of waarschuwingen komen, het ideaal is om dit te negeren, alleen als ze een fout achterlaten is noodzakelijk om op te letten.

Deze link heeft afbeeldingen .

8

Importeer het bestand in Excel door eerst Excel te openen, ga dan naar DATA, importeer uit TXT-bestand, kies de csv-extensie die 0 vooraf ingestelde waarden behoudt, en sla die kolom op als TEXT omdat Excel anders de leidende 0 laat vallen (DO NIET dubbelklikken om te openen met Excel als u numerieke gegevens hebt in een veld dat begint met een 0 [nul]). Sla dan gewoon op als een door tabs gescheiden tekstbestand. Wanneer u in Excel importeert, krijgt u een optie om op te slaan als ALGEMEEN, TEKST, enz. Kies TEKST zodat aanhalingstekens in het midden van een tekenreeks in een veld als YourCompany,LLC ook behouden blijven…

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

Ik wou dat ik de FORMAT- en Fieldquote-functionaliteit kon gebruiken, maar dat lijkt niet te worden ondersteund in mijn versie van SSMS

Antwoord 9

Ik weet dat er geaccepteerde antwoorden zijn, maar toch wil ik mijn scenario delen dat iemand misschien kan helpen om hun probleem op te lossen
GEREEDSCHAP

• ASP.NET
• EF CODE-EERSTE AANPAK
• SSMS
• EXCEL

SCENARIO
ik was de dataset aan het laden in CSV-indeling die later in de weergave zou worden getoond
ik heb geprobeerd de bulklading te gebruiken, maar ik kan niet laden omdat BULK LOAD

gebruikte

FIELDTERMINATOR = ','

en Excel-cel gebruikte ook ,
ik kon echter ook Flat file sourceniet rechtstreeks gebruiken omdat ik Code-First Approachgebruikte en dat maakte alleen model in SSMS DB, niet in het model waaruit Ik moest de eigenschappen later gebruiken.

OPLOSSING

1. Ik heb een platte bestandsbron gebruikt en een DB-tabel gemaakt van een CSV-bestand (Klik met de rechtermuisknop op DB in SSMS -> Importeer plat bestand -> selecteer CSV-pad en voer alle instellingen uit zoals aangegeven)
2. Modelklasse gemaakt in Visual Studio (U MOET alle datatypes en namen hetzelfde houden als die van het CSV-bestand dat in sql is geladen)
3. gebruik Add-Migrationin de NuGet-pakketconsole
4. DB bijwerken

Antwoord 10

Misschien kan SSMS: gegevens importeren (kopiëren/plakken) uit Excelhelpen (als u dat niet doet) wil BULK INSERTgebruiken of heb er geen rechten voor).