Discussion :

[rag83]

Bonjour,

Je ne suis pas certain d'être dans la bonne section, ma question est la suivante : je voudrais convertir une feuille Excel en table Access de manière un peu plus poussée qu'habituellement.
Je sais importer une feuille basique, mais pour la table qu'on trouve ici

http://www.sosfichier.com/files/Codes-NAF.xlsx

j'aimerais pouvoir incorporer les rubriques et sous-rubriques en catégories, pour avoir une arborescence qui me permettra de filtrer ce que je veux par une ou plusieurs listes déroulantes.
Je me vois mal faire ça à la main
Est-ce qu'il y a un moyen de faire ça ?

Voila merci

[marot_r]

Bonjour je n'ai pas regarder ton fichier excel (je n'ai pas l'autorisation de le charger sur mon poste) mais voici un algorithme général pour ce genre de situation.

1. Importer la feuille telle qu'elle est dans une table Access. Probablement en s'assurant que tous les champs sont de type texte.
2. Après avec des requêtes (de regroupement probablement pour éviter les doublons) extraire les données qui doivent se retrouver dans des tables de références (ex : liste de produit).
3. Remplir ces tables avec les requêtes.
4. Créer la table principale avec le bon type pour chaque champ.
   Remplacer les champs qui font référence à des données dans les tables de référence par ClefNomTable.
   Ex : si tu as CodeProduit et NomProduit dans ta table principale tu auras ClefProduit puisque les autres infos peuvent être déduites de la clef en regardant dans table Produit.
5. Ajouter les relations entre la table principale et les tables de références.
6. Une fois les tables de référence crées en supposant une structure du type : Champ Clef (Atonum), Champ Valeur (type selon données, défini comme index unique pour plus de sécurité)
7. Faire une requête de remplissage de la table principale basée sur la table temporaire qui va pécher via des jointure à droite pour chacune des tables de référence la clef correspondant à la valeur.
8. Faire les conversions de types des données (ex : Texte à Date) si nécessaire.
9. Exécuter la requête de chargement principale.

A+

[rag83]

Bonjour René,

Merci pour tes explications mais je ne pense pas arriver à faire ce que tu me dis, ça me semble trop compliqué.

Il est dommage que tu ne puisses pas voir la feuille Excel car il n'y a que deux colonnes et je ne sais pas si tes explications peuvent s'appliquer dans ce cas.

Je pourrais peut-être me servir du nombre de caractères de la colonne A pour automatiser le processus mais aucune idée de comment faire si c'est possible.

Je vais continuer à essayer de comprendre.

A+

[marot_r]

Peux-tu poster une copie d'écran de tes données.

A+

[rag83]

Voila !



On ne voit ici que la section A mais il y en a d'autres, section B, section C, etc...

[marot_r]

Ok ça a l'air moins complexe que ce que j'avais imaginé.

Quelles sont les listes de référence que tu veux ?

Sinon pour découper des données de type texte tu peux utiliser

• Mid(Texte;Position;Longueur) :
  ex : Mid("ABCD";2;2) donne "BC",
  ex : Mid("ABCD";2) donne "BCD"
• Left(Texte;Longueur) :
  ex Left(ABCD;2) donne "AB",
• Right(Texte;Longueur) :
  ex Right("ABCD";2) donne "CD".
• Len(Texte) te donne la longueur du texte
  ex Len("ABCD") donne 4.
• Instr(Texte;TexteChercher) te donne la position de début du texte cherché
  Instr("ABCD";"BC") donne 2.

Note Access va traduire automatiquement Mid, Left, Right, Len et Instr en français mais j'avoue que je ne les connais que sous leur nom anglais.

A+

[rag83]

Je te remercie pour tes explications René mais ce que tu me dis va me permettre de faire ça dans une table ?

[marot_r]

Oui comme je l'ai décrit dans l'algo générique en quelques passes tu devrais pouvoir le faire.

Est-ce que tu dois faire l'importation une seule fois ou ce sera répétitif ?

De ce que je vois tu devrais avoir besoin

table temporaire d'imporation tblTemp

