I work currently on the import of Excel files.
I have a folder with multiple Excel files.
Each file corresponds to a complete data set.
Each file contains multiple worksheets.
In the first worksheet to find general information about the file:
ID
product name
...
In the following worksheets are tables with further information. For example, the following columns:
Measured value, min, max, unit
Step control parameters, duration, time unit
...
I managed to read the data from each worksheet. However, the data are not linked.
My idea was to value 'ID' of the first worksheet to assign the tables when reading. Unfortunately, I find the manual and in the Forum only examples, in which all participating tables already have a key field. There is no way to edit the Excel spreadsheet.
Example:
SET filename = [\\ path \ to \ file \ * Computers * 3.xlsx];
LOAD
[Name of the device] as name,
Approval number as ID
FROM $ (filename)
(OOXML, embedded labels, table is General,
filters (
Remove (Row, RowCnd (CellValue, 2, StrCnd (null)))
Remove (Row, RowCnd (CellValue, 3, StrCnd (null)))
Transpose ()
Remove (Row, RowCnd (CellValue, 1, StrCnd (null)))
Remove (Row, RowCnd (CellValue, 2, StrCnd (null)))
)
);
Protocol:
LOAD
Art as [protokoll_art]
Min as [protokoll_min]
Max as [protokoll_max]
Unit as [protokoll_einheit]
FROM $ (filename)
(OOXML, embedded labels, header is 4 lines, table is [log 1v3]);
IS:
LOAD
Art as [ist_art]
Min as [ist_min]
Max as [ist_max]
Unit as [ist_einheit]
FROM $ (filename)
(OOXML, embedded labels, header is 4 lines, table is [log 2v3]);
SHOULD:
LOAD
Art as [soll_art]
Min as [soll_min]
Max as [soll_max]
Unit as [soll_einheit]
FROM $ (filename)
(OOXML, embedded labels, header is 4 lines, table is [log 3v3]);
Suppliers:
LOAD
Name as [lieferant_name]
Road as [lieferant_strasse]
HNr as [lieferant_hnr]
Zip as [lieferant_PLZ]
Place as [lieferant_ort]
FROM $ (filename)
(OOXML, embedded labels, header is 4 lines, table is [suppliers]);
- - - -
Ich bearbeite aktuell den Import von Excel-Dateien.
Ich habe einen Ordner mit mehreren Excel-Dateien.
Jede Datei entspricht einem vollständigen Datensatz.
Jede Datei enthält mehrere Arbeitsblätter.
Im ersten Arbeitsblatt finden sich allgemeine Informationen zur Datei:
In den darauf folgenden Arbeitsblättern befinden sich Tabellen mit weiteren Informationen. Z. B. folgende Spalten:
- Messwert, Min, Max, Einheit
- Schritt, Kontrollparameter, Dauer, Zeiteinheit
- ...
Mir ist es gelungen die Daten aus jedem Arbeitsblatt auszulesen. Allerdings sind die Daten nicht miteinander verknüpft.
Meine Idee war es den Wert 'ID' aus dem ersten Arbeitsblatt beim Einlesen den Tabellen zuzuweisen. Leider finde ich im Handbuch und im Forum nur Beispiele, bei dem alle beteiligten Tabellen bereits ein Schlüsselfeld besitzen. Es gibt keine Möglichkeit die Excel-Tabelle zu bearbeiten.
Beispiel:
SET filename = [\\path\to\file\*Geraete*3.xlsx];
LOAD
[Bezeichnung des Gerätes] as Bezeichnung,
Zulassungsnummer as ID
FROM $(filename)
(ooxml, embedded labels, table is Allgemein,
filters(
Remove(Row, RowCnd(CellValue, 2, StrCnd(null))),
Remove(Row, RowCnd(CellValue, 3, StrCnd(null))),
Transpose(),
Remove(Row, RowCnd(CellValue, 1, StrCnd(null))),
Remove(Row, RowCnd(CellValue, 2, StrCnd(null)))
)
);
Protokoll:
LOAD
Art as [protokoll_art],
Min as [protokoll_min],
Max as [protokoll_max],
Einheit as [protokoll_einheit]
FROM $(filename)
(ooxml, embedded labels, header is 4 lines, table is [Protokoll 1v3]);
IST:
LOAD
Art as [ist_art],
Min as [ist_min],
Max as [ist_max],
Einheit as [ist_einheit]
FROM $(filename)
(ooxml, embedded labels, header is 4 lines, table is [Protokoll 2v3]);
SOLL:
LOAD
Art as [soll_art],
Min as [soll_min],
Max as [soll_max],
Einheit as [soll_einheit]
FROM $(filename)
(ooxml, embedded labels, header is 4 lines, table is [Protokoll 3v3]);
Lieferanten:
LOAD
Name as [lieferant_name],
Straße as [lieferant_strasse],
HNr as [lieferant_hnr],
PLZ as [lieferant_PLZ],
Ort as [lieferant_ort]
FROM $(filename)
(ooxml, embedded labels, header is 4 lines, table is [Lieferanten]);
Message was edited by Community Moderator to include English translation as a courtesy.
