excel tabelle in access datenmodell normalisiert importieren

[Mordred]

Hallo zusammen!
Ich bin über google auf Euch gestossen und habe auch schon die sufu genutzt, aber nur ähnliche Themen gefunden, welche aber mein Kernproblem nicht direkt behandeln.

Ich habe eine Excel-Tabelle mit ca. 20 Spalten. Dazu habe ich in Access ein Datenmodell mit entsprechenden Tabellen und Beziehungen entwickelt.
Nun mein Problem:
Wie bekomme ich die Daten konsistent in die Tabellen?
Ich habe dazu gelesen:

1.) Excel-Tabelle in Access importieren
2.) In die importiere Access-Tabelle einen Autowert einfügen
(Dadurch ist jeder Datensatz erst einmal einheitlich gekennzeichnet)
3.) Daten in die einzelnen Tabellen verteilen (anhand des eindeutigen Schlüssel)
4.) Eventuell doppelte Datensätze in den einzelnen Tabellen eliminieren (denn sonst macht das mit der Aufteilung in die mehreren Tabellen keinen Sinn)
5.) Primär- und Fremdschlüssel bei den einzelnen Tabellen festlegen
6.) Referentielle Integrität festlegen (sofern gewünscht)
7.) Den bei 2) eingefügen Wert in allen Tabellen löschen.

Aber das ergibt für mich irgendwie keinen Sinn, denn:
1. Ich habe ja schon Beziehungen PK, FK etc.
2. Selbst wenn ich 1. lösche, wie bekomme ich es dann hin, dass die Daten so zusammenhängen, wie sie es in Excel tun?

Vielen Dank und Schöne Grüße
Michael

[Wodka43]

Hallo na und herzlich Willkommen im Forum,

die Tabelle die du so (wie in deinem Beispiel) importierst dient nur als temporäre Tabelle.
Um die Daten aus dieser in deine richtige Tabelle zu transferieren würde ich dir Recordsets empfehlen.

D.h: du (oder besser vba  ) öffnet die tempTabelle und übergibt entsprechend deiner Programmierung die Daten an die jeweils richtigen Tabellen.

Dafür solltest du dich entweder ein wenig mit Recordsets beschäftigen oder gibst uns hier mehr Informationen zu deinem Datenmodell und dem Satzaufbau. Und am besten lädst du deine bisherige mdb und die Exceldatei (reicht auch mit testdaten) hier hoch.
Dann können wir dir viel leichter unterstützend unterstützend unter die Arme greifen 

mfg
Leo

[MzKlMu]

Hallo,
warum Recordsets?
Ich würde alle Access Tabellen im Vorfeld anlegen, mit den Beziehungen. Eventuelle auch zusätzliche (zusammengesetzte) eindeutige Indizes um Doppelungen zu vermeiden.
Dann mit einfachen Anfügeabfragen die Daten in die richtige Tabelle schieben. Und schneller wäre es auch als Recordsets, falls es auf die Geschwindigkeit ankommt.

[Mordred]

erstmal vielen dank für die schnellen antworten
ich habe die excel-tabelle und den beziehungsbericht der db angehangen. bei bedarf kann ich auch die db komplett hochladen.

würde denn folgendes mit euren ideen (recordsets/vba, anfüge-abfragen) möglich sein?
es geht mir ja darum, dass die daten vor allem zusammenhängend in der db liegen. also im endeffekt so, dass ich zb durch eine abfrage über alle tabellen die daten nachher so angezeigt bekomme, wie ihr sie in der excel-tabelle sehen könnt.
wenn ich die tabellen "einfach" via anfüge-abfrage mit den jeweils relevanten spalten befülle, ist mir nicht klar, wie ich obiges realisieren können sollte. ich habe auch schon mal mit dem tabellen-analyse-assistent versucht, das hinzukriegen. der bastelt dann 15 tabellen, was noch nicht so schlimm wäre, denn immerhin kann man dann ja die felder zwischen den tabellen noch hin und her schieben und somit in etwa das angehängte datenmodell realisieren. nur verknüpft er die tabellen auch noch über nachschlagefelder. und soweit ich weiss, soll man das ja gerade vermeiden? ich las auch schon davon, dass man nachschalgefelder im nachhinein irgendwie zu textfeldern vom datentyp long/autowert umwandeln kann?

