[GELÖST] Umsatzdatei einer Bank importieren

[Ich Bins]

Hallo zusammen,

bei meinem Umstieg vor über einem Jahr von Excel zu Calc erhielt ich hier im Forum tatkräftige Unterstützung von mikele und balu bei der Anpassung eines Makros zum Importieren von Umsatzdaten.csv einer Bank.

Das Thema war damals
viewtopic.php?f=12&t=19090

Schon lange ärgere ich mich über die Umsatzdaten.csv einer anderen Bank. Das Feld Vorgang/Verwendungszweck enthält ein Sammelsurium von Daten z.B.

LASTSCHRIFT
254-258987456/21.11.19 BEIT
RAG VOM 01.12.19 BIS 01.06.
20 MDREFNR: 548-985647R00-M
-1 EREF: IGS-B-23154879 MRE
F: 258-965214R00-M-1 CRED:
DE1234567891234567 IBAN: DE
12345678912345678912 BIC: W
ELADEDD ABWA: Versicherungs
- Aktiengesellschaft

Zugegeben ein extremes Beispiel.

Im Feld Vorgang/Verwendungszweck befindet sich außer in der ersten Zeile nach jedem 27. Zeichen ein Zeilenumbruch. Dadurch werden die Informationen wie z.B. BIC und IBAN verstümmelt.

Leider liefert der Datenexport keine getrennten Felder für IBAN und BIC.

Meine Versuche diese Informationen aus dem Feld Vorgang/Verwendungszweck zu gewinnen scheiterten bisher.

Anbei befindet sich die Datei Kontoauszug.ods mit dem Import-Makro und der Tabelle an die ich neue Umsatzdaten anfügen möchte.
Anbei ist ebenfalls einen anonymisierte Importdatei. Leider ging beim Anonymisieren die Struktur des Feldes Vorgang/Verwendungszweck flöten. Der Datei musste ich die Endung rtf anhängen weil csv und txt nicht hochzuladen waren. Bitte vor dem Öffnen aus rtf ein csv machen.

Ich werde mich bemühen eine fehlerfreie Importdatei nachzuliefern.

Hat hier jemand eine Idee wie man die beiden Informationen IBAN und BIC in die dafür vorgesehen Felder der Zieltabelle übertragen kann.

Viele Grüße
Ich Bins

[mikele]

Hallo,
häng einfach an die Umsaetze_DE12345678912345678912_2019.12.06.csv die Endung .zip. Dann kannst du sie als Ich Umsaetze_DE12345678912345678912_2019.12.06.csv.zip hochladen.

[mikele]

Hallo,
ich entnehme deinem Post zwischen den Zeilen zwei Wünsche:
1) Zeilenumbrüche entfernen
2) IBAN und BIC herauslesen
Richtig?

[mikele]

Hallo,
das folgende Makro liest aus der Spalte I (Zeile 14 bis 26) die IBAN und BIC heraus (wenn vorhanden) und zeigt die Ergebnisse in einer Msgbox an.

Code: Alles auswählen

Sub Main
	oDoc=ThisComponent
	oTab=oDoc.Sheets(0)
	for i=13 to 25
		sText=oTab.getCellByPosition(8,i).string
		sText=join(split(sText,chr(10)),"")
		posIBAN=instr(sText,"IBAN:")
		if posIBAN>0 then
			sIBAN=left(ltrim(mid(sText,posIBAN+5)),12)
		end if		
		posBIC=instr(sText,"BIC:")
		if posBIC>0 then
			sBIC=left(ltrim(mid(sText,posBIC+4)),8)
		end if		
		sAusgabe=sAusgabe & chr(10) & "IBAN: " & sIBAN & "      BIC: " & sBIC 
	next
	msgbox sAusgabe
End Sub

[Ich Bins]

Hallo mikele,

