Discussion :

[evans]

Bonjour,

J'ai un bout de code qui me permet d'importer des données d'un fichier Excel, mais je n'arrive pas à le faire fonctionne à cause d'une erreur de syntaxe. Le chemin du fichier est envoyé en paramètre à ma procédure.

Voici le code :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
CREATE PROCEDURE LectureExcel (
	@Chemin		nvarchar(1000))
AS
 
BEGIN TRY
BEGIN TRANSACTION
 
Insert into Elements (No_element, Nom_elem, Desc_elem, Tps_elem)
	Select No_element, Nom_elem, Desc_elem, Tps_elem 
	FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
		'Excel 8.0;Database=' + @Chemin + ';HDR=YES', 
		'SELECT No_element, Nom_elem, Desc_elem, Tps_elem FROM [Feuille1$]');
 
COMMIT TRANSACTION
END TRY
 
BEGIN CATCH
	IF XACT_STATE() = -1
    BEGIN
		ROLLBACK TRANSACTION
		RETURN	
	END;
    IF XACT_STATE() = 1
	BEGIN
		COMMIT TRANSACTION
	END;
END CATCH;

L'erreur que cela produit est : Incorrect syntax near '+' et il pointe sur la ligne 'Excel 8.0;Database=' + @Chemin + ';HDR=YES'

Mais je n'arrive pas à comprendre pourquoi la concaténation ne se fait pas entre mon paramètre et ma String dans le OpenRowSet.

Merci,

Évans

[WOLO Laurent]

Simplement parce que vous devez plutot utiliser le SQL Dynamique !
Construiser cette requette dans une chaine varchar puis executer la chaine de commande avec la commande EXEC.

[evans]

Bonjour,

Merci pour l'info. J'aurais une autre question, comment fait-on pour sélectionner les noms de colonne du fichier Excel ? Avec les lettres des colonnes ?

Car je crois qu'il faut que la table et le fichier Excel aient les même noms de colonnes, alors comment on peut avoir une colonne No_element dans un fichier Excel par exemple.

Maintenant, j'obtiens ça comme commande à exécuter :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
Insert into Elements (No_element, Nom_elem, Desc_elem, Tps_elem)
	Select No_element, Nom_elem, Desc_elem, Tps_elem 
	FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
		'Excel 8.0;Database=Z:\Test.xls;HDR=YES', 
		'SELECT No_element, Nom_elem, Desc_elem, Tps_elem FROM [Feuille1$]');

Et voici le code que j'ai pour générer et exécuter cette commande :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
Set @Commande = 'Insert into Elements (No_element, Nom_elem, Desc_elem, Tps_elem)
	Select No_element, Nom_elem, Desc_elem, Tps_elem 
	FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', 
		''Excel 8.0;Database=' + @Chemin + ';HDR=YES'', 
		''SELECT No_element, Nom_elem, Desc_elem, Tps_elem FROM [Feuille1$]'');'
print @Commande
exec @Commande

Et le numéro d'erreur est le : 203

Merci encore,

Évans

[WOLO Laurent]

Le principe est simple :
Vous ne devez laisser que les colonnes nécessaire et dans l'ordre de votre entête Insert.
Si votre insert renseigne 4 colonnes, votre classeur excell doit aussi n'avoir que 4 cellules et dans l'ordre établie dans la commande insert.

[evans]

Bonjour,

D'accord, alors j'ai un fichier Excel qui ressemble à ca :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
1     Élément 1     Visser une vis       0,2
2     Élément 2     Brancher un câble      0,4
3     Élément 3     Connecter le câble     0,8

Je l'ai envoyé dans ma commande que j'ai écrit précédemment et il me cause l'erreur 203, qui, semblerait-il serait en lien avec un serveur. Voici le lien (en anglais) :
http://msdn.microsoft.com/library/de...ntmgr_59wx.asp

J'ai donc demandé de l'aide à un de mes collègues et il m'a dit d'essayer la chose suivante, qui consiste en fait à créer un serveur pour la source Excel. Voici le code :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- On drop le server s'il existe
 
      IF  EXISTS (SELECT srvname FROM master.dbo.sysservers srv WHERE srv.srvid != 
 
      0 AND srv.srvname = N'ExcelSource') 
 
      EXEC master.dbo.sp_dropserver @server=N'ExcelSource', @droplogins='droplogins' 
 
 
 
      -- On créé le server
 
      EXEC sp_addlinkedserver 
 
       @server='ExcelSource', 
 
       @srvproduct='Jet 4.0', 
 
       @provider='Microsoft.Jet.OLEDB.4.0', 
 
       @datasrc=@Chemin, 
 
       @provstr='Excel 10.0' 
 
 
 
      -- On se connecte sur le linked server
 
      EXEC sp_addlinkedsrvlogin N'ExcelSource', false, N'sa', N'ADMIN', NULL

Et ensuite, je pourrais tout simplement effectué ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
      -- On fait l'insertion dans la table locale
Insert into Elements (No_element, Nom_elem, Desc_elem, Tps_elem)
      Select * from ExcelSource...[Elements$]

Cependant, il me fait des erreurs dont je ne suis pas capable de capturer le numéro dans mes Try Catch. Les voici :

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ExcelSource" returned message "Impossible de démarrer votre application. Le fichier d'informations du groupe de travail est absent ou ouvert en mode exclusif par un autre utilisateur.".
Msg 7399, Level 16, State 1, Procedure LectureExcel, Line 24
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ExcelSource" reported an error. Authentication failed.
Msg 7303, Level 16, State 1, Procedure LectureExcel, Line 24
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ExcelSource".

