Office: csv Import Tabelle mit viel Inhalt in einer Zelle

Hallo,

ich habe bei mir auf Arbeit ein sehr altes Informationssystem in dem wir unser Aktenarchiv organisiert haben.
In diesem Archivsystem sind Informationen enthalten wie der zeitliche Umfang der Akte, wer diese geführt hat und den Inhalt der Akte.
Diese Informationen kann ich als csv exportieren. Allerdings ist der Inhalt der Spalte Akteninhalt teilweise sehr umfangreich gefüllt und sehr unterschiedlich geschrieben. Im Screenshot kann man sich ein Bild davon machen wie es aussieht. Es handelt sich um ca. 6000 Einträge.
Ich möchte nun die Informationen in eine Exceltabelle überführen. Wenn ich den Export ohne den Akteninhalt mache klappt das super die csv in Excel zu importieren.
Die csv mit Akteninhalt ist eine reine Katastrophe. Alles verschiebt sich und nichts kann mehr zugeordnet werden. Die Inhalte manuell zu übernehmen ist auf Grund des Umfangs eher auch keine Option.
Ich habe die Möglichkeit zu steuern welche Spalten exportiert werden, jedoch müssen es immer mindestens zwei sein.
Hat Jemand mit solch schwierigen Exporten bereits zu tun gehabt und kann mir helfen die Datei ordentlich aufzubereiten?
Ich habe mal ein paar Einträge dieses Systems in ein Parallelsystem gepackt und verfremdet, damit man sich ein Bild davon machen kann und auch die entsprechenden Dummy-Exporte mit und ohne Akteninhalt sowie das Ergebnis in der Exceldatei mit angehangen.

 

Hallo Alexander,

da sehe ich recht schwarz. Ohne viel Handarbeit und Prüfung gegen das Original geht da nichts...

Als ersten Schritt würde ich vermutlich die Spalte B Filtern und die leeren Zellen ausblenden. Dann hast Du nur Zugangsnummern und Texte, die dort nicht hingehören. Diese Texte gehören in die darüberliegende Zeile ab Spalte H. Also manuell rüberschieben.
Dann würde ich in Spalte B nur die leeren einblenden. Hier die texte aus Spalte A in Spalte G schieben.

Dann sollte es grob passen. Aber wie oben gesagt, du musst natürlich gegen das Original prüfen.

Ein anderer deutlich besserer Weg wäre, den Entwickler des Informationssystems zu bitten, den Datenexport in ordentlicher Form einzurichten *Klugscheißermodus aus*.

Gruß,
Lutz

 

Moin

Das Problem ist aus meiner Sicht dass die fraglichen Daten in der falschen Zeile und in der falschen Spalte stehen.
Damit hat man keine Chance eine automatische Zuordnung zu erreichen.

 

Hallo,

ich habe die Datei "Musterarchiv_komplet.txt" mit der "screenshot-archivsystem.jpg" verglichen und folgende Regelmäßigkeit gefunden:

1) Öffne die Textdatei
2) Lies aus der Textdatei die 1. Textzeile bis zum 1. CRLF in eine Textvariable ein. Zerlege diese mit dem ";" als Trennzeichen, um die Spaltenüberschriften und die Anzahl der Spalten (=AnzSp) zu erhalten im Excel-Arbeitsblatt.
3) Gehe in Excel an den Anfang der nächsten Zeile, setze Spaltenzähler auf 1.
4) Teste, ob das nächste Zeichen in der Textdatei ein CRLF ist:
4a) Falls nicht, lies aus der Textdatei bis zum nächsten ";" (egal ob da CRLFs enthalten sind oder nicht) und gib diesen String in die aktuelle Excelzelle.
4b) Gehe in die nächste Excel-Spalte (=Erhöhe Spaltenzähler um 1).
5) Überprüfe, ob der Spaltenzähler > AnzSp ist: falls ja, gehe nach 3), falls nein, gehe nach 4)
6) Falls in 4) das EOF erreicht ist, beende das Lesen und schließe die Textdatei.

Voraussetzung, damit dies funktioniert, ist, dass zwischen den ";" als Feldtrennzeichen keine weiteren ";" innerhalb des eigentlichen Textes enthalten sind. In der übermittelten Textdatei ist diese Bedingung erfüllt.

 

Hallo,

zur Beschreibung im letzten Posting folgt jetzt der VBA-Code.

Dazu habe ich dir in einem allgemeinen Codemodul die SUB "Einlesen_CSV_Txt" eingefügt. Diese SUB enthält eine Pfad-Variable und eine Datei-Variable, die du unbedingt an deine Verhältnisse anpassen musst, damit das Makro deine Export-Textdatei finden kann. Diese Stellen habe ich deutlich gekennzeichnet.
Das Trennzeichen ist der Strichpunkt.