vielen Dank schon mal für Deine erneute Unterstützung.
Es wäre schön, wenn nach dem Import die Zeilenumbrüche im Ziel-Feld Verwendungszweck nicht mehr vorhanden wären.

Dein Makro liest fast schon korrekt in der Original-Datei die IBAN und die BIC, nur die Länge ist zu kurz.

Code: Alles auswählen

sIBAN=left(ltrim(mid(sText,posIBAN+5)),22)

Code: Alles auswählen

sBIC=left(ltrim(mid(sText,posBIC+4)),11)

Mit der Änderung der Länge war das kein Problem.
Ein Problem für mich ist jedoch das Einfügen von IBAN und BIC in die richtigen Felder jeder Zeile.

Ein weiterer Punkt zur Optimierung wäre das zusätzliche separieren des Eintrages in der ersten Zeile (z.B. Lastschrift oder DA-GUTSCHRIFT) des Feldes Vorgang/Verwendungszweck. Als Zielfeld würde sich das Feld Flag eignen.

Viele Grüße
Ich Bins

[mikele]

Hallo,
ja klar, die IBAN ist 22-stellig. Die BIC ist leider 8- bis 11-stellig. Daher wird das Makro nicht immer korrekt arbeiten. Ich bin mir noch nicht sicher, wie man es sicher abgrenzen kann. In deinem Beispiel finden sich 8- und 11-stellige BIC. Manchmal direkt am Ende, manchmal mit folgendem Leerzeichen, manchmal auch mit folgendem Zeilenumbruch. Diese Fälle müssen beachtet werden.

Ein Problem für mich ist jedoch das Einfügen von IBAN und BIC in die richtigen Felder jeder Zeile.

Ein weiterer Punkt zur Optimierung wäre das zusätzliche separieren des Eintrages in der ersten Zeile (z.B. Lastschrift oder DA-GUTSCHRIFT) des Feldes Vorgang/Verwendungszweck. Als Zielfeld würde sich das Feld Flag eignen.

Dazu müsste klar sein, wie dein aktuelles Makro aussieht und welche Felder der csv in welche Zellen (Spalten) sollen.

[Ich Bins]

Hallo mikele,

anbei ist die Zieldatei, die eigentlich Deine, nur von mir angepasste Makro enthalten sollte.
Das Makro BBB macht bei mir bereits die gesamte Vorarbeit.

Kotoauszug.ods

(16.38 KiB) 183-mal heruntergeladen

Bei Transaktionen die von der Hausbank getätigt wurden, wie z.B. Gewinnsparen kommt es vor, dass die Felder Konto-Nr. und BLZ Inhalte haben. Beim Strukturieren der csv nenne ich Konto-Nr. = IBAN/Kontonummer und BLZ = BIC/BLZ. In diese beiden Felder sollen dann IBAN und BIC eingefügt werden. Wenn die komplette csv strukturiert ist soll das Ergebnis in die Zieldatei eine Zeile unter den letzten Datensatz in Spalte C eingefügt werden.

Die BIC ist für mich nur "nice to have", also nicht wirklich erforderlich.

Falls das Makro BBB in der Datei Kontoauszug.ods nicht enthalten sein sollte habe ich es nachfolgend noch eingefügt.

Sub BBB() Dim oKontoDoc as Object, oIm ... e) End Sub

Gruß
Ich Bins

[mikele]

Hallo,
in der Datei ist kein Makro enthalten (auch wenn sie so tut). Offensichtlich hat die Datei ursprünglich oder immer wieder mal Kontakt zu Microsoft excel. Warum?
Das Makro ist nicht zu lesen. Füge den Makrocode (markieren, kopieren) zwischen die Code-Tags (5. Symbol über dem Eingabefeld: </>) ein.

[Ich Bins]

Hallo mikele,

