none
ODBC Verbindung und Microsoft Text-Treiber (*.txt; *.csv) RRS feed

  • Frage

  • Hallo zusammen,

    ich habe eine VBA Prozedur, die eine Verbindung zu einer Text-Datei herstellt und dann die Daten in einer Pivot-Table auswertet.
    Unter Excel 2000 funktioniert das einwandfrei. In Excel 2010 leider nicht mehr.
    Die Text Datei wird unter Excel 2010 nicht mehr in Spalten aufgeteilt, sondern als eine einzige Spalte importiert.
    Das Trennzeichen ist ein Semikolon.

    Scheinbar erwartet der Microsoft Text-Treiber in Excel 2010 auf ein Komma als Trennzeichen, statt einem Semikolon.

    hier ein Ausschnitt aus dem Code:

    sql = "SELECT " _
              & spalten _
              & " FROM" _
              & "`" _
              & selectedFileName _
              & "`"
       
        conn = "ODBC;DBQ=" _
               & filePath _
               & ";DefaultDir=" _
               & filePath _
               & ";Driver={Microsoft Text-Treiber (*.txt; *.csv)};DriverId=27;FIL=text;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5; 
                    SafeTransactions=0;Threads=3;UserCommitSync=Yes;"


        With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
            .Connection = conn
            .CommandType = xlCmdSql
            .CommandText = sql
            .CreatePivotTable TableDestination:=Range("A8"), TableName:="PivotTable1"
        End With

    Danach passiert noch einiges mit der Pivot-Table, aber das funktioniert.                  

    Kann ich das irgendwo als Parameter definieren, wie das Trennzeichen zu lauten hat?
    Und noch viel spannender ist: Wieso funktioniert das Semikolon als Trennzeichen in Excel 2000, aber nicht in Excel 2010 ?

    Ich freue mich auch, wenn es einen besseren Ansatz gibt, um die Verbindung zwischen PT und Text Datei herzustellen.

    Besten Dank für eure Ideen!

    Viele Grüße

    Joe
    Freitag, 13. September 2013 07:15

Antworten

  • Hi Andreas,

    leider funktioniert dein Makro nur bei Textfeldern ohne Zeilenumbruch.
    Ich habe auch mal versucht solch ein Modul zu stricken, habe es aber auch diesem Grund wieder gelassen.
    Der "Microsoft Text-Treiber" kann das besser als ich und den kleinen Umweg über die Schema.INI bzw. die Import-Definition von ACCESS sind ihmo nicht das Problem. Da man mit der Schema.INI noch mehr nette Sachen machen kann, die einem in XL auf die Füsse fallen, wie Textimport von zeit-ähnlichen Texten und amerikanische Dezimalzahlen.

    4 Zeilen XL-VBA-Code sind nicht das Problem:

    Open sPath & "\Schema.ini" For Output As #1
    Print #1, "["; sFileName ; "]"
    Print #1, "Format=Delimited("; Application.International(xlListSeparator); ")"
    Close #1

    cu Carsten

    Komplett:

    Private Sub PVT_Data()
    Dim sql As String
    Dim conn As String
    Dim selectedFileName As String
    Dim spalten As String
    Dim filePath As String

    filePath = "C:\Users\Name\Desktop\"
    selectedFileName = "File.csv"
    spalten = "*"

    Open filePath & "Schema.ini" For Output As #1
    Print #1, "["; selectedFileName; "]"
    Print #1, "Format=Delimited("; Application.International(xlListSeparator); ")"
    Close #1

    sql = "SELECT " _
               & spalten _
               & " FROM " _
               & "`" _
               & selectedFileName _
               & "`"
        
        conn = "ODBC;DBQ=" _
                & filePath _
                & ";DefaultDir=" _
                & filePath _
                & ";Driver={Microsoft Text-Treiber (*.txt; *.csv)};DriverId=27;FIL=text;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UserCommitSync=Yes;"
     
        With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
             .Connection = conn
             .CommandType = xlCmdSql
             .CommandText = sql
             .CreatePivotTable TableDestination:=Range("A8"), TableName:="PivotTable1"
         End With
    Kill filePath & "Schema.ini"
    End Sub


    • Als Antwort markiert Joe_86 Dienstag, 17. September 2013 09:13
    • Bearbeitet CSekulla Sonntag, 22. September 2013 09:16 doppelter \
    Montag, 16. September 2013 07:09