ClefTemp: Autonum (on peut s'en passer mais cela peut être pratique pour réordonner les données importées).
CodeTemp : Texte 255 caractères
LibTemp : Text 255 caractères

Une table des sections tblSection

ClefSection : Autonum
CodeSection : Texte 255 caractères
LibSection : Text 255 caractères

Une table des SousSections tblSousSection

ClefSousSection : Autonum
CodeSousSection : Texte 255 caractères
LibSousSection : Text 255 caractères

Une table des SousSousSections tblSousSousSection

ClefSousSousSection : Autonum
CodeSousSousSection : Texte 255 caractères
LibSousSousSection : Text 255 caractères

table des code APE tblCodeAPE

ClefCodeAPE : Autonum
CodeCodeAPE : Texte 255 caractères
LibCodeAPE : Texte 255 caractères
ClefSection : Entier long
ClefSousSection : Entier long
ClefSousSousSection : Entier long

En relation avec tblSection sur ClefSection
En relation avec tblSousSection sur ClefSousSection
En relation avec tblSousSousSection sur ClefSousSousSection

Pour avoir la liste des sections

Code sql :

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

1
2
select mid([tblTemp].[CodeTemp], 8) as [CodeImport], [tblTemp].[LibTemp] as [LibImport]  from [tblTemp].[CodeTemp]
group [tblTemp].[CodeTemp], [LibTemp] having [tblTemp].[codeTemp] like "SECTION*"

Toutes les sections contiennent le mot "SECTION"

Créer une requête d'ajout pour remplir tblSection prenant pour source cette requête.

Pour avoir la liste des sous-sections

Code sql :

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

1
2
select [tblTemp].[CodeTemp] as [CodeImport], [tblTemp].[LibTemp] as [LibImport] from [tblTemp].[CodeTemp]
group [tblTemp].[CodeTemp], [LibTemp] having [tblTemp].[codeTemp] like "??"

Toutes les sous-sections on un code à 2 chiffres

Créer une requête d'ajout pour remplir tblSousSection prenant pour source cette requête.

Pour avoir la liste des sous-sous-sections

Code sql :

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

1
2
select [tblTemp].[CodeTemp] as [CodeImport], [tblTemp].[LibTemp] as [LibImport] from [tblTemp].[CodeTemp]
group [tblTemp].[CodeTemp], [LibTemp] having ([tblTemp].[codeTemp] like "??.*" and not [tblTemp].[codeTemp] like "??.*Z")

Toutes les sous-sections on un code à 2 chiffres un point et des chiffres mais ne se terminent pas par Z.

Créer une requête d'ajout pour remplir tblSousSousSection prenant pour source cette requête.

Pour avoir la liste des APE :

Code sql :

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

select [tblTemp].[CodeTemp] as [CodeImport], [tblTemp].[LibTemp] as [LibImport], left([tblTemp].[CodeTemp], 2) as [CodeSousSection], left([tblTemp].[CodeTemp], len([tblTemp].[CodeTemp])-1) as [CodeSousSousSection]   from [tblTemp].[CodeTemp] where [tblTemp].[codeTemp] like "??.*Z"

Tous les code APE finissent par Z, le code de sous-section sont les 2 premiers chiffres et le code de sous-sous-section sont tous les chiffres sauf le Z.

Créer une requête d'ajout pour remplir tblCodeAPE prenant pour source cette requête et faisant des jointures sur les tables tblSousSection et tblSousSousSection pour trouver les clefs correpondantes.

Pour la section je n'ai pas d'astuce mais tu devrais pouvoir les ajouter à la main après si tu n'en a pas des tonnes.

A+

[marot_r]

Une idée pour la section, ajouter une colonne section dans le Excel.

À vrai dire tu pourrais faire toute la préparation pour l'importation dans Excel en ajoutant des colonnes

Ton Excel ressemblerai à cela :

[Colonne CodeSection], [Colonne CodeSousSection], [Colonne CodeSousSousSection], [Colonne CodeAPE], [Colonne Lib]

[Colonne CodeSection] : =SI(GAUCHE(D2;7)="SECTION";STXT(D2;9;255);A1).
[Colonne CodeSousSection] : =SI(NBCAR(D2)=2;D2;B1).
[Colonne CodeSousSousSection] : =SI(ET(NB.SI(D2;"??.*")=1;NB.SI(D2;"??.*Z")=0);D2;C1).

Note que sur les premières lignes cela donne des résultats inappropriés. Un petit ménage manuel sera nécessaire.

Évidement il faudra adapter les requêtes d'inventorisation des sections, sous-sections et sous-sous-sections et la table temporaire en conséquence mais cela va grandement simplifier ton remplissage de la table tblCodeAPE. Tu n'auras à plus à faire de jointure ni de calcul dans la requête de chargement des codes APE, juste à utiliser les données fournies par Excel.

Les requêtes d'inventorisation des codes seront plus simples aussi. Un simple group by sur le champ voulu et c'est fait.

A+

[rag83]

Je te remercie pour tes explications très détaillées René, je devrais pouvoir m'en sortir maintenant.

Une petite correction, les codes APE ne finissent pas tous par Z. On ne voit bien sur pas toutes les lignes sur le screenshot, et il y en a un bon paquet qui n'ont pas le Z à la fin (Exemple 10.13).

J'essaye de regarder ça dans le week-end et je te tiens au courant si je m'en sors ou pas !

A+

[Robert1957]

Bonjour rag83, René,

Une autre approche pour le même problème... juste différente

Une seule table comme suit:

Le champ "APE_Link" fait le lien entre les différents niveaux de la table.

Le traitement de l'importation se fait en parcourant ton fichier Excel avec le code suivant:

Premièrement pour récupérer le fichier:

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
Private Function Parcourir()
'Ici c'est un copier coller de l'aide d'Access
 
'Requires reference to Microsoft Office XX.X Object Library.
 
   Dim fDialog As Office.FileDialog
   Dim varFile As Variant
 
   'Set up the File Dialog.
   Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
   With fDialog
      'Allow user to not make multiple selections in dialog box.  J'ai changé car dans notre cas un seul fichier à la fois
      .AllowMultiSelect = False
 
      'Set the title of the dialog box.
      .Title = "Veuillez choisir un fichier"
 
      'Clear out the current filters, and add our own.
      .Filters.Clear
      .Filters.Add "Fichiers Excel", "*.XLS; *.XLSX"
      .Filters.Add "All Files", "*.*"
 
      'Show the dialog box. If the .Show method returns True, the
      'user picked at least one file. If the .Show method returns
      'False, the user clicked Cancel.
      If .Show = True Then
         'Loop through each file selected and add it to the list box.
        For Each varFile In .SelectedItems
            Parcourir = varFile
         Next
 
      Else
         MsgBox "Vous n'avez pas sélectionné de fichier!!!" & Chr(13) & "La procédure est annulée."
      End If
   End With
 
End Function

Ensuite pour insérer les données dans la table:

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
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
Private Sub btnImport_Click()
On Error GoTo Err_btnImport_Click
 
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim oApp As Excel.Application
    Dim oWkb As Excel.Workbook
    Dim oWSht As Excel.Worksheet
    Dim i As Long, loStop As Long, loLignePasVide As Long
    Dim strFeuille As String, strChemin As String, strColonne1 As String, strColonne2 As String
    Dim strLink As String, strSection As String, strNiveau2 As String, strNiveau3 As String
    'Inscrire le nom du fichier choisi et son chemin
    DoCmd.Hourglass True
    strChemin = Parcourir
    'Si aucun fichier choisi
    If strChemin = "" Then: Exit Sub
    'Le nom de ta feuille étant NAF j'ai inscrit directement dans le code.
    'Il serait possible de choisir le nom de la feuille et de le passer dans la variable
    strFeuille = "NAF"
    'Créer l'objet Excel
    Set oApp = CreateObject("excel.application")
    'Récupérer le fichier
    Set oWkb = oApp.Workbooks.Open(strChemin)
    'Récupérer la bonne feuille
    Set oWSht = oWkb.Worksheets(strFeuille)
    'Créer le record
    Set db = CurrentDb
    Set rst = db.OpenRecordset("SELECT T_Code_APE.APE_Link, T_Code_APE.Code_APE, T_Code_APE.Description FROM T_Code_APE;")
    'Première ligne d'importation à 3 selon ton fichier
    loLignePasVide = 0
    For i = 3 To 65536 'Nombre maximum de ligne d'une feuille Excel en version 32 bytes
    If oWSht.Range("A" & i).Value <> "" Then
        loStop = 0 'Remettre le stop de la boucle à 0
        loLignePasVide = loLignePasVide + 1
        'On ajoute une ligne premier niveau dans la table
        strColonne1 = Trim(oWSht.cells(i, 1))
        If Left(strColonne1, 7) = "SECTION" Then
            strLink = "0"
            strSection = strColonne1 'On inscrit ce qui fera le lien pour la section
            strColonne2 = oWSht.cells(i, 2)
        'On ajoute une ligne deuxième niveau dans la table
        ElseIf InStr(1, strColonne1, ".") = 0 Then
            strLink = strSection
            strNiveau2 = strColonne1 'On inscrit ce qui fera le lien pour le deuxième niveau
            strColonne2 = oWSht.cells(i, 2)
        'On ajoute une ligne troisième ou quatirème niveau
        ElseIf InStr(1, strColonne1, ".") > 0 Then
            'Ici troisième niveau
            If Len(Mid(oWSht.cells(i, 1), 3)) = 2 Then
                strLink = strNiveau2
                strNiveau3 = strColonne1 'On inscrit ce qui fera le lien pour le deuxième niveau
                strColonne2 = oWSht.cells(i, 2)
            'ici quatrième niveau
            Else
                strLink = strNiveau3
                strColonne2 = oWSht.cells(i, 2)
            End If
        End If
        rst.AddNew
           rst("APE_Link") = strLink
           rst("Code_APE") = strColonne1
           rst("Description") = strColonne2
        rst.Update
    Else
        loStop = loStop + 1
    End If
        If loStop = 4 Then: Exit For 'Si on a passé 5 lignes sans données on sort de la boucle
    Next i
    rst.Close
    Set rst = Nothing
    Set db = Nothing
    oWkb.Close
    Set oWSht = Nothing
    Set oWkb = Nothing
    Set oApp = Nothing
    DoCmd.Hourglass False
    MsgBox "Vous venez d'insérer " & loLignePasVide & " lignes dans la table T_Code_APE"
 
Exit_btnImport_Click:
 
    Exit Sub
 
Err_btnImport_Click:
    MsgBox Err.Description & "# erreur: " & Err.Number
    Resume Exit_btnImport_Click
End Sub

Puis le formulaire avec les listes déroulantes synchronisées:


Je joints la base exemple.

Bonne journée

[rag83]

Bonjour, bon et bien voila je me suis essayé aux 2 méthodes et je suis arrivé au résultat escompté, je vais maintenant pouvoir poursuivre !

Un grand merci à René et Robert pour leur aide !