die Datei hat mit Excel nur durch das Öffnen der csv-Dateien Kontakt.
Auf meinem PC ist Excel installiert, aber mit der Datei habe ich, soweit ich mich erinnere, keine Experimente mit Excel gemacht.
Weshalb der Code nicht im Beitrag ist verstehe ich nicht. Ich hatte ihn über </> eingefügt.
Auf ein neues.

Code: Alles auswählen

Sub BBB()

Dim oKontoDoc as Object, oImportDoc as Object, oExtCSVBlatt as Object

	'Kontoauszug.ods als Objekt in Variable schreiben
	oKontoDoc = ThisComponent
	'Dateiauswahl-Dialog erzeugen
	oFilePicker = createUnoService("com.sun.star.ui.dialogs.FilePicker")
	oFilePicker.setMultiSelectionMode(False)
	'Filter für die Dateiauswahl initialisieren
	oFilePicker.appendFilter("Alle Dateien", "*.*") 	
	oFilePicker.appendFilter("csv - Dateien", "*.csv")
	'Filter für cvs-Dateien aktivieren
	oFilePicker.setCurrentFilter("csv - Dateien")
	'Dialog aufrufen und auswerten
	if oFilePicker.execute()=1 then 'wenn nicht abgebrochen
		'Auslesen der gewählten Datei
		oFiles= oFilePicker.getFiles() 'url der datei
		'Setzen der Importoptionen
		sFileURL=oFiles(0)
		Dim Args(2) as New com.sun.star.beans.PropertyValue
		args(1).Name = "FilterName"
		args(1).Value = "Text - txt - csv (StarCalc)"
		args(2).Name = "FilterOptions"
		args(2).Value = "59,34,0,1,2/4/3/4,0,false,true,true"
		'Öffnen des Dokuments
	    oImportDoc = StarDesktop.loadComponentFromURL(sFileURL,"_blank", 0, Args())
	end if
	varArbeitsblatt = split(oImportDoc.Title,"_")(1)
'	Zugriff auf das 1. Tabellenblatt
	oExtCSVBlatt = oImportDoc.Sheets(0)
'   Erste Zeilen löschen: Index und Anzahl
    myrows=oExtCSVBlatt.getrows
    myrows.removebyindex(0,12)
'   letzte Zelle des Bereiches
	ocursor = oExtCSVBlatt.createCursor()
	ocursor.gotoStart()	
	ocursor.gotoEndofUsedArea(false)			
'   index letzte Spalte des Bereichs
'    varLetzteSpalte = ocursor.getRangeAddress.endColumn
'   index letzte Zeile des Bereichs
	varLetzteZeile = ocursor.getRangeAddress.endRow
'   letzt drei Zeilen löschen	  
    myrows.removebyindex(varLetzteZeile - 2,varLetzteZeile)
	oExtCSVSpalte=oExtCSVBlatt.getColumns
	'Quell- und Zielspalten für Verschiebung
	aQuelle=array("D","A","C","L","M","E","G","I","B")
    aZiel=array("N","O","P","Q","R","S","T","V","W")
	'Kopieren
	for i=0 to ubound(aQuelle)
		oQuelleRange=oExtCSVSpalte.getByName(aQuelle(i))
		oQuellRangeAddresse = oQuelleRange.getRangeAddress
		oZiel = oExtCSVSpalte.getByName(aZiel(i)).getCellByPosition(0,0)
		oZielCellAdresse=oZiel.getCellAddress
		oExtCSVBlatt.copyRange(oZielCellAdresse,oQuellRangeAddresse)
	next
	'Spalten leeren
	oExtCSVSpalte.getByName("A").ClearContents(1023)
	oExtCSVSpalte.getByName("B").ClearContents(1023)
	oExtCSVSpalte.getByName("C").ClearContents(1023)