Alle Antworten

  • Hi

    ohne jetzt dein Problem genau zu analysieren, denke ich, ich hatte was ähnliches mit ACCESS.

    Es gibt 2 Wege (siehe auch ms-office-forum):

    1. das Trennzeichen in der Registry ändern (finde ich nicht so gut)

    2. ein Schema.ini zu erstellen (einfache Textdatei) und neben die Datenquelle zu kopieren.

    2. hat bei mir geholfen.

    cu CS


    • Bearbeitet CSekulla Freitag, 13. September 2013 09:16
    Freitag, 13. September 2013 09:13
  • Hi!

    Danke für den Tipp!

    Das mit der Registry find ich auch nicht ganz so prickelnd... ;-)

    Die Schema.ini hilft mir leider nicht wirklich weiter...
    Ich habe relativ viele solche Text Dateien, die vom ERP System ausgegeben werden.

    Der Benutzer wählt dann am Anfang des Makors die passende Datei per File-Dialog aus.
    Wenn ich das mit der Schema.ini richtig verstanden habe, brauche ich für jede Txt-Datei eine eigene Schema.ini...
    oder kann ich die auch "global" definieren, sodass sie für alle Txt Dateien in dem betreffenden Ordner gilt?

    Am meisten verwundert mich aber, dass es dem Microsoft Text-Treiber unter Excel 2000 scheinbar nichts ausmacht, dass das Trennzeichen kein Komma ist...

    Besten Dank!

    Grüße

    Jo

    Freitag, 13. September 2013 11:07
  • Hi

    wundern hilft nichts.. ihmo haben die da in 2010 was verpennt.

    Liest du die Daten per VBA o.ä. ein?

    Ich habe das wie folgt gelöst:

    1. Fileopendialog (csv auswählen lassen)  

    2. Path ermitteln

    3. Schema.ini in Path erzeugen

    (Open " Schema.ini " for Output .... Print #1, "[filename.txt]  Format=Delimited(;)" Close)

    4. Datei einlesen

    5. Schema.ini löschen

    cu CS

    Freitag, 13. September 2013 14:31
  • Wenn Du die CSV-Datei in Excel einliest, dann brauchst Du keine schema.ini und keinen Registry Zugriff und Du hast alle Möglichkeiten die man sich denken kann.

    https://dl.dropboxusercontent.com/u/35239054/CSV.xla

    Klick mit rechts auf eine Zelle, das CSV-Menü ist ganz unten. Du kannst auch ein Makro während des Ex-/Imports aufnehmen oder den Code direkt verwenden, ist nicht geschützt.

    Du musst natürlich Deine Pivottabelle auf die entsprechenden Zellen setzen, sprich mit SQL wird das dann wohl nix. Aber das wirst Du besser als ich wissen, Pivottabellen brauche ich nur alle paar Jahre mal, hab ich ganz wenig Erfahrung.

    Andreas.

    PS.: Bin die nächsten 3 Wochen in Urlaub, eine Antwort kann etwas dauern.

    Sonntag, 15. September 2013 09:05
  • Hi Andreas,

    leider funktioniert dein Makro nur bei Textfeldern ohne Zeilenumbruch.
    Ich habe auch mal versucht solch ein Modul zu stricken, habe es aber auch diesem Grund wieder gelassen.
    Der "Microsoft Text-Treiber" kann das besser als ich und den kleinen Umweg über die Schema.INI bzw. die Import-Definition von ACCESS sind ihmo nicht das Problem. Da man mit der Schema.INI noch mehr nette Sachen machen kann, die einem in XL auf die Füsse fallen, wie Textimport von zeit-ähnlichen Texten und amerikanische Dezimalzahlen.

    4 Zeilen XL-VBA-Code sind nicht das Problem:

    Open sPath & "\Schema.ini" For Output As #1
    Print #1, "["; sFileName ; "]"
    Print #1, "Format=Delimited("; Application.International(xlListSeparator); ")"
    Close #1

    cu Carsten

    Komplett:

    Private Sub PVT_Data()
    Dim sql As String
    Dim conn As String
    Dim selectedFileName As String
    Dim spalten As String
    Dim filePath As String

    filePath = "C:\Users\Name\Desktop\"
    selectedFileName = "File.csv"
    spalten = "*"

    Open filePath & "Schema.ini" For Output As #1
    Print #1, "["; selectedFileName; "]"
    Print #1, "Format=Delimited("; Application.International(xlListSeparator); ")"
    Close #1

    sql = "SELECT " _
               & spalten _
               & " FROM " _
               & "`" _
               & selectedFileName _
               & "`"
        
        conn = "ODBC;DBQ=" _
                & filePath _
                & ";DefaultDir=" _
                & filePath _
                & ";Driver={Microsoft Text-Treiber (*.txt; *.csv)};DriverId=27;FIL=text;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UserCommitSync=Yes;"
     
        With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
             .Connection = conn
             .CommandType = xlCmdSql
             .CommandText = sql
             .CreatePivotTable TableDestination:=Range("A8"), TableName:="PivotTable1"
         End With
    Kill filePath & "Schema.ini"
    End Sub


    • Als Antwort markiert Joe_86 Dienstag, 17. September 2013 09:13
    • Bearbeitet CSekulla Sonntag, 22. September 2013 09:16 doppelter \
    Montag, 16. September 2013 07:09
  • Hallo Carsten, hallo Andreas!

    vielen Dank für eure Antworten! :-)

    Ich werde wohl zur Lösung von Carsten greifen. Ich könnt mir grad in den... beißen, dass ich da nicht selber drauf gekommen bin, die Schema.ini zur Laufzeit zu erzeugen.

    Manchmal ist die Lösung einfacher als gedacht.

    Danke euch!

    Gruß

    Joe

    Dienstag, 17. September 2013 09:13