Discussion :

[QuestVba]

Bonjour au Forum,

Voici ma problématique : réaliser une requête sur deux fichiers distincts et fermés.

Au début, j'ai un seul fichier contenant deux onglets.

Onglet 'Scale' :

FILE_NUMBER	PERSON_ID	SCALE	FEDERAL_STATE
2238239	1146534	S40	FRENCH_STATE
2235247	100004895785	S40	DUTCH_STATE
2235247	1698343
2235963	1483033	S42B	GGC

etc.


Onglet 'Act' :

FILE_NUMBER	PERSON_ID	SOC_PROF_STATUS	SOC_PROF_START_MONTH	REASON_RIGHT_TYPE
48295	1146534	ACTIVE	195206	A56BIS1
2233955	100004895785	REFUSED	201607	G20071971
49007	1698343	ACTIVE	201803	NO_REASONRIGHT

Pour récupérer les données 'Refused' et 'S40', j'utilise ce code qui fonctionne très bien.

Code :

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

1
2
3
4
5
Const DB_PATH = "H:\BackUp\Lionel B - 60106\Mod Excel\SQL\DB_Nom.xlsx"
 
Public Const CONNSTR = "PROVIDER=MICROSOFT.ACE.OLEDB.12.0;" & _
                       "DATA SOURCE=" & DB_PATH & ";" & _
                       "Extended Properties=""Excel 12.0;HDR=YES;"";"

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
Option Private Module
Option Explicit
 
Sub Comment_SQL()
 
    Sheets("Search").Range("a2").CurrentRegion.Select
    Selection.ClearContents
 
'settings connection DB
Dim oConnection As New ADODB.Connection
Dim oRecordset As New ADODB.Recordset
 
Dim sSQL_Query As String: sSQL_Query = "SELECT [Act$].[FILE_NUMBER], [Act$].[PERSON_ID] " & _
                                      "FROM [Act$] left join [Scale$] on [Scale$].[PERSON_ID] = [Act$].[PERSON_ID]" & _
                                      "WHERE [SOC_PROF_STATUS] = 'REFUSED' and [SCALE]='S40'"
 
Debug.Print sSQL_Query
 
'connection DB--------------------------------------------------------------------------------------
 oConnection.Open CONNSTR
oRecordset.Open sSQL_Query, oConnection, adOpenStatic, adLockOptimistic, adCmdText
Dim nFound As Integer: nFound = oRecordset.RecordCount '#result
If nFound <> 0 Then Sheets("Search").Range("A2").CopyFromRecordset oRecordset
 
'Entêtes de colonne
Dim liCount As Integer
For liCount = 0 To oRecordset.Fields.Count - 1
    Sheets("Search").Cells(1, liCount + 1) = oRecordset.Fields(liCount).Name
Next
 
oRecordset.Close
oConnection.Close
 
Set oRecordset = Nothing
Set oConnection = Nothing
 
End Sub

Comme je le disais, cela fonctionne très bien car c'était pour m'amuser. Mais, maintenant, je dois réaliser ce genre d'opération mais avec deux fichiers distincts : l'un contenant les données 'Scale' et l'autre contenant les données 'Act'.

J'ai bien essayé des petits trucs, des adaptations... mais rien ne fonctionne.

Quelqu'un aurait-il au moins un grand début de piste car même en regardant d'autres postes, je n'y arrive pas. MERCI.

[Invité]

https://www.developpez.net/forums/d1.../#post10438761

[QuestVba]

Merci dysorthographie pour cette piste.
Je zieute.

[Invité]

Bonjour,

l'utilisation de requêtes SQL permet de gérer un grand nombre de données avec ses propres règle de nommage, le problème étant le temps de connexion aux fichiers.

si comme dans le deuxième exemple du poste précédent, je fait une connexion sur fichier1 la notion de in 'fichier1.xlsx' a disparût. mais si mon programme parcoure les 2000 fichier d'un répertoire, je me retrouve avec 2000 connexion déconnexion ce qui consomme un temps considérable et inutile.

en me connectant à thisworkbook.fullname je me connecte et me déconnecte qu'une fois et la je retrouve la jointure externe { in 'fichier1.xlsx' }.

autre problème, thisworkbook.fullname implique un enregistrement de la source vue que thisworkbook.fullname donne le chemin complet ou est sauvegardé le fichier???? mais que fais-je en phase de développement ou Classeur1 ne se trouve qu'en mémoire?

j'utilise une connexion sur un répertoire comme je le ferais pour des fichier CSV et j'utilise des jointure externe pour Fichier1 et Fichier2

