Feedback

C# - CSV und XML-Datei Datenbank-Import (incl. valid-check)

Veröffentlicht von am 13.11.2008
(1 Bewertungen)
Hallo :-)

vor kurzem stand ich vor der Aufgabe bei einem MS SQL Server 2005,
mehrere tausend Datensätze aus einer CSV-Datei zu importieren.
Eine Funktion mehrere Datensätze auf einmal zu übertragen, wie bei mySQL,
gibt es nicht (erst ab Version 2008)...


Einzelne Insert-Befehle per Schleife auszuführen ist zwar möglich,
aber viel zu langsam (50.000 Datensätze >= 7 Min. im LAN)!


Hatte es dann zunächst wie folgt versucht (50k Datensätze <=10 Sek):
1) CSV-Datei von Sonderzeichen bereinigen und
2) als neue CSV speichern
3) Datenwerte gegen Schema prüfen
4) CSV als XML speichern
5) XML in DataTable und letztendlich
6) DataTable an Datenbank schicken

Dies funktionierte zwar, dazu sind (bei den Anforderungen siehe unten)
jedoch etwa 200-300 Zeilen Code und zwei Temp-Dateien/viel Speicher nötig.

Hier ein noch schnellerer und effektiverer Weg (50k <= 5 Sek) :-)
(Schritte 2 und 4 entfallen, im 5ten wahlweise xml/csv verarbeiten)

Alternativ könnte der "Bulk-Insert"-Befehl genutzt werden.
Dieser hätte u.a. folgende Nachteile:
- Bulk-Insert-Recht können nur global, nicht für einzelne DBs vergeben werden
- es funktioniert nicht mit Remote-Server (CSV wird nicht gefunden)
- CSV-Datei muss ggf. ebenfalls aufbereitet werden
- keine Möglichkeit zu überprüfen ob Daten kompatibel sind (valid-check)

So, für alle Experimentierfreudigen und "Hilfebedürftigen" ^_^, hier nun der Code:

EDIT: hab den Code etwas umgebaut:
- Aufruf per Parameter (QuellDatei, ZielTabelle, Verbindungsinfo)
- ganz unten seht ihr eine zweite Methode die ein Beispiel-Aufruf beinhaltet
- es kann nun sowohl eine XML als auch CSV-Datei importiert werden
- CSV/XML wird per OpenFileDialog ausgesucht
- InfoMeldung über Erfolg/Fehler (incl. Zeitmesser & verarbeitete Zeilen)
- Zuverlässigkeit gesteigert durch hinzufügen von einigen if-Abfragen und einer try-Klausel
GFU-Schulungen  [Anzeige]

ASP.NET Core - Der Einstieg

In nur 3 Tagen lernen Sie, wie man mit ASP.NET Core und den Technologien MVC, Entity Framework, WebAPI und Razor professionelle Web-Anwendungs-Architekturen aufbaut. Diese Schulung ist der perfekte Einstieg, um datengetriebene und präsentations-orientierte Applikationen auf Basis der ASP.NET Core Plattform zu erstellen.

C# Komplett

Sie kennen sich in objektorientierter Programmierung aus. Sie schreiben C++ oder Java? Und nun stehen Sie vor der Aufgabe, in C# Anwendungen zu erstellen. Das C# Komplett-Seminar verschafft Ihnen umfassende Programmierkenntnisse in dieser Sprache. Nach der Schulung entwickeln Sie selbständig Anwendungen mit C#. Sie kennen die Datentypen und Klassenbibliotheken der objektorientierten Programmiersprache C#. Der Komplettkurs setzt bei den Grundlagen von C# ein. Sie arbeiten mit Variablen und konvertieren Typen. Multithreading, Delegates, Generics sind nach dem Seminar für Sie kein Geheimnis mehr.

//using System.Data;
//using System.Data.SqlClient;
//using System.IO;