Code:

Option Explicit
Dim TxtZeile As String

Sub Einlesen_CSV_Txt()
  Dim fs As Object          'New FileSystemObject
  Dim Txt As Object         'TextStream
  Dim Pfad As String, Datei As String
  Dim FeldBez() As String, Tr As String
  Dim Ws As Worksheet
  Dim Zl As Long, Sp As Long, AnzSp As Long
  Const ForReading As Long = 1
 
  Pfad = "S:\Dokumente\OfficeH\"                          '<-- Pfad zur Textdatei
  Datei = "Alexander2401_Musterarchiv_komplet.txt"        '<-- Textdateiname
  Tr = ";"                                                '<-- Trennzeichen in der Textdatei
 
  Set fs = CreateObject("Scripting.FileSystemObject")
  Set Txt = fs.OpenTextFile(Filename:=Pfad & Datei, IOMode:=ForReading)
  On Error GoTo End_Of_Proc
 
  Set Ws = ActiveSheet                              '<-- Import ins aktive Arbeitsblatt
  Ws.Cells.NumberFormat = "@"                       '<-- alle Zellen als Text formatieren
 
  TxtZeile = Txt.ReadLine
  FeldBez = Split(TxtZeile, Tr): AnzSp = UBound(FeldBez, 1) + 1
  Zl = 1
  For Sp = 1 To AnzSp
      Ws.Cells(Zl, Sp) = FeldBez(Sp - 1)
  Next Sp
 
  TxtZeile = ""
  Do
      Zl = Zl + 1: Sp = 1
      Do
         Ws.Cells(Zl, Sp).Value = GetNxtText(Txt, ";", Sp = AnzSp)
         Sp = Sp + 1
      Loop While Sp <= AnzSp
  Loop
End_Of_Proc:
  Txt.Close
  Set Txt = Nothing
  Set fs = Nothing
End Sub

Function GetNxtText(Txt As TextStream, Tr As String, EOL As Boolean) As String
  Dim Ps As Long
  Do
    If EOL Then TxtZeile = Replace(TxtZeile, vbNewLine, Tr, , 1)
    Ps = InStr(TxtZeile, Tr)
    If Ps Then
       GetNxtText = Left(TxtZeile, Ps - 1): TxtZeile = Mid$(TxtZeile, Ps + 1)
       Exit Function
    Else
       TxtZeile = TxtZeile & Txt.ReadLine & vbNewLine
    End If
  Loop
End Function

Aufgerufen wird das Makro durch die Tastenkombination Alt+F8 und anschließendem Ausführen der SUB "Einlesen_CSV_Txt".
Der Import nach Excel erfolgt jeweils im aktiven Arbeitsblatt, wobei alle Zellen als Text formatiert werden.
Weiters ist in deiner Datei jede Menge Kommentar zum leichteren Verständnis enthalten.

 

Hallo,

danke erstmal für das schnelle und hilfreiche Feedback.

Das Script funktioniert wunderbar. In den Originalen Datensätzen stecken aber leider vereinzelt noch ; drin.

Das werde ich jetzt händisch anpassen müssen und dann das Script nochmal drüber laufen lassen.

Eine andere Möglichkeit wäre noch den Export mit Tabstopp als Trennzeichen zu machen. Das habe ich auch getestet, aber das Script hat dann keine Spalten mehr erzeugt als ich das Trennzeichen ";" durch einen Tabstopp ersetzt habe. Scheinbar interpretiert Excel den Tabstopp dann nicht.

Trotzdem schon mal vielen lieben Dank, das hilft mir schon sehr viel weiter.

Grüße

Alexander

 

Hallo Alexander,

im VBA-Makro musst du die Code-Zeile, in der das Trennzeichen eingestellt wird, ändern.
Dieses ist bisher auf das Semikolon-Zeichen ";" eingestellt in der Code-Zeile:

Code:

Tr = ";"                          '<-- Trennzeichen in der Textdatei

Diese Code-Zeile musst du umstellen auf die VBA-Konstante vbTab:

Code:

Tr = vbTab                         '<-- Trennzeichen in der Textdatei

wobei dem vbTab-Zeichen im ASCII/ANSI-Code der Codewert ASC(vbTab)=9 entspricht.

 

Hallo Anton,

danke für die nochmalige schnelle Reaktion. Die Tabstopps sind noch verbreiteter in der Datei als die Semikolons. Mehr Exportmöglichkeiten habe ich aber auch nicht.

Ich denke manuell die Semikolons aus den Einträgen suchen und entfernen wird der einzige Weg sein. Dann läuft dein Script auch perfekt und ich kann mit der fertigen Datei was anfangen. Auf jeden Fall wird es schneller sein als alles händisch zu übertragen.

Vielen Dank dir.