OLE DB Verbindungen zu Excel: Unterschied zwischen den Versionen
Aus Asprova Hilfe
GK (Diskussion | Beiträge) K (→Spaltenköpfe: Formatierung) |
GK (Diskussion | Beiträge) K (→Erweiterte Eigenschaften) |
||
| (13 dazwischenliegende Versionen von 2 Benutzern werden nicht angezeigt) | |||
| Zeile 5: | Zeile 5: | ||
==Arbeitsschritte== | ==Arbeitsschritte== | ||
| − | Die Verwendung der Option OLE-DB für Datenimport- und -exporteinstellungen ermöglicht | + | Die Verwendung der Option OLE-DB für Datenimport- und -exporteinstellungen ermöglicht zum Datenaustausch mit Excel. |
| − | + | ===Datenquelle=== | |
| − | + | Setzen Sie in den Schnittstelleneinstellungen für das Schnittstellenobjekt "OLE DB" als [Datenquelle]. | |
| − | + | ||
| − | + | ===Provider=== | |
| + | Wählen Sie im über [Verzeichnis/Datenbank] geöffneten Dialogfenster "Microsoft Office ''xx''.0 Access Database Engine OLE DB Provider" aus und klicken dann auf [Weiter >>].<br>[[Image:dbio-excel01.jpg]] | ||
| + | |||
| + | ===Dateipfad=== | ||
| + | Datenquelle: Geben Sie nun Pfad und Dateinamen der Excel-Datei ein, zu der Sie eine Verbindung erstellen möchten.<br>[[Image:dbio-excel02.jpg]] | ||
| + | |||
| + | ===Erweiterte Eigenschaften=== | ||
| + | Setzen Sie nun unter der Registerkarte [Alle] die Version der Excel-Datei im Feld [Extended Properties]. | ||
{| | {| | ||
|- | |- | ||
| Zeile 39: | Zeile 46: | ||
|} | |} | ||
|- | |- | ||
| − | |}< | + | |}<br>So wie die Excel-Version können Sie auch weitere Attribute im Feld [Extended Properties] eingeben: |
| − | |||
{| class ="wikitable" | {| class ="wikitable" | ||
|- | |- | ||
!Wert!!Bedeutung | !Wert!!Bedeutung | ||
|- | |- | ||
| − | |HDR||Definiert ob die erste Zeile Spaltenköpfe oder Daten enthält | + | |HDR||Definiert ob die erste Zeile Spaltenköpfe oder Daten enthält. |
|- | |- | ||
| − | | Yes|| | + | | Yes||Daten der erste Reihe, die im Excel-Tabellenblatt Werte enthält, werden als Spaltenköpfe interpretiert.<br/>Leeren Zellen in dieser Zeile werden automatisch generierte Feldnamen "F1", "F2", etc. zugewiesen. |
|- | |- | ||
| − | | Nein||Feldnamen werden automatisch erstellt, beginnend mit "F1", "F2" etc. | + | | Nein||Feldnamen werden automatisch erstellt, beginnend mit "F1", "F2", etc. |
|- | |- | ||
| − | |MAXSCANROWS||Bestimmt den Datentyp der Daten in | + | |valign="top"|MAXSCANROWS||width="300"|Bestimmt automatisch den Datentyp der Daten in Excel. Legen Sie die Anzahl der zu prüfenden Zeilen (von 1 - 16) fest. Standardmäßig werden die ersten acht Zeichen für die Bestimmung betrachtet. Nachdem alle Zeilen geprüft wurden, wird der jeweils häufigste Datentyp ausgewählt.<br>Ist <code>MAXSCANROWS</code> nicht definiert, wird [TypeGuessRows] des Registry-Schlüssels <code>HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel</code> verwendet, um die Anzahl der zu prüfenden Zeilen zu bestimmen. Der Standardwert beträgt hier 8. Wird 0 eingegeben, werden alle Zeilen überprüft. |
|- | |- | ||
|} | |} | ||
| − | Für eine Excel 2016 Datei mit Spaltenköpfen würde der folgende String als Wert für [Extended Properties] genutzt:<br> Excel 12.0;HDR=Yes</ | + | Für eine Excel 2016 Datei mit Spaltenköpfen würde der folgende String als Wert für [Extended Properties] genutzt:<br> <code>Excel 12.0;HDR=Yes</code> |
| + | |||
| + | [[Image:Excel07.png|frame]] | ||
| − | + | ===Externe Tabelle=== | |
| − | + | Wurden diese Einstellungen vorgenommen und eine Verbindung zu Excel hergestellt, so kann das zu verwendende Tabellenblatt auf die herkömmliche Weise in der Eigenschaft [Externer Tabellenname] des DBIO-Objekts ausgewählt werden. Neben Tabellenblättern können auch benannte Zellbereiche als externe Tabelle dienen. Tabellenblattnamen enthalten ein Dollarzeichen am Ende ihres Namens (z.B. "Tabelle1$"), Zellbereiche aber nicht.<br />Innerhalb eines Tabellenblattes können Verbindungen zu mehreren benannten Zellbereichen hergestellt werden. Zellbereiche werden komplett importiert, selbst wenn keine Daten enthalten sind. Es kann also sein, dass leere Zeilen importiert werden. Um dies zu vermeiden, kann <code>FValid(ME.Code)</code> als [Externe Importfilterformel] gesetzt werden. | |
| − | + | ||
| − | + | ===Weitere Einstellungen=== | |
| − | + | Die weiteren Einstellungen ([Field-Mapping] etc.) entsprechen denen anderer OLE DB-Verbindungen. | |
==Spaltenköpfe== | ==Spaltenköpfe== | ||
| − | Abhängig von [Extended Properties] können in Excel-Dateien Spaltenköpfe verwendet werden. | + | Abhängig von [Extended Properties], können in Excel-Dateien Spaltenköpfe verwendet werden. |
| − | + | ===Mit Spaltenköpfen=== | |
| − | + | <code>HDR=Yes</code> - In der Excel-Datei werden Spaltenköpfe erwartet. Die erste Zeile mit Daten wird als Spaltenköpfe interpretiert. | |
{| | {| | ||
|- | |- | ||
| Zeile 77: | Zeile 85: | ||
|} | |} | ||
| − | Leeren Zellen in der Spaltenkopfzeile werden | + | Leeren Zellen in der Spaltenkopfzeile werden automatisch generierte Feldnamen "F1", "F2", etc. zugewiesen. |
{| | {| | ||
| Zeile 89: | Zeile 97: | ||
|} | |} | ||
| − | + | ===Ohne Spaltenköpfe=== | |
| − | + | <code>HDR=No</code>- In der Excel-Datei werden keine Spaltenköpfe erwartet. Die erste Spalte mit Daten wird als das erste Datenfeld interpretiert, bis zur letzten Spalte mit darin enthaltenen Daten. Feldnamen werden automatisch erstellt, beginnend mit "F1", "F2", etc. | |
| − | |||
{| | {| | ||
|- | |- | ||
| Zeile 102: | Zeile 109: | ||
|} | |} | ||
| − | ==Einschränkungen== | + | == Einschränkungen == |
| − | + | * Da die SQL-Befehle <code>DELETE</code> (Datensatz löschen) und <code>DROP</code> (Tabelle löschen) nicht unterstützt werden, wird der Wert der Eigenschaft [Tabelle vor Export leeren] ignoriert. | |
| − | < | + | * Die SQL-Befehle <code>COMMIT</code> und <code>ROLLBACK</code> werden nicht unterstützt. |
| − | + | * Während eines Exports, bei dem Daten hinzugefügt werden, werden die neuen Daten immer ans Ende von bestehenden Daten gesetzt. Dabei können Fehler auftreten, wenn durch die hinzugefügten Zeilen die maximale Anzahl an Zeilen in Excel überschritten wird. | |
| − | |||
| − | |||
{| class="wikitable" | {| class="wikitable" | ||
|- | |- | ||
| − | !Version der | + | !Version der Excel-Datei!!Maximale Zeilenanzahl!!Maximale Spaltenanzahl |
|- | |- | ||
| − | |Excel 2007 und neuer||1.048.576||16.384 | + | |Excel 2007 und neuer||align="right"|1.048.576||align="right"|16.384 |
|- | |- | ||
| − | |Excel 97-2003||65.535||256 | + | |Excel 97-2003||align="right"|65.535||align="right"|256 |
|- | |- | ||
| − | |Excel 95 und älter||16.384||256 | + | |Excel 95 und älter||align="right"|16.384||align="right"|256 |
|- | |- | ||
|} | |} | ||
| − | + | :Ist ein Name zu lang, so tritt ebenso ein Fehler auf. | |
| − | + | * Die Daten werden automatisch untersucht, um den Datentyp des jeweiligen Feldes zu bestimmen. Normalerweise werden hierfür die ersten acht Zeilen betrachtet und der häufigste Datentyp ausgewählt. | |
| − | + | ** Unter [Erweitere Eigenschaften] können Sie einen Eintrag "MAXSCANROWS" hinzufügen und einen Wert von 1 bis 16 für die Anzahl der zu betrachtenden Zeilen setzen. | |
| − | + | ** Ansonsten wir der Wert von "TypeGuessRows" des Registrierungsschlüssels <code>HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel</code> genutzt. Der Standardwert ist hier 8, setzen können Sie Werte zwischen 1 und 16. Wenn Sie 0 setzen, werden alle Zeilen betrachtet. | |
| − | + | ** Gibt es unterschiedliche Datentypen in einem Feld, so können Sie unter [Erweitere Eigenschaften] können Sie einen Eintrag "IMAEX=1 hinzufügen. Dies erzwingt die Nutzung von "ImportMixedTypes=Text" des Registrierungsschlüssels <code>HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel</code>. Allerdings kann es dabei zu unerwünschten Ergebnissen kommen. | |
| − | + | * Bei Verbindungen zu Excel werden Passwörter nicht unterstützt. | |
| + | * Sie können Verbindungen zu Arbeitsmappen oder benannten Bereichen erstellen. | ||
| + | ** Auf den Namen von Arbeitsmappen folgt das Dollarzeichen ("$"). | ||
| + | ** Sie können Verbindungen zu mehreren benanntnen Bereichen der gleichen Arbeitsmappe erstellen. | ||
| + | ** Bei benannten Bereichen werden immer der gesamte Bereich importiert, auch wenn dort keine Daten enthalten sind. Um den Import leerer Zeilen zu verhindern, setzen Sie die [Interne Importfilterformel] auf <code>FValid(ME.Code)</code>.<br>Moreover, because of the tail information of the output rows, so far, is available in Excel, this information is exported so that the rows are added. | ||
| − | + | == Verbindungen zu XLSX-Dateien == | |
| − | + | * Nutzen Sie den OLE DB Provider "Microsoft Office 12.0 Access Databese Engine OLE DB Provider". | |
| − | + | * Der entsprechende Registrierungsschlüssel ist <code>HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel</code>. | |
| − | |||
| − | |||
<hr> | <hr> | ||
<div align="right">Hilfe Nummer: 757700</div> | <div align="right">Hilfe Nummer: 757700</div> | ||
Aktuelle Version vom 3. März 2021, 11:44 Uhr
Hilfe Nummer: 757700
Inhaltsverzeichnis
Arbeitsschritte
Die Verwendung der Option OLE-DB für Datenimport- und -exporteinstellungen ermöglicht zum Datenaustausch mit Excel.
Datenquelle
Setzen Sie in den Schnittstelleneinstellungen für das Schnittstellenobjekt "OLE DB" als [Datenquelle].
Provider
Wählen Sie im über [Verzeichnis/Datenbank] geöffneten Dialogfenster "Microsoft Office xx.0 Access Database Engine OLE DB Provider" aus und klicken dann auf [Weiter >>].
Dateipfad
Datenquelle: Geben Sie nun Pfad und Dateinamen der Excel-Datei ein, zu der Sie eine Verbindung erstellen möchten.
Erweiterte Eigenschaften
Setzen Sie nun unter der Registerkarte [Alle] die Version der Excel-Datei im Feld [Extended Properties].
|
|
So wie die Excel-Version können Sie auch weitere Attribute im Feld [Extended Properties] eingeben:
| Wert | Bedeutung |
|---|---|
| HDR | Definiert ob die erste Zeile Spaltenköpfe oder Daten enthält. |
| Yes | Daten der erste Reihe, die im Excel-Tabellenblatt Werte enthält, werden als Spaltenköpfe interpretiert. Leeren Zellen in dieser Zeile werden automatisch generierte Feldnamen "F1", "F2", etc. zugewiesen. |
| Nein | Feldnamen werden automatisch erstellt, beginnend mit "F1", "F2", etc. |
| MAXSCANROWS | Bestimmt automatisch den Datentyp der Daten in Excel. Legen Sie die Anzahl der zu prüfenden Zeilen (von 1 - 16) fest. Standardmäßig werden die ersten acht Zeichen für die Bestimmung betrachtet. Nachdem alle Zeilen geprüft wurden, wird der jeweils häufigste Datentyp ausgewählt. Ist MAXSCANROWS nicht definiert, wird [TypeGuessRows] des Registry-Schlüssels HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel verwendet, um die Anzahl der zu prüfenden Zeilen zu bestimmen. Der Standardwert beträgt hier 8. Wird 0 eingegeben, werden alle Zeilen überprüft.
|
Für eine Excel 2016 Datei mit Spaltenköpfen würde der folgende String als Wert für [Extended Properties] genutzt:
Excel 12.0;HDR=Yes
Externe Tabelle
Wurden diese Einstellungen vorgenommen und eine Verbindung zu Excel hergestellt, so kann das zu verwendende Tabellenblatt auf die herkömmliche Weise in der Eigenschaft [Externer Tabellenname] des DBIO-Objekts ausgewählt werden. Neben Tabellenblättern können auch benannte Zellbereiche als externe Tabelle dienen. Tabellenblattnamen enthalten ein Dollarzeichen am Ende ihres Namens (z.B. "Tabelle1$"), Zellbereiche aber nicht.
Innerhalb eines Tabellenblattes können Verbindungen zu mehreren benannten Zellbereichen hergestellt werden. Zellbereiche werden komplett importiert, selbst wenn keine Daten enthalten sind. Es kann also sein, dass leere Zeilen importiert werden. Um dies zu vermeiden, kann FValid(ME.Code) als [Externe Importfilterformel] gesetzt werden.
Weitere Einstellungen
Die weiteren Einstellungen ([Field-Mapping] etc.) entsprechen denen anderer OLE DB-Verbindungen.
Spaltenköpfe
Abhängig von [Extended Properties], können in Excel-Dateien Spaltenköpfe verwendet werden.
Mit Spaltenköpfen
HDR=Yes - In der Excel-Datei werden Spaltenköpfe erwartet. Die erste Zeile mit Daten wird als Spaltenköpfe interpretiert.
| Excel-Datei | [Field-Mapping] |
|---|---|
 |