private void CSV_XML_nach_Datenbank(String dateiPfad, String ZielTabelle, SqlConnection con)
{
//knoxyz 14.11.2008
    try
    {
        DateTime startzeit = System.DateTime.Now;

        //stellt Vermittler zwischen DatenQuelle(sqlServer) und DatenZiel(DataSet/DataTable) dar
        SqlDataAdapter da = new SqlDataAdapter();

        //enthält Tabellen
        DataSet ds = new DataSet();

        SqlCommand cmd = new SqlCommand();  //der sqlCommand nimmt SQL-Befehle entgegen
        cmd.Connection = con;               //generell festlegen, für welche Datenbank er diese Befehle ausführen soll

        //ggf Verbindung herstellen
        if (con.State != ConnectionState.Open) con.Open();

        String schemaPfad = Application.StartupPath + @"\Schema_" + ZielTabelle + ".xsd";

        //(Optional) Per TableMapping können Tabellen einen Namen gegeben werden. Alte Einträge entfernen
        da.TableMappings.Clear();

        //Welche Datenbankt
        cmd.CommandText = "SELECT * FROM " + ZielTabelle;


        da.SelectCommand = cmd;
        da.TableMappings.Add("Table", ZielTabelle);
        ds.Clear();
        if (File.Exists(schemaPfad) == false) //Fall keine Schemadatei vorhanden ist, jetzt eine erstellen
        {                                    //sofern Änderungen an der Datenbank gemacht wurden, die Schema.xsd einfach löschen
            da.Fill(ds); //Tabellen des DataAdapter in ein DataSet schreiben
            da.FillSchema(ds, SchemaType.Source); //Schema(Aufbau) der SQL-Tabellen auch im DataSet übernehmen
            ds.WriteXmlSchema(schemaPfad); //Schema lokal speichern
        }
        //ds.WriteXml(schemaPfad + ".xml");//Schema und Daten lokal speichern (BackUp)


        //Ziel-SQL-Tabelle
        //  anders als hier, schicke ich die Daten erst an eine Temp-Tabelle, die den gleichen Aufbau wie die eigentliche Ziel-Tabelle hat,
        //  und frage per Exist-Befehl anhand verschiedener Kriterien ab, ob Datensätze evtl. in der Ziel-Tab bereits vorhanden sind.
        DataTable dt = new DataTable("Artikelstamm");

        //um zu überprüfen ob die CVS-Daten zur SQL-Tabelle passen, hier das Schema der Datenbank übergeben
        dt.ReadXmlSchema(schemaPfad);

        //festlegen wo die CSV zu finden ist, die wir an die Datenbank schicken wollen

        //Wurde eine eine 'xml-Datei' ausgesucht..
        if (dateiPfad.EndsWith(".xml", StringComparison.OrdinalIgnoreCase))
        {
            dt.ReadXml(dateiPfad);
        }
        else
        {
            if (//...oder CSV/Textdatei?
                (dateiPfad.EndsWith(".csv", StringComparison.OrdinalIgnoreCase))
                || (dateiPfad.EndsWith(".txt", StringComparison.OrdinalIgnoreCase))
               )
            {
                StreamReader DateiLeser = new StreamReader(dateiPfad, System.Text.Encoding.Default);

                //Die nachfolgende Replace-Aufrufe entfernen Sonderzeichen, die mögliche Probleme verursachen könnten
                //Spaltenüberschriften auslesen und ggf. Punkte mit Unterstrich ersetzen
                String Zeileninhalt = DateiLeser.ReadLine().Replace(".", "_").Replace("\t", ";");
                DateiLeser.ReadLine(); //Leerzeile falls Daten erst in 3ter Zeile beginnen


                char trennzeichen = ';';
                //Der Inhalt der CSV-Datei wird Zeile für Zeile ausgelesen und in eine DataTable geschrieben
                while (DateiLeser.EndOfStream == false)
                {
                    Zeileninhalt = DateiLeser.ReadLine().Replace("'", "").Replace(";", "").Replace("=", "").Replace("\"", "");
                    Zeileninhalt = Zeileninhalt.Replace("\t", ";").Substring(0, Zeileninhalt.Length - 1);
                    //Zeileninhalt = Zeileninhalt.Replace("\t", ";"); //je nach CSV-Aufbau, diese oder Zeile darüber benutzen

                    //Inhalt einer Zeilen in Teilstrings zerlegen.
                    String[] csvZeilenInhalt = Zeileninhalt.Split(trennzeichen);

                    dt.Rows.Add(csvZeilenInhalt);
                }
                DateiLeser.Close();
            }
            else { MessageBox.Show("ungültige Datei ausgesucht", "falscher Dateityp"); }
        }
        if (dt.Rows.Count > 0) //wurden Daten aus der Quelldatei in DataTable kopiert?
        {
            //Jetzt liegt der Inhalt der CSV-Datei, auf Datentypen geprüft und mit Schema,
            // im DataTable und kann in einem Rutsch (als Objekt) an den Datenbank-Server geschickt werden :-)
            SqlBulkCopy bcopy = new SqlBulkCopy(con);
            bcopy.DestinationTableName = ZielTabelle;
            bcopy.WriteToServer(dt);
            //dt.WriteXml(dateiPfad + ".xml"); //csv als xml speichern

            String dauer = new DateTime(System.DateTime.Now.Ticks - startzeit.Ticks).ToString("mm 'Minuten und' ss 'Sekunden'");
            MessageBox.Show(dt.Rows.Count + " Zeilen wurden in " + dauer + " verarbeitet :-)",
                            "Import war erfolgreich :-)");
            dt.Clear();
            //try
            //{
            //(optional) Log-Datei der Datenbank verkleinern
            //cmd.CommandText = "DBCC SHRINKDATABASE(N'" + con.Database + "' )";
            //cmd.ExecuteNonQuery();
            //}
            //catch (Exception e) { MessageBox.Show(e.Message); }
        }
    }
    catch (Exception importFehler)
    {
        MessageBox.Show(importFehler.Message, "Import-Fehler");
    }
}



