Import de données d'un fichier Excel [Résolu]

[Lobay]

Bonjour,

Je suppose que ce sujet a été abordé de nombreuses fois mais lorsque je fais une recherche sur le forum à propos de "excel" / "xls" et "ms sql" , je tombe sur des centaines de résultats mais aucun ne traitant mon problème. Je ne sais pas si les mots-clefs sont trop courts ou si je me sers mal de l'outil de recherche, mais je ne trouve pas de réponse à ma question. Désolé par avance si ce sujet est donc facilement trouvable.

J'explique un peu mon problème. J'ai des données sur dans fichier .xlsx (que je peux enregistrer en .xls si besoin) et je souhaite créer une procédure pour les insérer dans une base SQL 2005.

Dans mes recherches je suis tombé sur des codes de ce style :

Code :

1
2
SELECT *
FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=\\REPERTOIRE\Test.xls;HDR=YES', 'SELECT * FROM [Feuil1$]')

Ce qui me retourne comme erreur :
Cannot get the column information from OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

Lors de mes recherches sur le net, j'ai vu que je n'étais pas le seul face à ce problème, mais les rares solutions proposées ne m'aident pas à le résoudre (supprimer les espaces, relancer SQL server, vérifier que le fichier existe bien dans le répertoire spécifié, être sûr que le fichier ne soit pas ouvert en même temps, essayer avec le format .xls, ...).

Je me demande s'il ne manque pas quelque chose à installer pour pouvoir utiliser 'Microsoft.Jet.OLEDB.4.0' mais je n'ai rien trouvé à ce sujet (et si c'est le cas, je ne peux pas utiliser cette méthode).

Auriez-vous une solution pour résoudre / contourner ce problème ?
Je vous remercie par avance.

[Ptit_Dje]

SQL Server cree un fichier temporaire au niveau de "c:\document...." et tout le tralala...

Alors ce que vous pouvez faire et qui est le plus propre d'apres moi:
Dans la registry assurez vous d'avoir ces clefs (REG_DWORD):
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<votreinstance>\Providers\Microsoft.Jet.OLEDB.4.0
AllowInProcess: 1
DisallowAdHocAccess: 0
DynamicParameters: 1

Creez un login SQL qui vous servira de proxy ayant les permissions dans las DB ou vous souhaitez charger votre fichier (i.e.: sql_openrowset)

Grantez le droit d'impersonalisation sur ce login au compte/groupe d'utilisateur devant effectuer un tel chargement:

Code :

1
2
 
GRANT IMPERSONATE ON LOGIN::[sql_openrowset] TO [<votreGroupe>]

Au niveau du filesysteme, assurez vous que le groupe local de votre instance SQL a access en lecture au fichier excel.

Lorsque vous chargez votre fichier, basculer dans le contexte d'execution de sql_openrowset:

Code :

1
2
execute AS login = 'sql_openrowset' -- D'ou la necessite de faire le grant impersonate ci-dessus.
<Votre query>

Lorsque vous lancerez votre code, vous serez sous le contexte d'execution d'un login SQL Server -> au niveau systeme, ce sera le compte de service SQL Server qui executera la lecture du fichier + creation du fichier temporaire.

[Lobay]

Merci beaucoup de votre réponse si rapide !

Alors, dans la base de registre, j'ai ceci :



Et dans les 4 sous-dossiers de Microsoft.Jet.OLEDB.4.0, juste des clés "(par défaut)". J'en déduis que je n'ai pas les clefs dont vous avez parlé et que mon problème vient de là ?

Je n'ai pour l'instant pas la possibilité (je n'ai pas les droits) de créer le login ni de lui donner les droits que vous me conseillez (je pourrai sûrement tester lundi).

Y aurait-il une autre possibilité que de passer par la requête dont j'ai parlé ou est-ce l'unique moyen pour lire et insérer des données provenant d'Excel dans SQL Server ?

[Ptit_Dje]

Vous ne regardez pas la clef de registre que je vous ai donne et je ne sais pas comment vous etes arrive la... (NB il y a un parametre <votreinstance> a identifier depuis la clef HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL

Il se peut qu'au niveau de la bonne clef (donnee dans le post precedent) les valeurs citees ne soient pas presentes. Dans ce cas creez les.

Vous pouvez ensuite tester votre query. Vous risquez un access denied.
Dans ce cas, un shortcut serait de vous donner des droits au niveau de:
C:\Documents and Settings\<votre service account SQL>\Local Settings\Temp.
Ce que je trouve pas propre.

De plus si vous etes sur un Windows Server 2008 R2 vous pourriez meme ne pas avoir access a ce path.

Il y a aussi moyen de changer la valeur de la variable d'environnement TEMP... (edit: je trouve ca bof bof aussi car on sait pas l'impact que ca peut avoir sur le reste et demande un reboot du serveur)

Voila un peu de lecture:
http://blogs.msdn.com/b/spike/archiv...ied-error.aspx

NB: la solution proposee dans le premier post est testee et validee et a ete pensee pendant un certain temps...

Edit:
Encore un peu de lecture:
http://connect.microsoft.com/SQLServ...nticated-users

[Lobay]

Oups désolé, je n'ai pas l'habitude de me balader dans l'éditeur de registre !

Je n'ai pas de "Instance Names\SQL" dans "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\" (même dans les sous dossiers).
J'ai simplement 6 dossiers "80", "90", "BC", "BOL", "Services" et "SQLXML4" (je ne sais pas si ça aide).

Sinon pour la capture d'écran, je suis allé dans :
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\

Puis ne sachant pas à quoi correspondait <votreinstance> dont vous parliez, j'ai fait une recherche de "Microsoft.Jet.OLEDB.4.0" sur les sous-dossiers, ce qui m'a amené dans ce répertoire.

Merci pour ces deux liens. J'étais tombé sur le premier en recherchant à partir de l'erreur affichée, mais il ne répond pas à ma question. Je vais regarder le 2e plus en détail !

En tout cas, un grand merci pour vos réponses ! En espérant que je puisse utiliser la 1e méthode pour résoudre ce problème.

[elsuket]

Merci Pti_Dje pour ce tuyau, car j'ai tellement été déçu par les serveurs liés autres que de type SQL Server que je les ai totalement abandonnés.
Alors je serai heureux de connaître la solution !

Pour ma part j'ai SQL Server 2008 R2 (version 10.50) installé en instance par défaut, et je trouve donc la clé que tu indiques ici :

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\Providers\Microsoft.Jet.OLEDB.4.0

Dans une instance SQL Server 2008 (non R2), qui est aussi une instance par défaut, je la trouve sous :

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\Providers\Microsoft.Jet.OLEDB.4.0

@++

[Ptit_Dje]

De rien
J'ai tres peu d'instances par defaut et j'ai pas regarde ou se trouve la clef de registre dans ce cas la.
En 2008 (r2), la meme methodologie peut etre apppliquée avec le provider microsoft.ace.oledb.12.0 plutot que Microsoft.Jet.OLEDB.4.0.

Cheers,
Dje

[elsuket]

En tapant le nom que tu as donné sur un moteur de recherche très connu, j'ai trouvé ceci.

Est-ce donc le fait d'installer ce package qui fait que l'on peut facilement accéder à un fichier Excel ou Access (j'entends par là : sans passer deux heures à se faire des nœuds au cerveau pour tenter en vain de faire fonctionner un serveur lié sur ce type de fichiers) ?

@++

[Ptit_Dje]

C'est surtout que JET est pas compatible avec une infra/appli en 64bit.
ACE l'est
Je sais pas pourquoi j'avais en tete que c'etait par defaut avec 2008(+).