Office: (Office 2016) Prüfung der Datenbankeinträge auf zulässige Werte unter Verwendung von Auswahllisten

Guten Morgen,

ich soll eine Datenbank mit ca. 2,5 Mio. Zustandsbeschreibungen für Maschinenbauteile auf unzulässige Werte prüfen und komme gerade nicht so recht weiter. Vielleicht könnt Ihr mir helfen?

Die Zustandsbeschreibungen setzt sich aus 7 von einander abhägigen Werten zusammen. Die ersten 5 Werte bestehen aus jeweils einem Buchstaben. Der 6 Wert besteht aus mehreren aphanumerischen Werten. In der Regel sind es 2 Buchstaben oder eine mehrstellige Zahl. Häufig kommt aber auch ein Strich ("-") vor. Die Zahlen können Maßangabe oder Beschreibungen sein. Ist der 6 Wert eine Maßangabe, enthält der 7. Wert die Längen- oder Flächeneinheit. Ist der 6 Wert keine Zahl oder keine Maßangaben enthält der 7.Wert einen Strich "-".
In der Datenbank gibt es pro Zustandsbeschreibung eine Datenzeile. In jeder Datenzeile ist für jeden Wert eine Spalte des Typs Text vorhanden. Hier ein Beispiel für die Daten:
Code:

Zur Prüfung der Datenbankeinträge habe ich eine csv-Datei mit mehreren hundert Einträgen erhalten. In dieser Datei finde ich für jede der 7 Spalten die zulässigen Werte und in der 8. Spalte eine Beschreibung. Sind in einem Datenfeld mehrere Werte zulässig, ist eine Auswahlliste vorhanden. Die Datei sieh wie folgt aus:
Code:

Die brachiale Methode wäre für jede mögliche Kombination eine Zeile in der csv-Datei zu erstellen, in Access zu importieren und mit den vorhandenen Zustandsbeschreibungen in der Datenbank abzugleichen. Auf Grund der Vielzahl der Einträge ist es ganz schön aufwendig und fehleranfällig, von der Wartbarkeit ganz zu schweigen.

Gibt es eine Möglichkeit die Auswahllisten direkt zu nutzen? Ich habe schon eine ganze weite gesucht aber noch nichts passenden gefunden.

Schon einmal vielen Dank für das Mitdenken!

Joe

:)

 

Relativ direkt: Ja.
Aus solch einer Zeile kann man ein Pattern (Muster) für reguläre Ausdrücke erstellen. Bezüglich Wartbarkeit: RegEx versteht auch nicht jeder, da hohe Abstraktion auf den Punkt.
Vor allem aber: Mehrere hundert RegEx-Prüfungen auf jeweils 2,5 Mio Datensätze - da darf man es nicht eilig haben.

Brachial finde ich gut: Man müsste einen Weg finden, einfach und vor allem schnell die Einzelkombinationen zu erstellen (automatisiert), in eine Datenbanktabelle zu schreiben und zu indizieren.

Deine zwei Zeilen ergeben auch schon 120 + 90 Kombinationen, das ergibt hoch gerechnet auch ganz schnell 100k und mehr.
Aber der Vergleich per Abfrage wäre dann übersichtlich und keineswegs fehleranfällig.

Man sollte in seine Überlegungen auch einbeziehen, was genau das für eine Datenbank ist, denn sie und ihr SQL-Dialekt sind durchaus wesentlich für Maßnahmen.

 

Ich habe im ersten Augenblick auch an RegEx gedacht.
Da aber jeder Wert in einer Spalte gespeichert ist, würden ich den Vergleich mittels like mal testen.

Ich hätte nach dem ersten Zeichen gefiltert und die restlichen Spalten vergleichen.

Beispiel:
B; T; (A,B,E,F,M,-); (O,R,U,L,-); (R,A,E,G); - ; - ;

Filtern nach B
Ergebnis = Spalte2 = 'T' and Spalte3 like '[A,B,E,F,M,-]' usw.

Die CSV einfach einlesen oder verknüpfen.
In einer Schleife jede Zeile abarbeiten und die Bedingung für den Vergleich per VBA erstellen.

LG Markus

Edit:
Wenn die Daten nicht in einer Access Datei gespeichert sind - natürlich passthrogh oder ADO verwenden.

 

Hallo,

... oder man kreiert die Where Klausel dynamisch für die Spalten und bei Auswahlmöglichkeit wird die IN-Klausel verwendet.

Je nach DBS geht auch so etwas:

Ulrich

 

Statt mit Like könntest du natürlich auch die einzelnen Vergleichswerte in eine Tabelle schreiben. Jeden Buchstaben in eine Zeile.
Dann geht das ganze auch unter Nutzung eines Index mit einer einzigen Abfrage ohne zusätzliche Funktion.

In jede Vergleichstabelle den Buchstaben der ersten Spalte mit allen möglichen Kombinationen

Beispiel:
B; T; (A,B,E,F,M,-); (O,R,U,L,-); (R,A,E,G); - ; - ;

Spalte 2
B;T

