Importer des données d'Excel : pas si simple ?

Lorsqu'on a besoin d'importer vers une table SQL Server des données d'Excel, la première solution qui vient à l'esprit est l'"Assistant Importation et Exportation de données", directement accessible par un clic droit / Tâches dans SQL Server Management Studio.

Lançons l'assistant et choisissons donc une source de données Microsoft Excel.

A la première tentative d'accéder au fichier, message d'erreur : Le fournisseur 'Microsoft.ACE.OLEDB.12.0' n'est pas inscrit sur l'ordinateur local.

Qu'à cela ne tienne, nous allons donc installer le pilote nécessaire : Microsoft Access Database Engine Redistributable. On en trouve deux versions disponibles :

La version 2010 SP2, téléchargeable ici, va installer le pilote 12.0
La version 2016, téléchargeable ici, va installer le pilote 16.0 et aussi le 12.0. Qui peut le plus peut le moins.

Notez que l'utilisation de ces pilotes pour un serveur n'est pas supportée : c'est donc à vos risques et périls ! Sur mon laptop, pas de problème...

Le premier dilemme se pose : faut-il installer la version 32 bits ou 64 bits ? C'est ici où cela se complique :

Ma plate-forme est en x64, donc pourquoi pas la version x64 ? Cela semblerait être le mieux...
Mais SQL Server Management Studio est une application 32 bits, donc il me faut plutôt la version x86...
Mais mon SQL Server est installé en x64, et si je veux un jour faire un serveur lié avec Excel, ou une commande OPENDATASOURCE, la version nécessaire est impérativement la x64 sur ce serveur...

J'aimerais bien installer les deux, mais l'installation côte à côte des versions x86 et x64 n'est pas supportée et toute tentative nous montre un message d'erreur.

Il y a bien quelques tutoriels sur internet qui expliquent que l'on peut installer côte à côte une version incompatible avec le paramètre /passive (ou /quiet selon la version). Mais au bout du test, on finit par comprendre que cela ne marche pas vraiment et que cela pose de vrais problèmes.

En plus, si jamais j'ai installé Microsoft Office sur ma plate-forme, le choix est encore plus contraint : si j'ai Office x86, impossible d'installer le pilote x64. Et si j'ai Office x64, impossible d'installer le pilote x86...

La solution qui fonctionne apparemment bien sur mon laptop est la suivante :

J'avais Office2019 x64 déjà installé. J'ai donc installé la version 2016 en x64 du Access Database Engine. Parfait pour les serveurs liés, mais mon assistant d'importation ne fonctionne toujours pas, 32 bits oblige.
Toutes les tentatives pour forcer une installation de la version 2016 en x86 se sont soldées par un échec (au point de faire crasher SQL Server lorsqu'on essaie de configurer un serveur lié avec Excel)
J'ai réussi finalement à installer tout de même la version 2010 en x86 côte à côte avec la version 2016 x64, sans que cela ne semble poser de problème. Et l'assistant fonctionne (à condition de choisir l'option "Excel 2007-2010", et le serveur lié aussi !

Je vous passe la suite de l'importation avec l'assistant, vu que c'est assisté c'est assez trivial.

Configuration d'un serveur lié Excel

Maintenant que j'ai un pilote, il est donc possible de configurer un serveur lié pour accéder à mon fichier Excel.

La configuration d'un serveur lié avec Excel est la suivante :

N'oublions pas d'octroyer les permissions d'accès nécessaire sur le fichier (ou dossier) au compte de service SQL Server : c'est lui qui va lire le fichier !

Et quelques autres étapes sont nécessaires :

1. Configurer le fournisseur Microsoft.ACE.OLEDB.12.0 pour s'exécuter In Process :


EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1

2. Si nécessaire, donner explicitement les permissions sur le répertoire temporaire de SQL Server à l'utilisateur qui lance la requête. Si ce n'est pas fait et qu'on est en local sur le serveur, l'UAC peut bloquer l'exécution et le moyen de s'en sortir serait alors de lancer SSMS en tant qu'administrateur ! Le chemin du répertoire temporaire est différent selon le type de compte de service.
- Si c'est un compte d'utilisateur : C:\Users\<ServiceAccountName>\AppData\Local\Temp
- Si c'est un compte virtuel : %Windir%\ServiceProfiles\<InstancePath>\AppData\Local\Temp

3. Autoriser les requêtes Ad Hoc distribuées


exec sp_configure 'Ad Hoc Distributed Queries',1
RECONFIGURE

Interrogation en TSQL

Nous allons tenter de visualiser nos données Excel, avec la requête (où sample est le nom de l'onglet dans le fichier Excel). Bien sûr, pour importer des données Excel dans une table, il n'y aura plus qu'à faire un INSERT SELECT ou un SELECT INTO...

Première méthode, utiliser le serveur lié, directement ou avec OPENQUERY :


SELECT * FROM [MYEXCEL]...sample$
SELECT * FROM OPENQUERY(MYEXCEL, 'SELECT * FROM [sample$]')

Deuxième méthode, sans mentionner de serveur lié, avec OPENROWSET, 2 syntaxes :


SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=C:\Files\Tableau.xlsx', [sample$])
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;DATABASE=C:\Files\Tableau.xlsx', 'Select * FROM [sample$]')

Troisième méthode, serveur lié créé à la volée avec OPENDATASOURCE :


SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
'Data Source=C:\Files\tableau.xlsx;Extended Properties=Excel 12.0')...[sample$]

Et encore plus amusant, INSERT et UPDATE dans le fichier Excel :


-- INSERT AND UPDATE (delete not supported)
--------------------------------
INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;DATABASE=C:\Files\tableau.xlsx',
'Select * from [sample$]')
(Prénom,Nom)
SELECT 'Aude','Vessel'

INSERT INTO OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0',
'Data Source="C:\Files\tableau.xlsx";
extended Properties=Excel 12.0')...[sample$]
(Prénom,Nom)
SELECT 'Paul','Issier'

UPDATE OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;DATABASE=C:\Files\tableau.xlsx',
'Select * from [sample$]')
SET Prénom='Alex' WHERE Nom = 'Thérieur'
GO

Bonne importation de feuilles Excel !

Datafly.pro