'   Spalten löschen
    oExtCSVSpalte.removeByIndex(3,1)
    oExtCSVSpalte.removeByIndex(3,1)
    oExtCSVSpalte.removeByIndex(3,1)
    oExtCSVSpalte.removeByIndex(3,1)
    oExtCSVSpalte.removeByIndex(3,1)
    oExtCSVSpalte.removeByIndex(3,1)
    oExtCSVSpalte.removeByIndex(3,1)
    oExtCSVSpalte.removeByIndex(3,1)
    oExtCSVSpalte.removeByIndex(3,1)
    oExtCSVSpalte.removeByIndex(3,1)
	'neue Spaltenüberschriften
	oExtCSVBlatt.getCellByPosition(0,0).string="ID"
	oExtCSVBlatt.getCellByPosition(1,0).string="Flag"
	oExtCSVBlatt.getCellByPosition(2,0).string="Vorgang"
	oExtCSVBlatt.getCellByPosition(3,0).string="Buchungstext"
	oExtCSVBlatt.getCellByPosition(5,0).string="Kontoinhaber"
	oExtCSVBlatt.getCellByPosition(6,0).string="Betrag"
	oExtCSVBlatt.getCellByPosition(7,0).string="S_H"
	oExtCSVBlatt.getCellByPosition(8,0).string="IBAN/Kontonummer"
	oExtCSVBlatt.getCellByPosition(9,0).string="BIC/BLZ"
	oExtCSVBlatt.getCellByPosition(10,0).string="Saldo"
	oExtCSVBlatt.getCellByPosition(11,0).string="Verwendungszweck"
	oExtCSVBlatt.getCellByPosition(12,0).string="Valutadatum"	
'   Spaltenüberschrift fett formatieren
	oRange = oExtCSVBlatt.getCellRangeByName("A1:M1")
  	oRange.CharWeight = com.sun.star.awt.FontWeight.BOLD
'   Spaltenbreite optimieren
	For iSl = 0 to 10
		oExtCSVBlatt.Columns(iSl).OptimalWidth = True
	next iSl
    oExtCSVBlatt.Columns(11).Width = 4000
	oExtCSVBlatt.Columns(12).OptimalWidth = True	
'   Reihenfolge vertauschen
	oCellCursor = oExtCSVBlatt.createCursor
    oCellCursor.GotoEndOfUsedArea(False)
    oBereich=oExtCSVBlatt.getCellRangeByPosition(0,1,oCellCursor.getRangeAddress().endColumn,oCellCursor.getRangeAddress().endRow)
    'Zellinhalt in Array lesen
    aDaten()=oBereich.getDataArray
    'Zeilen- und Spaltenanzahl merken
    zeilen=ubound(aDaten())
    spalten=ubound(aDaten(0))
    'Zeilen vertauschen
    for i=0 to zeilen/2
		tmp=aDaten(zeilen-i)
		aDaten(zeilen-i)=aDaten(i)
		aDaten(i)=tmp
    next
'   ab hier stehen die aufbereiteten Daten zur Verfügung
'   letzte nicht leere Zeile in der Kontodatei ermitteln
	oZiel = oKontoDoc.Sheets().getByName(varArbeitsblatt)
	ocursor = oZiel.createCursor()
	ocursor.gotoEndofUsedArea(false)
	varEinfuegezeile = ocursor.getRangeAddress.endRow + 2
'   Bereich zum Einfügen festlegen
'	Der Bereich muss dieselbe Größe haben, wie der Ursprungsbereich!
    oZielBereich = oZiel.getCellRangeByPosition(0,varEinfuegezeile,0+spalten,varEinfuegezeile+zeilen)
'   die nächste Zeile fügt die gedrehten Daten in die csv-Datei ein   
	oZielBereich.setDataArray(aDaten)
'   csv-Datei schließen
	oImportDoc.close(false)
End Sub

[mikele]

Hallo,
wenn ich den Code richtig lese, willst du die ursprüngliche csv-Datei eigentlich gar nicht umstricken, sondern "nur" Daten in anderer Reihenfolge in deine kontostand.ods einfügen. Richtig?