Et alors, je ne suis pas trop sûr de comprendre. Je ne sais pas si j'ai été assez clair dans tout ce que j'ai fait, sinon dites le moi, je tenterai d'expliquer davantage.

Merci,

Évans

[WOLO Laurent]

Dans ta première solution, il te suffit de remplacer les virgules par les point. En fait, il s'agit d'un problème d'interpretation du point-décimal.

[evans]

Bonjour,

Je viens de modifier mon fichier Excel comme ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
1     Élément 1     Visser une vis         0.2 
2     Élément 2     Brancher un câble      0.4 
3     Élément 3     Connecter le câble     0.8

Et j'ai repris ma première solution, c'est-à-dire celle-ci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
Set @Commande = 'Insert into Elements (No_element, Nom_elem, Desc_elem, Tps_elem) 
   Select *
   FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', 
      ''Excel 10.0;Database=' + @Chemin + ';HDR=YES'', 
      ''SELECT * FROM [Elements$]'');' 
print @Commande 
exec @Commande

Cependant, il me fait toujours la même erreur, celle du numéro 203. J'ai un peu modifié ma solution comparativement à l'ancienne au niveau de la version d'Excel. Avant j'avais 8.0, mais puisque j'ai Office XP, il me faut 10.0 n'est-ce pas ? Je ne sais pas si cela peut changer quelque chose, alors j'ai tenté le coup. Malheureusement, ceci n'a rien changé. Je ne sais pas si j'ai mal construit ma commande, mais je n'arrive pas à trouver la raison de cette erreur...

Merci,

Évans

[evans]

Bonjour,

J'ai finalement réutiliser la solution précédente (celle avec "Exec @Commande") sauf que puisque c'est une commande SQL il faudrait mettre ce bout là "exec sp_executesql". Bon je croyais que ça allais régler le problème, mais cependant, il m'apparait l'erreur numéro 15281 qui, en fait, ne cause pas réellement d'erreur, puisque ma procédure se termine.

Je ne sais pas pourquoi, mais cette erreur n'est pas affiché sur le site e MSDN... étrange.

Je me demandais si l'un de vous pourrais y trouver une réponse.

Voici le code complet de ma procédure :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
CREATE PROCEDURE LectureExcel (
	@Chemin		varchar(1000),
	@Reussi		int = 0 output)
WITH ENCRYPTION
AS
 
  SET NOCOUNT OFF
 
DECLARE	@Commande as nvarchar(1050);
 
BEGIN TRY
BEGIN TRANSACTION
 
Set @Commande = 'Insert into Elements (No_element, Nom_elem, Desc_elem, Tps_elem) 
   Select *
   FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', 
      ''Excel 10.0;Database=' + @Chemin + ';HDR=YES'', 
      ''SELECT * FROM [Elements$]'');' ;
print @Commande ;
exec sp_executesql @Commande;
 
 
COMMIT TRANSACTION
set @Reussi = 1;
END TRY
 
BEGIN CATCH
	IF XACT_STATE() = -1
    BEGIN
		ROLLBACK TRANSACTION
		set @Reussi = 0;
	END;
    IF XACT_STATE() = 1
	BEGIN
		COMMIT TRANSACTION
		set @Reussi = 1;
	END;
print 'Erreur:'
print error_number()
RETURN
END CATCH;

Merci,

Évans

[evans]

Bonjour,

J'ai finalement réussi à faire fonctionner ma procédure. Voici le code final :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
CREATE PROCEDURE LectureExcel (
	@Chemin		varchar(1000),
	@Reussi		int = 0 output)
WITH ENCRYPTION
AS
 
  SET NOCOUNT OFF
 
DECLARE	@Commande as nvarchar(1050);
 
BEGIN TRY
BEGIN TRANSACTION
 
Set @Commande = 'Insert into Elements (No_element, Nom_elem, Desc_elem, Tps_elem) 
   Select No_element, Nom_elem, Desc_elem, Tps_elem
   FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', 
      ''Excel 5.0;Database=' + @Chemin + ';HDR=YES;IMEX=1'', 
      ''Select * from [Elements$]'');' ;
 
exec sp_executesql @Commande;
 
COMMIT TRANSACTION
set @Reussi = 1;
END TRY
 
BEGIN CATCH
	IF XACT_STATE() = -1
    BEGIN
		ROLLBACK TRANSACTION
		set @Reussi = 0;
	END;
    IF XACT_STATE() = 1
	BEGIN
		COMMIT TRANSACTION
		set @Reussi = 1;
	END;
print 'Erreur:'
print error_number()
RETURN
END CATCH;

Et voilà, j'ai changer Excel 10.0 pour Excel 5.0 et rajouter IMEX = 1 (je ne sais pas trop pourquoi, mais ceci semble fonctionner). J'ai aussi modifier au niveau de la sélection dans le RowSet qui me permet d'avoir une seule feuille et sélectionner les colonnes que je veux. HDR = YES, tant qu'à lui, sert à pouvoir tenir la compte une colonne titre (header row) pour faciliter l'accès aux colonnes dans le fichier Excel. Sinon, celui-ci les nomme comme F1, F2, F3...

Merci à tous,

Évans

[WOLO Laurent]

Bravo !