|
| 1. Leere Zeilen werden übersprungen 2. Diese Zeile wird für die Spaltenköpfe genutzt |
Leeren Zellen in der Spaltenkopfzeile werden automatisch generierte Feldnamen "F1", "F2", etc. zugewiesen.
| Excel-Datei | [Field-Mapping] |
|---|---|
 |
|
| Feldname für leere Zelle wird automatisch gesetzt |
Ohne Spaltenköpfe
HDR=No- In der Excel-Datei werden keine Spaltenköpfe erwartet. Die erste Spalte mit Daten wird als das erste Datenfeld interpretiert, bis zur letzten Spalte mit darin enthaltenen Daten. Feldnamen werden automatisch erstellt, beginnend mit "F1", "F2", etc.
| Excel-Datei | [Field-Mapping] |
|---|---|
| |
| Da keine Spaltenköpfe erwartet werden, werden Feldnamen automatisch erstellt. |
Einschränkungen
- Da die SQL-Befehle
DELETE(Datensatz löschen) undDROP(Tabelle löschen) nicht unterstützt werden, wird der Wert der Eigenschaft [Tabelle vor Export leeren] ignoriert. - Die SQL-Befehle
COMMITundROLLBACKwerden nicht unterstützt. - Während eines Exports, bei dem Daten hinzugefügt werden, werden die neuen Daten immer ans Ende von bestehenden Daten gesetzt. Dabei können Fehler auftreten, wenn durch die hinzugefügten Zeilen die maximale Anzahl an Zeilen in Excel überschritten wird.
| Version der Excel-Datei | Maximale Zeilenanzahl | Maximale Spaltenanzahl |
|---|---|---|
| Excel 2007 und neuer | 1.048.576 | 16.384 |
| Excel 97-2003 | 65.535 | 256 |
| Excel 95 und älter | 16.384 | 256 |
- Ist ein Name zu lang, so tritt ebenso ein Fehler auf.
- Die Daten werden automatisch untersucht, um den Datentyp des jeweiligen Feldes zu bestimmen. Normalerweise werden hierfür die ersten acht Zeilen betrachtet und der häufigste Datentyp ausgewählt.
- Unter [Erweitere Eigenschaften] können Sie einen Eintrag "MAXSCANROWS" hinzufügen und einen Wert von 1 bis 16 für die Anzahl der zu betrachtenden Zeilen setzen.
- Ansonsten wir der Wert von "TypeGuessRows" des Registrierungsschlüssels
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excelgenutzt. Der Standardwert ist hier 8, setzen können Sie Werte zwischen 1 und 16. Wenn Sie 0 setzen, werden alle Zeilen betrachtet. - Gibt es unterschiedliche Datentypen in einem Feld, so können Sie unter [Erweitere Eigenschaften] können Sie einen Eintrag "IMAEX=1 hinzufügen. Dies erzwingt die Nutzung von "ImportMixedTypes=Text" des Registrierungsschlüssels
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel. Allerdings kann es dabei zu unerwünschten Ergebnissen kommen.
- Bei Verbindungen zu Excel werden Passwörter nicht unterstützt.
- Sie können Verbindungen zu Arbeitsmappen oder benannten Bereichen erstellen.
- Auf den Namen von Arbeitsmappen folgt das Dollarzeichen ("$").
- Sie können Verbindungen zu mehreren benanntnen Bereichen der gleichen Arbeitsmappe erstellen.
- Bei benannten Bereichen werden immer der gesamte Bereich importiert, auch wenn dort keine Daten enthalten sind. Um den Import leerer Zeilen zu verhindern, setzen Sie die [Interne Importfilterformel] auf
FValid(ME.Code).
Moreover, because of the tail information of the output rows, so far, is available in Excel, this information is exported so that the rows are added.
Verbindungen zu XLSX-Dateien
- Nutzen Sie den OLE DB Provider "Microsoft Office 12.0 Access Databese Engine OLE DB Provider".
- Der entsprechende Registrierungsschlüssel ist
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel.
Hilfe Nummer: 757700