Code :

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

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=TEMP;Extended Properties=""Text;HDR=YES;FMT=Delimited;"""

désolé de donner une réponse de normand {p'être ben qu'(Oui/Non)},mais ça reste du filing!

[QuestVba]

Re-, dysorthographie,
Je continue ma découverte. J'ai testé toute une série de combinaison avec mes deux fichiers et cela donne de bons résultats. Normal si les DB sont correctes et cohérentes.

Du coup, je vais un peu plus loin et j'essaie de faire une jointure entre trois fichiers (je mets un exemple de ce 3e fichier au cas où).

Ma requête en français est :

• Fichier Act > SOC_PROF_STATUS = ACTIVE
• Fichier Scale > SCALE = S40
• Fichier Mono > RIGHT_MONOPARENTAL_SUPPL = 1

Mais j'avoue que je patauge pour le code. Je pense avoir compris qu'il vaut mieux faire des connexions successives plutôt qu'ouvrir toutes les connexions en même temps, mais du coup, je ne comprends pas comment faire. Si tu as un peu de temps pour continuer mon apprentissage ou me renvoyer vers une autre source...

[QuestVba]

Bon, ben je pense avoir trouvé :

Code :

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

1
2
3
rQuery = "SELECT [Fichier1].[PERSON_ID],[Fichier1].[SOC_PROF_STATUS],[Fichier2].[SCALE],[Fichier3].[RIGHT_MONOPARENTAL_SUPPL] " & _
"FROM ([TAct$]  as Fichier1  INNER JOIN (select * from [TScale$]  in '" & Fichier2 & "' 'Excel 12.0;HDR=Yes' ) as Fichier2 ON [Fichier1].[PERSON_ID] = [Fichier2].[PERSON_ID] ) INNER JOIN (select * from [TMono$]  in '" & Fichier3 & "' 'Excel 12.0;HDR=Yes' ) as Fichier3 ON [Fichier1].[PERSON_ID] = [Fichier3].[PERSON_ID] " & _
                                      "WHERE [SOC_PROF_STATUS] = 'ACTIVE' and [SCALE]='S40' and [RIGHT_MONOPARENTAL_SUPPL]='1' "

J'espère que c'est bien comme cela qu'il faut faire. Donc, si un pro passe par ici, merci de corriger si jamais.

Bête question complémentaire : dans ma DB (csv d'origine), j'ai des PERSON_ID de toutes sortes : 1646471, 100004895785... Evidemment, pour le moment, je transforme tout en Texte (c'est pour apprendre) mais je pense que je vais avoir des problèmes par la suite. Déjà en chiffre, il se présente comme : 1,00005E+11. Quelle est la meilleure façon de faire avec ce type de données. Les laisser en nombre, les passer en texte, les transformer, ... ? Quel est l'impact sur la requête. Ici, avec le code, j'ai eu quatre doublons et je ense que c'est à cause de ce codage. Qu'en pensez-vous ?

[78chris]

Bonjour

Avec 2016 c'est réalisable très simplement par PowerQuery intégré qui remplace VBA pour traiter les données de nombreuses sources...

[QuestVba]

Salut 78chris.
Si c'est facilement réalisable - Chouette ! Tu aurais un lien vers un tuto ou autre pour une explication rapide (surtout partie avec deux fichiers distincts).

[Invité]

PowerQuery c'est pareil il te faudra écrire une requête avec jointure externe.

https://www.developpez.net/forums/d1.../#post10169353

https://www.developpez.net/forums/d1...l/#post9769211

https://www.developpez.net/forums/d1...e/#post9140019

[78chris]

Bonjour à tous

PowerQuery c'est pareil il te faudra écrire une requête avec jointure externe.

Ai-je dit le contraire ?

une version simple (quoique le chemin paramétrable n'est pas le niveau 0) où on requête d'abord chaque fichier avant jointure

une version en une seule requête où on définit les 2 sources et la jointure dans la même mais besoin de passer par l'éditeur avancé...

[QuestVba]

J'ai ouvert le fichier 'RequêtePQ1.xlsx' mais j'ai cette erreur lorsque je vais vers les requêtes.

Expression.Error : La clé ne correspondait à aucune ligne dans la table.
Détails :
Key=Record
Table=Table

[78chris]

Re

Tes fichiers réels sont sans doute différents de tes exemples...

Notamment ton code semble monter des PERSON_ID différents alors que dans les exemples ils sont similaires... et quid des noms des fichiers et des onglets ? ...

Les sources utilisées ci-joint...

[QuestVba]

Chris,

Effectivement, il y avait une petite différence. Maintenant, cela s'ouvre correctement. Enfin, je pense car il faut que je découvre ce qui s'y cache.

[Invité]

Bonsoir,

Désolé de t'avoir un peut mis de coté, travail oblige.

Oui c'est bien cela. Pour requêter des csv, on considère le répertoire ou sont enregistré les csv comme une base de données.

https://www.developpez.net/forums/d1...u/#post9163787

Mais il subsiste un problème, tu as déjà vus qui'il y avait une différence au niveau des dates entre excel et vba. Et bien pour les csv c'est pareil le ";" pour Excel le "," pour vba, le date les numériques,etc...

C'est bien ta question sur le format des données converties par Excel.

Pour gérer tous cela il faut créer un fichier Shema.ini dans le répertoire qui contient les fichiers.
Le shema.ini te permet de fixer le format de tes colonnes ce qu' Excel ne fait pas!

https://www.developpez.net/forums/d1...s/#post9890122

https://docs.microsoft.com/fr-fr/sql...ql-server-2017


Tu pourras ainsi faire des requêtes sur tes csv.

Code :

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

1
2
3
 rQuery = "SELECT [Fichier1].[PERSON_ID],[Fichier1].[SOC_PROF_STATUS],[Fichier2].[SCALE],[Fichier3].[RIGHT_MONOPARENTAL_SUPPL] " & _
"FROM [TAct#csv]  as Fichier1  INNER JOIN [TScal#csv]  as Fichier2 ON [Fichier1].[PERSON_ID] = [Fichier2].[PERSON_ID]  INNER JOIN  [TMono#csv]   as Fichier3 ON [Fichier1].[PERSON_ID] = [Fichier3].[PERSON_ID] " & _
                                      "WHERE [SOC_PROF_STATUS] = 'ACTIVE' and [SCALE]='S40' and [RIGHT_MONOPARENTAL_SUPPL]='1' "

[Invité]

Bonsoir,
As Fichier1 est un alias pour la [table Excel$] donc ton champ est [Fichier1].[champ]

Code :

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

1
2
3
rQuery = "SELECT [Fichier1].[PERSON_ID],[Fichier1].[SOC_PROF_STATUS],[Fichier2].[SCALE] " & _
"FROM (select * from  [TAct$]  in 'C:\Temp\SQL_DataFam\Act.xlsx' 'Excel 12.0;HDR=Yes' ) as Fichier1 INNER JOIN (select * from [TScale$]  in 'C:\Temp\SQL_DataFam\Scale.xlsx' 'Excel 12.0;HDR=Yes' ) as Fichier2 ON [Fichier1].[PERSON_ID] = [Fichier2].[PERSON_ID] "
Debug.Print rQuery

[QuestVba]

Merci pour le retour, mais j'ai une erreur sur cette ligne

Code :

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

Set objRecordSet = objConnection.Execute(rQuery)

Erreur :

[Invité]

Il me faut le temps d'allumer mon vieil ordinateur et je regarde avec les mêmes fichier que dans les postes précédent.

Mon téléphone ne gère pas les macro

[QuestVba]

Oups, ... Cool, j'ai le temps.
Je mets mes fichiers plus complets.

[78chris]

RE

Tu as juste à l'étape Navigation de chaque requête à remplacer, dans la barre de Formule, Feuil1 par le nom de la feuille dans le classeur source (TAct et TScale)

Si le tableau de restitution dans Excel a perdu son lien : le supprimer et utiliser Données, Connexions existantes TScale.

[Invité]

ton code est parfait regarde le format de PERSON_ID dans Act.xlsx et Scale.xlsx !

j'ai un peut simplifier ton 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
Sub test()
Dim objConnection As Object, Fichier1 As String, Fichier2 As String
Fichier1 = "C:\Temp\SQL_DataFams\Act.xlsx"
Fichier2 = "C:\Temp\SQL_DataFams\Scale.xlsx"
 
 
rQuery = "SELECT [Fichier1].[PERSON_ID],[Fichier1].[SOC_PROF_STATUS],[Fichier2].[SCALE] " & _
"FROM (select * from  [TAct$]  in '" & Fichier1 & "' 'Excel 12.0;HDR=Yes' ) as Fichier1  INNER JOIN (select * from [TScale$]  in '" & Fichier2 & "' 'Excel 12.0;HDR=Yes' ) as Fichier2 ON [Fichier1].[PERSON_ID] = [Fichier2].[PERSON_ID] "
 
 
With CreateObject("ADODB.Connection")
   .Open = "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0 Macro;HDR=YES;"""
    Set Rs = .Execute(rQuery)
    .Close
End With
End Sub

dans cette exemple je fais une connection sur Fichier1 et une joiture externe sur Fichier2

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
Sub test2()
Dim objConnection As Object, Fichier1 As String, Fichier2 As String
Fichier1 = "C:\Temp\SQL_DataFams\Act.xlsx"
Fichier2 = "C:\Temp\SQL_DataFams\Scale.xlsx"
 
 
rQuery = "SELECT [Fichier1].[PERSON_ID],[Fichier1].[SOC_PROF_STATUS],[Fichier2].[SCALE] " & _
"FROM [TAct$]  as Fichier1  INNER JOIN (select * from [TScale$]  in '" & Fichier2 & "' 'Excel 12.0;HDR=Yes' ) as Fichier2 ON [Fichier1].[PERSON_ID] = [Fichier2].[PERSON_ID] "
 
 
With CreateObject("ADODB.Connection")
   .Open = "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=" & Fichier1 & ";Extended Properties=""Excel 12.0 Macro;HDR=YES;"""
    Set Rs = .Execute(rQuery)
    .Close
End With
End Sub