Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1620to1624
1620to1624
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

CSV-Daten-Import mittels QueryTable

Forumthread: CSV-Daten-Import mittels QueryTable

CSV-Daten-Import mittels QueryTable
26.04.2018 20:54:44
Chris
’Nabend,
das Thema ist sicher schon oft angesprochen worden, als VBA-Anfänger tue ich mich bisher aber schwer daran, die vorhandenen Lösungen an meine Bedürfnisse anzupassen.
Was mein Ziel ist: Mit einem Makro möchte ich in einer offenen Arbeitsmappe die Daten einer CSV-Datei als neues Tabellenblatt importieren.
Zur Verfügung steht mir dafür Excel 2016 und ich dachte an die QueryTable-Funktion.
Die Daten in der CSV-Datei sind mittels Komma getrennt und enthalten in der ersten Zeile eine Überschrift, die ebenfalls übernommen werden soll.
Das neue Tabellenblatt möchte ich, einsortiert ganz rechts als letztes Blatt, mit dem Namen der CSV-Datei benennen - ohne die Endung CSV.
Meine ersten Versuche in der Richtung hab ich mit dem Makrorecorder von Excel 2016 und der Daten-Importfunktion gemacht. Das Ergebnis enthält aber unter anderem farblich gestaltete Zeilen und Spalten, ohne das die CSV-Datei dafür eine Grundlage liefert. Kurzum, die Importfunktion von Excel scheint zusätzliche (Design-)Formatierungen vorzunehmen, die ich dort nicht verhindern kann.
Kann mir hierzu jemand weiter helfen?
Wenn der dafür passende Code auch noch ein paar Erläuterungen der einzelnen Schritte enthielte, wäre das Traumhaft,
Vielen Dank schon mal für eure Mühe.
Anzeige

6
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: CSV-Daten-Import mittels QueryTable
27.04.2018 10:05:56
mmat
Moin,
Guck mal ob das so für dich passt. Ggf ist noch ein wenig Feinschliff erforderlich. Aber das ist garantiert ohne erzwungene Designer-Farben ...
Sub ImportCSV()
Dim d As String
d = "C:\MeinOrdner\Datei.txt" 'Anpassen
Workbooks.OpenText Filename:=d, Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, _
Comma:=True, Space:=False, Other:=False
Columns.AutoFit
Range("A1").Select
End Sub

Anzeige
AW: CSV-Daten-Import mittels QueryTable
27.04.2018 15:01:25
Chris
Hallo mmat,
dass damit keine unerwünschten Designfarben hinzugefügt werden, stimmt.
Der Grund dafür ist allerdings, dass dein Code die für die Variable "d" definierte CSV-Datei öffnet.
Soweit ich das richtig nachlese, ist das auch der Sinn der von dir verwendeten Methode Workbooks.OpenText
Mein Ziel ist es aber, dass ich eine offene, leere Arbeitsmappe mit dem Makro habe und dort den Inhalt einer CSV-Datei als neues, letztes Tabellenblatt einfügen möchte.
Anzeige
AW: CSV-Daten-Import mittels QueryTable
27.04.2018 16:10:42
mmat
Hallo Chris,
viele Wege führen nach Rom. Bei meinem Code brauchts für Deine Anforderung 2 Zeilen mehr ...
Sub ImportCSV()
Dim d As String, wb As Workbook
Set wb = ActiveWorkbook
d = "C:\MeinOrdner\Datei.txt" 'Anpassen
d = "U:\FSP-Daten\Excel\BT1-Muster.txt"
Workbooks.OpenText Filename:=d, Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, _
Comma:=True, Space:=False, Other:=False
Columns.AutoFit
Range("A1").Select
ActiveSheet.Move after:=wb.Sheets(wb.Sheets.Count)
End Sub