Spalte 3
B;A
B;B
B;E
B;F
B;M
B;-

 

Ich würde es so machen:

(
PatternLines müssen aus der CSV-Datei kommen.
CheckRecords sind das Recordset
Columns ist dann Fields aus dem Record
)

Code:

 

Vielen Dank für die Rückmeldungen.

@ebs17, markusxy:
Die Verwendung von regulären Ausdrücken wäre auch meine Vorzugslösung. Nur leider ist mir, ohne die Installation von irgendwelchen dubiosen Erweiterungen für Access - Installationen sind hier verboten - keine Möglichkeit für die Nutzung von regulären Ausdrücken bekannt.

Solange keine Nutzerinteraktionen erforderlich sind, sollte Zeit nicht das große Problem sein. Schließlich langweilt oder verschläft mein Rechner sowieso die längste Zeit des Tages. ;-)

Unter Verwendung eines awk- oder sed-Skripts sollte die Erstellung der Einzelkombinationen automatisch und relativ geschmeidig möglich sein.

@knobbi38:
Meines Wissens dürfte die Lösung mit WHERE und in bei Access-SQL nicht funktionieren.

@ebs17, #5:
das verstehe ich nicht so recht. Ich sollte mal eine keine Testdatenbank bauen...


Weiterhin werde ich mal schauen, wie groß der aufwand für die "brachiale Methode" ist.

Nochmals vielen Dank fürs Mitdenken Joe

 

Eine für VBA und damit auch Jet-SQL verwendbare Version ist ohne weitere Installation bereits verfügbar, allerdings mit eingeschränkten Möglichkeiten gegenüber anderen Sprachen.
Siehe auch Codebeispiel - "Intelligente" Textanalyse

 

Was fragst du denn überhaupt, wenn dir die Laufzeit eh wurscht ist und du dir die anderen Vorschläge wie es scheint nicht testen willst.

 

Hallo Joe,

dann solltest du dein Wissen aber mal ein bißchen auffrischen, denn den IN-Operator gibt es schon immer, nur eben nicht die verketteten INs, aber das hatte ich auch geschrieben.

Ulrich

 

Eine andere Möglichkeit wäre die CSV-Prüfdatei in Access zu importieren und etwas aufzubereiten (per Suchen/Ersetzen oder per Aktualisierungsabfrage):

1) In den Spalten 3, 4, 5 die Kommas entfernen und die runden Klammern durch eckige Klammern ersetzen, und
2) in Spalte 6 die runden Klammern entfernen, und
3) in den Spalten 3, 4, 5 muss der Bindestrich immer am Ende der Buchstabenliste stehen, damit der Like-Vergleich wie erwartet funktioniert!

Die Prüftabelle laut Beispieldaten von Post #1 sollte schließlich so aussehen:

Code:

Nachfolgende Auswahlabfrage (Beispiel-SQL-Code) würde dann die Datensätze anzeigen, welche nicht mit der Prüftabelle übereinstimmen (Die Bezeichnungen müssen natürlich an die tatsächlichen Gegebenheiten angepasst werden. Und, je nach Datenherkunft, eventuell statt Sternchen (*) das Prozentzeichen (%) verwenden):

Code:

Bei den in Post #1 gezeigten Beispiel-Daten würde dann folgendes Ergebnis erscheinen:

Code:

Eventuell bei einer kleineren Datenmenge (z. B. 10.000) mit gesetzten Indexen in den Prüftabellenfeldern testen, wie lange die Abfrage dauert für anschließende Abschätzung bei 2,5 Mio. Datensätzen.

 

\@readonly,
Was lässt dich annehmen, dass da ein Index genutzt werden kann?

Außerdem solltest du dir like etwas genauer ansehen:
Ich habs ja schon in #3 gezeigt.
?"B" like "[APC]"
?"P" like "[APC]"



@joe3768
Grundsätzlich ist dein "Radikalvorschlag" aus Sicht der Performance ja optimal.

Du musst nur einen Index über alle Felder erstellen.
Ich würde überlegen, die Tabelle mit allen Möglichkeiten künftig als Basis für das Anlegen von neuen Datensätzen zu verwenden, dann gibts auch keine falschen Einträge mehr. Dem Bauteil einfach nur noch einen Eintrag aus der Tabelle zuweisen, statt bei jedem Produkt einzeln die Buchstaben einzufügen.

 

Bei den Spalten 1, 2, 7 wird ein vorhandener Index genutzt (=).

----

?"B" like "[APC]"
Falsch
?"P" like "[APC]"
Wahr

Die Ergebnisse sind so, wie erwartet. Was ist daran ungewöhnlich?

 

Eine Demo-DB mit 10k DS + die csv-Datei wäre gut, dann könnt man mal die Performance testen.

 

Bei allen Versuchen würde ich vorher überprüfen, ob die Beschreibung über die Einzelbuchstaben die Realität trifft oder nur modelliert wurde.

Die Antwort auf die Frage, in welcher DB denn die Tabelle mit den 2,5 Mio Einträgen liegt, scheint auch uninteressant zu sein.