ein 2tes Problem, welches im zweifelsfall nicht so wichtig ist bzw. dann hat der chef pech gehabt:
gibt es tatsächlich eine möglichkeit, obiges weitestgehend in zukunft zu automatisieren? also wenn ich die nächste tabelle bekomme, dass ich sie dann einfach via anfügeabfrage so one-cklick-mäßig importiere?

wiederum vielen dank für eure tipps
michael

[Anhang gelöscht durch Administrator]

[Wurliwurm]

Hallo Michael,

der Entwurf sieht gut aus auf den ersten Blick, genauer habe ich mich nicht damit beschaftigt, weil ich ja auch die Anforderungen nicht kenne. Wenn Du das richtig normalisiert hast, kannst Du es auch verlustfrei genauso wieder zusammenbauen. Ich hoffe, du hast eindeutige Indexe bei den zusammengesetzten Keys, sonst läufst Du Gefahr, uneindeutige Daten zu bekommen.

Nachschlagefelder sind nur eine Hilfe beim Eingeben. Über Nachschlagefelder wird nicht verknüpft, man kann auch Nachschlagefelder anlegen, ohne eine Verknüpfung zu haben.

Was die Verteilung auf die Tabellen betrifft, ob Du es mit Recordsets machst oder mit Anfügeabfragen machen sollst. Es gibt einen kleinen Performacevorteil, wenn du direkt mit INSERT arbeitest, aber dann kannst Du nicht vorher prüfen, ob der Eintrag schon existiert. Ich würde es mit Recordsets machen.

Du mußt für jede Tabelle eine Projektionsabfrage auf die Quelltabelle machen und in einer Schleife dann in die normalisierte Tabelle einfügen.

Beispielsweise (ein generisches Beispiel)

Dim rs as dao.recordset
Dim sql as string

Dim rs1 as dao.recordset

sql = "SELECT DISTINCT Feld1, Feld2 FROM Quelltabelle"
set rs = currentdb.openrecordset(sql)

do until rs.eof

       sql = "SELECT Feld1, Feld2 FROM Zieltabelle"
       set rs1 = currentdb.openrecordset(sql)

       if rs1.bof = true and rs1.eof = true then  'Noch kein Eintrag
          rs1.adnnew
            rs1!feld1 = rs!feld1
            rs1!feld2 = rs!feld2
         rs1.update
       end if

       rs1.close
      set rs1 = nothing

  rs.movenext
loop

rs.close
set rs = nothing

Du solltest für jede Tabelle eine Sub-Routine haben und auch eine Gesamtroutine, wo du die einzelnen Routinen der reihe nach aufrufst. Dann kannst du die Verteilung mit einem Aufruf der Zentralroutine über eine Formularbutton aufrufen und bräuchtest nur den einen Knopf drücken. Wenn Du die Routinen als Funktionen realisierst und als Rückgabewert den Wert der geschrieben Datensätze zurückgibst, kannst Du eine informative Msgbox-Rückmeldung in der Gesamtroutine einbauen.

Hope this helps
Johannes

[Mordred]

Hallo Michael,

der Entwurf sieht gut aus auf den ersten Blick, genauer habe ich mich nicht damit beschaftigt, weil ich ja auch die Anforderungen nicht kenne. Wenn Du das richtig normalisiert hast, kannst Du es auch verlustfrei genauso wieder zusammenbauen. Ich hoffe, du hast eindeutige Indexe bei den zusammengesetzten Keys, sonst läufst Du Gefahr, uneindeutige Daten zu bekommen.

Nachschlagefelder sind nur eine Hilfe beim Eingeben. Über Nachschlagefelder wird nicht verknüpft, man kann auch Nachschlagefelder anlegen, ohne eine Verknüpfung zu haben.

Was die Verteilung auf die Tabellen betrifft, ob Du es mit Recordsets machst oder mit Anfügeabfragen machen sollst. Es gibt einen kleinen Performacevorteil, wenn du direkt mit INSERT arbeitest, aber dann kannst Du nicht vorher prüfen, ob der Eintrag schon existiert. Ich würde es mit Recordsets machen.

Du mußt für jede Tabelle eine Projektionsabfrage auf die Quelltabelle machen und in einer Schleife dann in die normalisierte Tabelle einfügen.

Beispielsweise (ein generisches Beispiel)

Dim rs as dao.recordset
Dim sql as string

Dim rs1 as dao.recordset

sql = "SELECT DISTINCT Feld1, Feld2 FROM Quelltabelle"
set rs = currentdb.openrecordset(sql)

do until rs.eof

       sql = "SELECT Feld1, Feld2 FROM Zieltabelle"
       set rs1 = currentdb.openrecordset(sql)

       if rs1.bof = true and rs1.eof = true then  'Noch kein Eintrag
          rs1.adnnew
            rs1!feld1 = rs!feld1
            rs1!feld2 = rs!feld2
         rs1.update
       end if

       rs1.close
      set rs1 = nothing

  rs.movenext
loop

rs.close
set rs = nothing

Du solltest für jede Tabelle eine Sub-Routine haben und auch eine Gesamtroutine, wo du die einzelnen Routinen der reihe nach aufrufst. Dann kannst du die Verteilung mit einem Aufruf der Zentralroutine über eine Formularbutton aufrufen und bräuchtest nur den einen Knopf drücken. Wenn Du die Routinen als Funktionen realisierst und als Rückgabewert den Wert der geschrieben Datensätze zurückgibst, kannst Du eine informative Msgbox-Rückmeldung in der Gesamtroutine einbauen.

Hope this helps
Johannes

hallo johannes. erstmal danke für die ausführliche anleitung.
besonders die idee, dass ganze in routinen abzubilden und dann per click auf nen formular-button immer wiederholen zu können ist für mich interessant, weil wohl doch öters nen import anstehen könnte.
auch danke für die erklärung zu den nachschlagefeldern. ich dachte bisher immer, die wären in tabellen generell "böse".
was ich an deinem code noch nciht verstehe (bin in vba noch relativer noobie, kann aber grundsätzlich von anderen sprachen her mit schleifen etc. was anfangen):
1. ich sehe nirgendwo eine kontrolle, die bedingt, dass die daten redundanzfrei in den tabellen landen.
2. mir ist immer noch nicht klar, was bei diesem vorgehen dazu führt, dass die datensätze zusammenhängend in die db kommen.

also am ende will ich es ja so haben, dass ich z.b. ne abfrage ala
"select * from tblPruefung inner join tblPruefposition on PruefungID=pruefungFK" machen kann.
wenn ich die jetzt aber "einfach so" importiere, klappt das doch nciht?
oder habe ich egrade ein brett vorm kopf?

[Wolfang]

Hallo zusammen!
Ich habe mich gerade erst hier registriert und per Suche dieses (leider schon etwas ältere) Thema gefunden, was auch genau mein aktuelles Problem beschreibt...

Ich habe ein Datenmodell mit ca. 10 Tabellen und diese per Beziehungen verknüpft. Die meisten Tabellen sind soweit mit den zukünftig erforderlichen Daten erfüllt, meine Maske in Form mehrere Formulare wird auch langsam fertig und man kann auch schon Berichte erzeugen...für die ein oder andere Sache habe ich mich bereits auch schon etwas mit VBA beschäftigen müssen...nun komme ich an einer Stelle aber nicht weiter: dem konsistenten "Massenimport" von Daten in mein Datenmodell bzw. die Verknüpfung mit den entsprechenden Tabellen.

Wirklich "importiert" werden sollen hier im Prinzip nur noch neue Daten, die sich auf zwei Tabellen verteilen. Dabei ist eine Haupttabelle mit fast sämtlichen Fremdschlüsselattributen der anderen Tabellen versehen, bspw. für Kunden, etc. Ich stelle mir das also so vor, dass ich bspw. 100 Datensätze aus Excel importiere in eine temporäre Tabelle (habe ich derzeit auch schon per VBA gemacht und das dann wie in diesem Thread beschrieben in ein Recordset gepackt). Anschließend will ich dann Auftragsnummern in eine Tabelle schreiben und nur noch die Fremdschlüssel in die Tabelle schreiben, die die Verknüpfung mit den anderen Daten (wie Kunden, Ort, etc.) umsetzen, damit ich dies später per Abfrage auch komplett als ein Tupel zusammenhängend auswerten kann. Vorher, also bevor neue Auftragsnummern gespeichert werden, soll aber noch überprüft werden, ob nicht die Auftragsnummer schon in der Tabelle ist...Ich will also im Prinzip viele Datensätze konsistent in mein Datenmodell kriegen und frage mich wie ich das genau umsetzen soll...der vorletzte Beitrag beschreibt ja nur den einfachen Import wenn die Zieltabelle leer ist und schreibt dann die Daten aus der Quelltabelle dort hinein per Schleife...

Hoffe das war verständlich und jm kann mir helfen!!!

Gruß,
Wolfgang

[DF6GL]

Hallo Wolfgang,


Wenn aus einer einzigen (Excel-Tabelle) die Daten in eine (relationale) Access-Tabellen-Struktur importiert werden sollen, dann kann (unter der Voraussetzung , dass im Excel-Sheet die entspr. Schlüsselfelder auch richtig vorhanden sind)  das am einfachsten mit mehreren Anfügeabfragen erledigt werden, wobei immer zunächst die 1-Tabellen und danach erst die n-Tabellen zu füllen sind.

Probleme kann es geben, wenn in den Access-Tabellen Autowerte als Schlüssel benutzt werden. Dann wird es eher einfacher sein, alle Tabellen entspr. vorstehenden  Prinzip  zu füllen.

[ebs17]

Erarbeite Dir mal diese Lösung: Importtabelle in m:n-Beziehung auflösen.

Hier wird mit einer Anfügeabfrage pro Tabelle gearbeitet, was deutlich zügiger als Recordsetschleifen laufen sollte. In den Abfragen ist jeweils eine Inkonsistenzprüfung enthalten, so dass jeweils nur neue Datensätze angefügt werden können, auch bei einem "versehentlichen" mehrfachen Import. In der dritten Abfrage (auf die Verknüpfungstabelle) ist die Inkonsistenzprüfung nicht enthalten. Hier wird das Fremdschlüsselpaar zuzüglich eines Zeitstempels eingetragen. Das ist natürlich je nach eigenem Datenmodell abänderbar.

Für die Exceltabelle genügt übrigens ein Verlinken (per Hand oder per TransferSpreadsheet acLink). Eine Temp-Tabelle ist nur "Müll", der dann wieder entsorgt werden muss. Eine Temp-Tabelle (als Datenbanktabelle) hätte dann einen Vorteil, wenn man Felder indizieren und diese Indizierung nachfolgend nutzen würde. Dies aber erfolgt meistens nicht, dafür "helfen" Temp-Tabellen beim ungewünschten Aufblähen der Datenbank.

Eine mögliche Technik des Ver- und Entlinkens der Exceltabelle ist im Importcode der beschriebenen Demo kommentiert.

MfGA
ebs

[Wolfang]

Guten Morgen,

danke für eure Antworten!

@DF6GL: Ja es ist so, dass ich eine Excel Tabelle habe mit den zu importierenden Daten. Mein Datenmodell sieht nun konkret so aus, dass ich eine Tabelle in Access habe die schlichtweg die tatsächlich nur neuen Datensätze aus Excel bekommt (ca. 5 Attribute pro Datensatz/Tupel) - die ganzen anderen Daten aus der Excel-Datei entsprechen im Prinzip den Bestandsdaten in meiner Access-DB, die auf andere Tabellen verteilt sind und mit der "Haupttabelle" (die die neuen DS enthält) per FK-Beziehnung verknüpft sind.

D.h. ich will die die Excel Liste Zeile für Zeile durchgehen, die neuen Datensätze in meine Tabelle in Access schreiben (vorher prüfen das Schlüssel nicht schon enthalten ist) und für die anderen Werte aus der Excel Tabelle muss ich ja im Prinzip überprüfen ob der Datensatz in einer der anderen Tabellen im Datenmodell schon vorhanden ist und wenn ja, dann in meiner Haupttabelle dafür den Fremdschlüssel abspeichern für den aktuellen Datensatz. Also im Prinzip eine Haupttabelle die fast ausschließlich neue Daten bekommen wird, aber mit den anderen Tabellen verknüpft sein muss, damit ich über Auswertungen auch den zusammenhängenden Datensatz über alle Tabelle abfragen kann...

Wie kriege ich das hin?

Ergänzung: Ich musste leider auf künstliche Schlüssel bzw. Autowerte zurückgreifen...

[DF6GL]

Hallo,

indem Du Dir das Link-Beispiel aus dem Beitrag von ebs ansiehst....

[Wolfang]

Ok, werde mich mal daran versuchen