Anzeige
AW: CSV-Daten-Import mittels QueryTable
27.04.2018 11:07:32
EtoPHG
Hallo Chris,
Zitat: Kurzum, die Importfunktion von Excel scheint zusätzliche (Design-)Formatierungen vorzunehmen, die ich dort nicht verhindern kann.
Ja das ist so, weil XL aus Queries automatisch ein sogenanntes ListObject macht.
Das ist IMHO auch gut so. Wer sich an der 'Standardformatierung' stört kann diese im Menü-Tab |Entwurf| immer noch nach seinem Gusto einstellen. Ich vermute aber, dass durch deine Makroaufzeichnung immer ein neues ListObject angelegt wird, statt immer wieder das einmalig erstellte zu nutzen.
Für eine konkrete Hilfestellung ist eine Beispielmappe, incl. aufgezeichnetem Code nötig
Gruess Hansueli
Anzeige
AW: CSV-Daten-Import mittels QueryTable
27.04.2018 15:29:44
Chris
Hallo Hansueli,
die Makroaufzeichnung hatte mir ja bereits gezeigt, wie der Befehl für eine nachträgliche Entfernung des Designs aussieht. Hatte nur auf eine Möglichkeit gehofft, ganz ohne dieses Design aus zu kommen.
Was mir die Makroaufzeichnung liefert, ist folgendes: 
Sub Makro5()
' Makro5 Makro
ActiveWorkbook.Queries.Add Name:="Original_180328", Formula:= _
"let" & Chr(13) & "" & Chr(10) & "    Quelle = Csv.Document(File.Contents(""D:\Test\ _
_Original_180328.csv""),[Delimiter="","", Columns=27, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    #""Erste Zeile als Überschriften verwenden"" = Table.PromoteHeaders(Quelle, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Typ ändern"" = Table.TransformColumnTypes(#""Erste Zeile a" & _
"ls Überschriften verwenden"",{{""IP Adresse"", type text}, {""Datum"", type text}, {"" _
Uhrzeit"", type time}, {""Zeitzone"", type text}, {""laufende Nr."", Int64.Type}, {""Serviceprovider"", type text}, {""Anrede"", type text}, {""Vorname"", type text}, {""Nachname"", type text}, {""Straße"", type text}, {""PLZ"", type text}, {""Ort"", type text}, {""Kennung"", type " & _
"text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Typ ändern" _
ActiveWorkbook.Worksheets.Add
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location= _
Original_180328;Extended Properties=""""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [Original_180328]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Original_180328"
.Refresh BackgroundQuery:=False
End With
ActiveSheet.ListObjects("Original_180328").TableStyle = ""
End Sub
Versteh ich das einigermaßen richtig, dass er mittels Queries.Add erst einmal die einzulesende CSV-Datei als Abfrage erstellt, definiert wie das Trennzeichen ist und welcher Datentyp jede einzelne Spalte ist?
Fehlt dann am Ende nicht das löschen der Abfrage, wenn sie nicht mehr gebraucht wird?
Was der weitere Code im Detail macht, erschließt sich mir allerdings noch nicht vollständig. Außer, dass er scheinbar mit CommandText aus einem irgendwo definierten Array den Inhalt heraus greift.
Anzeige
AW: CSV-Daten-Import mittels QueryTable
27.04.2018 16:25:43
mmat
Hallo Chris,
ja, du hast den Hintergrund des Codes einigermaßen verstanden. Command-Text ist nix weiter wie eine SQL-Abfrage, und die Datenbank deine Textdatei (das Array...).
Die Abfrage wird nicht gelöscht, denn in Excel gibt's bei diesen Abfragen die Möglichkeit mit einem Knopfdruck die Daten zu aktualisieren (was in deinem Fall vielleicht gar keinen Sinn macht).
Anzeige
;
Anzeige
Anzeige

Infobox / Tutorial

CSV-Daten-Import mit QueryTable in Excel 2016

Schritt-für-Schritt-Anleitung

  1. Makro erstellen: Öffne Excel 2016 und drücke ALT + F11, um den VBA-Editor zu öffnen.

  2. Neues Modul einfügen: Klicke mit der rechten Maustaste auf "VBAProject (DeinWorkbookName)" und wähle Einfügen > Modul.

  3. Code einfügen: Kopiere den folgenden Code in das Modul:

    Sub ImportCSV()
    Dim d As String, wb As Workbook
    Set wb = ActiveWorkbook
    d = "C:\MeinOrdner\Datei.txt" 'Anpassen
    With wb.QueryTables.Add(Connection:="TEXT;" & d, Destination:=wb.Sheets.Add.Range("A1"))
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .Refresh
    End With
    ActiveSheet.Move after:=wb.Sheets(wb.Sheets.Count)
End Sub

  4. Dateipfad anpassen: Ersetze C:\MeinOrdner\Datei.txt mit dem Pfad zu deiner CSV-Datei.

  5. Makro ausführen: Schließe den VBA-Editor und führe das Makro aus, um die CSV-Daten zu importieren.

Häufige Fehler und Lösungen

  • Fehler: Datei nicht gefunden
    Lösung: Überprüfe den angegebenen Pfad zur CSV-Datei. Stelle sicher, dass die Datei existiert.

  • Fehler: Unerwünschte Formatierungen
    Lösung: Der Import über QueryTables.Add sollte ohne zusätzliche Formatierungen erfolgen. Achte darauf, dass du die richtigen Trennzeichen in deinem Code angibst.

Alternative Methoden

  • Power Query: In Excel 2016 kannst du auch Power Query verwenden, um CSV-Daten zu importieren. Gehe zu Daten > Abfragen & Verbindungen > Daten abrufen > Aus Datei > Aus Text/CSV. Dies ermöglicht eine benutzerfreundliche Methode, um CSV-Daten mit verschiedenen Optionen zu importieren.

  • Direkter Import ohne VBA: Du kannst die Datei auch direkt über Daten > Aus Text/CSV importieren und die Trennzeichen im Dialogfeld auswählen.

Praktische Beispiele

Hier ist ein Beispiel, wie du mit dem vba querytables.add Befehl eine CSV-Datei importierst:

Sub ImportCSVExample()
    Dim csvFile As String
    csvFile = "C:\MeinOrdner\Beispiel.csv"

    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & csvFile, Destination:=Range("A1"))
        .TextFileCommaDelimiter = True
        .TextFileConsecutiveDelimiter = False
        .Refresh
    End With
End Sub

In diesem Beispiel wird eine CSV-Datei mit Komma als Trennzeichen importiert.

Tipps für Profis

  • Dynamische Dateipfade: Verwende eine Input-Box, um den Dateipfad für die CSV-Datei dynamisch abzufragen. So kannst du das Makro flexibler gestalten.

  • Fehlerbehandlung: Integriere Fehlerbehandlungsroutinen in deinen Code, um unerwartete Fehler beim Import zu erkennen und zu beheben.

  • Hintergrundabfrage: Setze .BackgroundQuery = False, um sicherzustellen, dass der Importprozess abgeschlossen ist, bevor das Makro fortfährt.

FAQ: Häufige Fragen

1. Wie ändere ich das Trennzeichen für den CSV-Import?
Du kannst das Trennzeichen in der QueryTables.Add Methode ändern, indem du .TextFileCommaDelimiter, .TextFileSemicolonDelimiter usw. entsprechend anpasst.

2. Kann ich die Daten nach dem Import weiter bearbeiten?
Ja, du kannst die importierten Daten ganz normal in Excel bearbeiten, formatieren oder analysieren.

3. Ist dieser Code in anderen Excel-Versionen anwendbar?
Der Code sollte auch in Excel-Versionen ab 2010 funktionieren, allerdings können die Menüs und Optionen leicht variieren.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige