PDA

Vollständige Version anzeigen : Hilfe zum Design und Import einer Konto DB


Silv
03.04.2004, 11:05
Hallo,

ich möchte mir eine Konto DB basteln in die ich dann regelmäßig die export.csv einspeisen kann und dann diverse Auswertungen erstellen kann.
Zum Beispiel Kosten KFZ, Versicherung usw. über Zeiträume, feste Kosten, variable Kosten.
Nun soll im Feld "C" der csv Tabelle die 2. Zeile ja irgendwie einer Kategorie zugeteilt werden (im Bildbeispiel z.B. Jet Tankstelle zu KFZ).
Gibt es da schon was in dieser Form (über die suche habe ich nichts gefunden, vieleicht benutze ich aber auch die falschen Schlagwörter)
Wie könnten die Tabellen aussehen um den fragliche Import zu realisieren?

jmc
03.04.2004, 11:18
Hi Silvia

um die Buchung einer Kategorie zuzuordnen musst du doch irgendwo eine Kontonummer haben. Diese ist in einer Tabelle, in der du dann jeder Kontonummer eine Kategorie zuordnest. Damit kannst du dann mit einer Gruppierungsabfrage das Total pro Kategorie ermitteln.

Silv
03.04.2004, 15:35
Hi,

danke für deine Antwort.
Also:
tblKonto: KontoNr, Name, Ort, PLZ, Strasse (Sparkasse...)
tblKategorie: KategorieID, Name, UKategorie (Versicherung, Privat, KFZ..)
tblUKategorie: UKategorieID, Name, (Haftpflicht, LV, KFZ-Vers...)
tblUmsätze: UmsatzID, KontoNr, Datum, KategorieID, EinAusgang, Währung, SollHaben

So ungefähr?
Ich muß halt dann auch die csv (von oben) reinbringen können.

jmc
03.04.2004, 16:22
Hi Silvia

ach so - du sprichst von einer Bank-Kontonummer ! Ich dachte da eher an eine Finanzbuchhaltungs-Kontonummer bei meinem Beitrag.

Demnach ist die Unter-Kategorie sowas wie die FIBU-Kontonummer und die Kategorie ist eine Gruppierung davon - oder andersrum ??

Bei deiner Darstellung:
tblKategorie: KategorieID, Name, UKategorie (Versicherung, Privat, KFZ..)
tblUKategorie: UKategorieID, Name, (Haftpflicht, LV, KFZ-Vers...)

ist nicht klar, wieso du in der tblKategorie ein Feld UKategorie hast. Die Hierarchie ist nicht klar ...

Was nun das csv-File angeht: wie sieht denn dieses komplett aus, also bezüglich Feldern ? Die müsste ja dann in die tblUmsaetze (Achtung: keine Umlaute in den Namen!!) rein.
Da hast du jetzt wiederum die KategorieID als Fremdschlüssel drin ... unklar - siehe oben.
Diese KategorieID (oder UKategorieID ? ) müsstest du in dem CSV drin haben - ist aber in deinem Screenshot nicht zu sehen. Ist die weiter rechts ?

Engeli
03.04.2004, 17:55
Hallo Sylvia

Würde eventuell als Ausgangsbasis, kannst sie ja Deinen Bedürfnissen anpassen und ausbauen, ein Kassenbuch für den Anfang reichen?
Hier ein schönes Beispiel (Kassenbuch.zip) von Köbi.

http://www.ms-office-forum.net/forum/showthread.php?s=&threadid=113177&highlight=kassenbuch

Gruss Markus

Silv
03.04.2004, 18:56
@JMC
Der Ausschnitt ist bis auf die Überschriften komplett.
Überschriften heißen:
Buchungstag, Valuta, Vorgang/Verwendungszweck, Umsatz.
Die letzte Spalte S oder H hat keine Überschrift.
Im Feld Vorgang/Verwendungszweck sind mehrere Zeilen. Die zweite Zeile ist jeweils die Kategorie oder wie man sie nennen will.


@Engeli
Danke. Werde ich mir anschauen, ob ich dort die gegebene csv Datei rein bringen kann.

