Microsoft Excel ist eines der beliebtesten Werkzeuge für die Arbeit mit Tabellendaten. Es ermöglicht dem Benutzer, Informationen bequem zu erstellen, zu bearbeiten und zu analysieren. Wenn Sie jedoch mit großen Datenmengen arbeiten, müssen Sie möglicherweise Datenbanken wie SQL Server verwenden.
In diesem Artikel werden die Grundlagen der Interaktion zwischen Excel und SQL Server erläutert. Wir besprechen insbesondere, wie Sie Daten aus einer Datenbank in Excel importieren und wie Sie Daten aus Excel in SQL Server exportieren können. Darüber hinaus werden wir hilfreiche Tipps und Anleitungen zur Optimierung Ihrer Dateneingabe einsehen.
Bevor Sie mit den Daten beginnen können, müssen Sie den ODBC-Treiber installieren und seine Verbindung mit SQL Server konfigurieren. Dadurch können Sie eine Verbindung zwischen Excel und der Datenbank herstellen und Abfragen direkt aus Excel ausführen.
Um Daten aus SQL Server nach Excel zu importieren, können Sie die Funktion "Daten abrufen" im Abschnitt "Daten abrufen und Transformieren" verwenden. Mit dieser Funktion können Sie eine Datenquelle (in unserem Fall SQL Server) auswählen, die erforderlichen Verbindungseinstellungen angeben und eine Abfrage ausführen. Danach werden die ausgewählten Daten in Excel importiert und als Tabelle angezeigt.
Wenn Sie Daten aus Excel nach SQL Server exportieren möchten, verwenden Sie die Funktion "Power Query Server". Damit können Sie eine Tabelle oder einen Datenbereich in Excel auswählen, eine Datenbank angeben und einen Exportvorgang ausführen. Dabei ist es wichtig zu berücksichtigen, dass die Tabellenstruktur in Excel und SQL Server kompatibel sein muss, um Datenverluste oder Informationsverfälschungen zu vermeiden.
Erforderliche Komponenten installieren
Bevor Sie mit Excel von SQL Server aus interagieren können, müssen Sie mehrere Komponenten installieren, um die Kompatibilität zu gewährleisten und ordnungsgemäß zu funktionieren.
Der erste Schritt besteht darin, den Microsoft ACE-Treiber für den Zugriff auf Excel-Dateien zu installieren. Der Microsoft ACE-Treiber unterstützt Excel 2007-Dateiformate und neuere Versionen. Um den Treiber zu installieren, müssen Sie die Installationsdatei von der offiziellen Microsoft-Website herunterladen und den Installationsanweisungen folgen.
Nachdem Sie den Microsoft ACE-Treiber installiert haben, müssen Sie sicherstellen, dass SQL Server auf dem Computer installiert und für die Arbeit mit Excel konfiguriert ist. Dazu müssen Sie SQL Server Configuration Manager ausführen und sicherstellen, dass der entsprechende Excel-Supportschlüssel aktiviert ist.
Wenn der Abschnitt zur Unterstützung von Excel nicht vorhanden ist, müssen Sie die folgenden Schritte ausführen:
- Öffnen Sie SQL Server Management Studio, und stellen Sie eine Verbindung mit der Instanz von SQL Server her.
- Führen Sie die folgende Abfrage an den SQL-Server aus: sp_configure 'show advanced options', 1;RECONFIGURE;sp_configure 'Ad Hoc Distributed Queries', 1;RECONFIGURE;
- Führen Sie dann die folgende Abfrage aus, um die Excel-Unterstützung zu aktivieren: EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
- Starten Sie SQL Server nach dem Ausführen der Abfragen neu, damit die Änderungen wirksam werden. Möglicherweise müssen Sie auch den Computer neu starten.
Nachdem Sie die erforderlichen Komponenten installiert und konfiguriert haben, können Sie von SQL Server aus mit Excel interagieren und die gewünschten Datenoperationen durchführen.
Einrichten einer Verbindung zu Excel
Bevor Sie mit Excel in SQL Server arbeiten können, müssen Sie eine Verbindung zwischen den beiden Anwendungen einrichten. Dazu müssen Sie einige Werkzeuge und Anpassungen verwenden.
1. Installieren Sie den Anbieter, um mit Excel zu arbeiten. Öffnen Sie dazu SQL Server Management Studio, und führen Sie das folgende Skript aus:
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
2. Erstellen und konfigurieren Sie eine OLE DB-Datenquelle. Navigieren Sie dazu in SQL Server Management Studio zum Knoten Server, klicken Sie mit der rechten Maustaste auf den Knoten Datenquellen, und wählen Sie Neue Datenquelle erstellen aus. Wählen Sie im angezeigten Dialogfeld "Microsoft Office 12.0 Access Database Engine OLE DB Provider" aus und klicken Sie auf Weiter. Geben Sie den Pfad zur Excel-Datei an, und konfigurieren Sie die Verbindungseinstellungen.
3. Erstellen Sie mithilfe des vollständigen Namens der OLE DB-Datenquelle, den Sie im vorherigen Schritt erstellt haben, eine Beziehung zwischen SQL Server und Excel. Führen Sie dazu das folgende Skript aus:
EXEC sp_addlinkedserver @server = 'ExcelServer', @srvproduct = 'OLE DB Provider for Jet', @provider = 'Microsoft.ACE.OLEDB.12.0', @datasrc = 'C:\путь\к\файлу\Excel .xlsx', @provstr = 'Excel 12.0;HDR=YES';
4. Führen Sie die folgende Abfrage aus, um die Verbindung mit Excel zu überprüfen:
SELECT * FROM OPENQUERY(ExcelServer, 'SELECT * FROM [Blatt1$]');
Nachdem Sie diese Schritte ausgeführt haben, können Sie mit den Daten in Excel von SQL Server aus beginnen, Abfragen ausführen und die Informationen aktualisieren.
Vergessen Sie nicht, dass Sie den Microsoft ACE OLEDB-Treiber (verfügbar auf der offiziellen Microsoft-Website) installieren und über die entsprechenden Berechtigungen für den Zugriff auf die Excel-Datei verfügen müssen, damit er ordnungsgemäß funktioniert.