//Hier folgt ein Beispiel wie die Methode aufgerufen werden könnte
private void BeispielZumAufrufDerImportMethode_Click(object sender, EventArgs e)
{   //Vorbereitung

    String zielTabelle = "Artikelstamm";

    String dateiPfad = String.Empty;
    OpenFileDialog dateiAuswaehlen = new OpenFileDialog();
    dateiAuswaehlen.Filter = "csv-Datei (*.txt)|*.txt"
                             + "|xml-Datei (*.xml)|*.xml"
                             + "|alle Dateien (*.*)|*.*";
    dateiAuswaehlen.Title = zielTabelle;

    if (dateiAuswaehlen.ShowDialog() == DialogResult.OK) dateiPfad = dateiAuswaehlen.FileName;


    //Verbindungsaufbau definieren
    SqlConnectionStringBuilder conBuilder = new SqlConnectionStringBuilder();
    conBuilder.DataSource = "(local)";        //kann auch eine IP oder Name sein, dann ohne Klammern
    conBuilder.InitialCatalog = "knoxyz";
    conBuilder.ConnectTimeout = 10;
    conBuilder.IntegratedSecurity = true; //oder User & Password
    //conBuilder.UserID = "knoxyz";
    //conBuilder.Password = "xyz";

    SqlConnection con = new SqlConnection(conBuilder.ConnectionString);
    //Prüfen ob Parameter Werte haben, falls nicht kein Aufruf
    if ((dateiPfad != String.Empty) && (zielTabelle.Trim() != "") && (con.ConnectionString != ""))
        CSV_XML_nach_Datenbank(dateiPfad, zielTabelle, con);
    else MessageBox.Show("ParameterWerte für DB-Import stimmen nicht");
}

5 Kommentare zum Snippet

knoyxz schrieb am 13.11.2008:
Gibt es eine Möglichkeit sein "Snippet" zu bearbeiten?

Diese usings werden benötigt:
using System.Data;
using System.Data.SqlClient;
using System.IO;
Jan Welker schrieb am 13.11.2008:
Hallo,
ja das geht, mit dem blauen Notizblock unter dem Snippet.
Jan
knoyxz schrieb am 13.11.2008:
Dankeschön Jan :-)
Günther Foidl schrieb am 13.11.2008:
Der ConnectionString kann auch mit dem SqlConnectionStringBuilder erstellt werden.
knoyxz schrieb am 13.11.2008:
Gute Einwand den SqlConnectionStringBuilder zu benutzen,
da dieser, noch vor dem Versuch eine Verbindung aufzubauen,
überprüft, ob nur gültige Werte für die Verbindungseigenschaften angegeben wurden.
(Bsp: für TimeOut keine Buchstaben zugelassen, ect)

Habe es im Code entsprechend angepasst.
 

Logge dich ein, um hier zu kommentieren!