jmc
04.04.2004, 10:55
Hi Silvia

das gibt aber eine "Bastlerei" !
Woher kommt denn diese csv-Datei ? Wenn ich es richtig interpretiere, dann kommt sie von der Bank. Da hast du doch keinen Einfluss darauf,wie die Zeile 2 aussieht, das ist jetzt bei den bestehenden Buchungen noch klar, was da steht, aber in Zukunft kann doch irgend ein Text kommen, den du gar nicht kennst ... Wie willst du da eine Automatisierung machen ?

Kannst du mal deine mdb mit den Tabellen und die csv-Datei zippen und hochladen ? Dann versuche ich mal, eine mögliche Lösung zu machen ...

Silv
04.04.2004, 14:19
Hallo jmc,

toll das du mir helfen willst.

Als zip habe ich mal die csv (natürlich mit geänderten Werten) und die db von Köbi (Tip von Engeli) angehängt denn die db ist bis auf ein paar Kleinigkeiten so wie ich sie mir vorgestellt habe. Natürlich möchte ich mir die noch mit grafischen Auswertungen ausbauen nach Kategorie usw nur dazu muß ich ja erst mal die csv rein bringen können.
In dieser db ist das Feld Kategorie vorhanden und das Feld Bemerkung wäre in meiner Vorstellung die UKategorie gewesen.

Anmerkung zur csv:
Komischerweise habe ich wenn ich die Datei über das geöffnete Excel öffne eine schöne Tabellenansicht und wenn ich die csv über den Explorer öffne bekomme ich eine ungeordnete Ansicht mit Kommatas.
Bei einem anderen PC kann ich wiederum öffnen wie ich will und bekomme immer die geordnete Tabelleansicht.

Nouba
04.04.2004, 17:22
Ich habe mir nur die CSV-Datei angesehen. Um die Buchungsdaten in eine 5-spaltige Tabelle zu bekommen, kann nachfolgender Kode in ein allgemeines Modul gestellt werden. Die Tabellenfelder sind als Datum/Uhrzeit, Datum/Uhrzeit, Memo, Währung, Text festzulegen. Falls verschiedene Währungen in Betracht kommen, muß nach der dritten Position noch eine Textspalte für den Währungstext angelegt werden und die Schleife wäre minimal anzupassen.
Option Compare Database
Option Explicit

Private Const mcsCSVIMPORT As String = "Export.csv"

Sub DoIt()

Const csIDENTITY As String = "Buchungstag;Valuta;Vorgang/Verwendungszweck;;Umsatz;"

Dim rs As DAO.Recordset

Dim sPath As String '// unser Dateipfad
Dim sContent As String '// nimmt den Inhalt der CSV-Datei auf
Dim vRows As Variant '// Array, das mit Zeilen der CSV-Datei gefüllt wird
Dim vRecord As Variant '// ein Datensatz-Array
Dim i As Long '// Zählvariablen
Dim j As Variant
Dim k As Integer

'// Pfad zusammensetzen (die Datei sollte im DB-Verzeichnis liegen)
sPath = GetThisDBPath & mcsCSVIMPORT

'// Datei einlesen
sContent = ReadFile(sPath)

'// wenn die Datei nicht leer ist
If Len(sContent) > 0 Then

'// Array vRows mit den Zeilen der Datei füllen
vRows = Split(sContent, vbCrLf)

'// wir schauen nach, ob wir die konstante Zeichenkette csIDENTITY finden
For i = LBound(vRows) To UBound(vRows)
If vRows(i) = csIDENTITY Then
Exit For
End If
Next i

'// i zeigt jetzt auf diese Zeile (falls vorhanden)
Set rs = CurrentDb().OpenRecordset("Umsatz", dbOpenDynaset)

'// solange weitere Zeilen vorhanden sind
Do While i < UBound(vRows)
i = i + 1

'// eine Leerzeile bedeutet, dass nun anderer Inhalt folgt
If Len(vRows(i)) = 0 Then Exit Do

'// Datensatzarray füllen
vRecord = Split(vRows(i), ";")

'// einen neuen DS anlegen
rs.AddNew
'// Feldpositionszeiger auf 0 setzen
k = 0

'// diese Positionen interessieren uns im Array
For Each j In Array(0, 1, 2, 4, 5)
'// wir schreiben unsere Daten und wandeln vorhandene Linefeeds in Windows-Zeilenumbrüche um
rs(k) = Replace(Nz(vRecord(j)), vbLf, vbCrLf)
'// und erhöhen den Feldpositionszeiger
k = k + 1
Next j

'// Daten schreiben
rs.Update
Loop

'// Recordset schließen
rs.Close

'// Speicher freigeben
Set rs = Nothing
End If

End Sub

Function ReadFile(ByRef Path As String) As String

Dim lFileNo As Long

On Error Resume Next

If FileLen(Path) = 0 Then Exit Function
On Error GoTo 0

lFileNo = FreeFile
Open Path For Binary As #lFileNo
ReadFile = Space$(LOF(lFileNo))
Get #lFileNo, , ReadFile
Close #lFileNo

End Function

Function GetThisDBPath() As String

Dim db As DAO.Database

Set db = CurrentDb()
GetThisDBPath = Left$(db.Name, Len(db.Name) - Len(Dir(db.Name)))
Set db = Nothing

End Function

jmc
04.04.2004, 18:38
Hi Silvia

Auch ich habe mir mal die csv-Datei angeschaut. Nouba hat ja schon eine schöne IMport-Routine geschrieben, trotzdem hat's da noch ein paar Grundsatzfragen:

1) es hat Buchungen, die nur aus einer Zeile bestehen, wie soll man da den Text ermitteln .. ?

2) es hat Buchungen, deren zweite Zeile IMHO nichts aussagen (GAA-Bezug, vermutlich die Kartennummer)

Ich habe dir mal die Texte aus der 2. Zeile in ein Feld in der Tabelle "Export3 ex Excel" geschrieben. Schau's dir mal an und sag mir dann, ob du damit wirklich was anfangen kannst ..

Silv
04.04.2004, 20:29
Hallo,

vielen Dank euch zweien für das was Ihr da auf die schnelle auf die Beine gestellt habt...sagenhaft was Ihr drauf habt.

@nouba
Habe dein Modul probiert, funktioniert sehr gut. Wenn ich das aber richtig sehe kann ich ohne Änderung des Modules die Tabelle nicht erweitern.
Bei Angabe eines IDFeldes bekam ich jedenfalls gleich einen Fehler. Als ich das ID-Feld weg gelassen habe hat es funktioniert. Mit dem Manko das ich in dem Memofeld den kompletten Text habe.

@jmc
In Field3 (Kategorie) bräuchte ich nur das erste zuammenhängende Wort (Lastschrift, GAA-Bezug, Uebertrag...).
Zeile2 (Bemerkung) bräuchte ich den Rest was übrig bleibt.

Ist es möglich Field5 in zwei Felder (Einnahmen/Ausgaben im Bezug von S/H) beim Import aufzuteilen oder muß das später über eine Abfrage geschehen?

Deine Zeile2 wäre aber zusätzlich als separates Feld wahrscheinlich gar nicht schlecht um später Auswertungen zu machen, oder was meinst du?

Zu1: Das ist dann die Kategorie ohne Bemerkung
Zu2: Ist OK so (ich kann zwar auch nichts damit anfangen ist ja aber nicht in allen DS so - die meisten sagen ja was aus)

Nouba
04.04.2004, 21:02
Hallo Silvia,

der Import ist zugegebenerweise nicht ganz so flexibel. Um eine ID als Primärschlüssel hinzuzufügen, müßte k bei 1 anfangen zu zählen, falls das Autowertfeld am Anfang steht.

Zum Memo müßtest Du erklären, was denn für Dich relevant ist. Falls Schlagworte vorhanden sind, könnte man auch einen Fremdschlüssel für Schlagworte (Verwendungszwecke) setzen.

Hier ist die geänderte Prozedur für ein Autowertfeld an Position 1. Ausserdem wird nur das erste Wort des Verwendungszwecks ausgewertet. (Ich habe mal die LFs darin in Leerzeichen umgewandelt - das könnte man auch noch abscheiden).
Sub DoIt()

Const csIDENTITY As String = "Buchungstag;Valuta;Vorgang/Verwendungszweck;;Umsatz;"

Dim rs As DAO.Recordset

Dim sPath As String '// unser Dateipfad
Dim sContent As String '// nimmt den Inhalt der CSV-Datei auf
Dim vRows As Variant '// Array, das mit Zeilen der CSV-Datei gefüllt wird
Dim vRecord As Variant '// ein Datensatz-Array
Dim vWords As Variant '// nimmt die Worte der Verwendungszwecke auf
Dim i As Long '// Zählvariablen
Dim j As Variant
Dim k As Integer

'// Pfad zusammensetzen (die Datei sollte im DB-Verzeichnis liegen)
sPath = GetThisDBPath & mcsCSVIMPORT

'// Datei einlesen
sContent = ReadFile(sPath)

'// wenn die Datei nicht leer ist
If Len(sContent) > 0 Then

'// Array vRows mit den Zeilen der Datei füllen
vRows = Split(sContent, vbCrLf)

'// wir schauen nach, ob wir die konstante Zeichenkette csIDENTITY finden
For i = LBound(vRows) To UBound(vRows)
If vRows(i) = csIDENTITY Then
Exit For
End If
Next i

'// i zeigt jetzt auf diese Zeile (falls vorhanden)
Set rs = CurrentDb().OpenRecordset("Umsatz", dbOpenDynaset)

'// solange weitere Zeilen vorhanden sind
Do While i < UBound(vRows)
i = i + 1

'// eine Leerzeile bedeutet, dass nun anderer Inhalt folgt
If Len(vRows(i)) = 0 Then Exit Do

'// Datensatzarray füllen
vRecord = Split(vRows(i), ";")

'// einen neuen DS anlegen
rs.AddNew
'// Feldpositionszeiger auf 0 setzen
k = 1

'// diese Positionen interessieren uns im Array
For Each j In Array(0, 1, 2, 4)
'// wir schreiben unsere Daten
Select Case k
Case 1, 2
rs(k) = vRecord(j)
Case 3
vWords = Split(vRecord(j), " ")
rs(k) = Trim$(Replace(Replace(Nz(vWords(0)), vbLf, " "), Chr$(34), vbNullString))
Case 4
rs(k) = IIf(vRecord(5) = "H", vRecord(j), vRecord(j) * -1)
End Select

'// und erhöhen den Feldpositionszeiger
k = k + 1
Next j

'// Daten schreiben
rs.Update
Loop

'// Recordset schließen
rs.Close

'// Speicher freigeben
Set rs = Nothing
End If
End Sub

Silv
04.04.2004, 21:47
@Nouba

Da bin ich sprachlos in welcher Geschwindigkeit das geht.

Toll wäre das Memo als Textfeld (A) mit nur dem ersten zusammenhängendem Wort (Lastschrift, GAA-Bezug, Uebertrag...)

+ ein weiteres Feld (B) mit dem 2. zusammenhängendem Wort (Tipp24 AG, Allianz Lebensversicherung AG...) - falls Text vorhanden

+ ein weiteres Feld mit nochmal dem Inhalt von (B) und dem Rest drin - ohne (A) (in dem Fall dann als Memo, wobei denke ich ein Textfeld mit 255 ausreicht) - falls Text vorhanden. Dieses Feld könnte ich dann im Formblatt als Infofeld nutzen.

Wenn zudem die jetzt schönen + und - Umsatzwerte noch in 2 Felder geteilt wären (Ausgaben/Einnahmen)....wäre traumhaft.



Dann wäre mal alles schön in einer Tabelle drin mit der ich dann auch Auswertungen fahren kann... so hoffe ich doch ;)

jmc
05.04.2004, 07:07
Hi Silvia

Nouba träumt vermutlich noch seinen wohlverdienten Schlaf aus ...

Deshalb übernehme ich für ihn mal seine Routine und habe sie ein wenig angepasst, damit deine neuesten Wünsche auch noch berücksichtigt sind. Obschon eigentlich der Ansatz von Nouba, den Betrag nur in einer Spalte mit Vorzeichen zu speichern an sich RICHTIG ist !! - Du wirst es merken, wenn es darum geht, den Saldo zu ermitteln ...

Sub DoIt2()

Const csIDENTITY As String = "Buchungstag;Valuta;Vorgang/Verwendungszweck;;Umsatz;"

Dim rs As DAO.Recordset

Dim sPath As String '// unser Dateipfad
Dim sContent As String '// nimmt den Inhalt der CSV-Datei auf
Dim vRows As Variant '// Array, das mit Zeilen der CSV-Datei gefüllt wird
Dim vRecord As Variant '// ein Datensatz-Array
Dim vWords As Variant '// nimmt die Worte der Verwendungszwecke auf
Dim i As Long '// Zählvariablen
Dim j As Variant
Dim k As Integer
Dim z As Integer
Dim sZeilen As String '// restliche Zeilen Buchungstext

'// Pfad zusammensetzen (die Datei sollte im DB-Verzeichnis liegen)
sPath = GetThisDBPath & mcsCSVIMPORT

'// Datei einlesen
sContent = ReadFile(sPath)

'// wenn die Datei nicht leer ist
If Len(sContent) > 0 Then

'// Array vRows mit den Zeilen der Datei füllen
vRows = Split(sContent, vbCrLf)

'// wir schauen nach, ob wir die konstante Zeichenkette csIDENTITY finden
For i = LBound(vRows) To UBound(vRows)
If vRows(i) = csIDENTITY Then
Exit For
End If
Next i

'// i zeigt jetzt auf diese Zeile (falls vorhanden)
Set rs = CurrentDb().OpenRecordset("Umsatz2", dbOpenDynaset)

'// solange weitere Zeilen vorhanden sind
Do While i < UBound(vRows)
i = i + 1

'// eine Leerzeile bedeutet, dass nun anderer Inhalt folgt
If Len(vRows(i)) = 0 Then Exit Do

'// Datensatzarray füllen
vRecord = Split(vRows(i), ";")

'// einen neuen DS anlegen
rs.AddNew
'// Feldpositionszeiger auf 0 setzen
k = 1

'// diese Positionen interessieren uns im Array
For Each j In Array(0, 1, 2, 4)
'// wir schreiben unsere Daten
Select Case k
Case 1, 2
rs(k) = vRecord(j)
Case 3
vRecord(j) = Replace(vRecord(j), Chr$(34), vbNullString)
vWords = Split(vRecord(j), vbLf)
rs("Buchungstext1") = vWords(0)
If UBound(vWords) > 0 Then
rs("Buchungstext2") = vWords(1)
End If
If UBound(vWords) > 1 Then
sZeilen = ""
For z = 2 To UBound(vWords)
sZeilen = sZeilen & vWords(z) & vbCrLf
Next z
rs("BuchungstextRest") = Left(sZeilen, Len(sZeilen) - 2)
End If
Case 4
' nur für Schweiz ;)
'vRecord(j) = Replace(vRecord(j), ".", "")
'vRecord(j) = Replace(vRecord(j), ",", ".")
If vRecord(5) = "H" Then
rs("Betrag_Haben") = vRecord(j)
Else
rs("Betrag_Soll") = vRecord(j)
End If

End Select

'// und erhöhen den Feldpositionszeiger
k = k + 1
Next j

'// Daten schreiben
rs.Update
Loop

'// Recordset schließen
rs.Close

'// Speicher freigeben
Set rs = Nothing
End If
End Sub
Der Tabelleentwurf - Umsatz2 ! - sieht dann so aus:
.

Silv
05.04.2004, 20:55
Hallo jmc und Nouba,
besten Dank euch beiden für die spitzen Hilfe.

@jmc
Danke für deinen Tip das ich wahrscheinlich Probleme mit dem Saldo bekomme. Ich werde es mal so probieren und wenn ich es nicht hin bekomme dann tausche ich dein Case4 im Modul gegen noubas aus.

Mit Eurer Hilfe stehen mir ja jetzt alle Möglichkeiten offen. :)
Nochmal Danke.