Dans les 2 fichiers que tu donnes, dans la cellule BJ4, je m'attends à trouver « TimeAverage » et j'ai « IO.Signal » => ce qui fait planter mon algorithme.
D'où vient cette anomalie ?
Dans les 2 fichiers que tu donnes, dans la cellule BJ4, je m'attends à trouver « TimeAverage » et j'ai « IO.Signal » => ce qui fait planter mon algorithme.
D'où vient cette anomalie ?
SVP ne m'envoyez pas de messages privés pour poser des questions techniques, vous n'aurez pas de réponse !
Bonjour,
Voici une routine qui importe le fichier .xls d’une installation pour alimenter la table à 3 colonnes tMesures.
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 Option Compare Database Option Explicit Public Sub ImportXLS(Fichier As String) Dim i As Integer Dim otbl As TableDef Dim odb As Database Dim sSQL As String DoCmd.SetWarnings False Debug.Print Now() 'Supprimer l'ancienne tTransit DoCmd.DeleteObject acTable, "tTransit" 'Importation DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tTransit", Fichier, True, "A4:DP485" ' liste des capteurs Set odb = CurrentDb Set otbl = odb.TableDefs("tTransit") For i = 0 To otbl.Fields.Count - 1 If otbl.Fields(i).Name Like "*:Control Module" Then sSQL = "INSERT INTO tMesures ( Capteur, Moment, Mesure ) " _ & "SELECT """ & Left(otbl.Fields(i).Name, Len(otbl.Fields(i).Name) - 15) & """ AS Expr1, " _ & "[" & otbl.Fields(i).Name & "],Timeaverage" & IIf(i / 3 = 0, "", i / 3) & " FROM tTransit;" DoCmd.RunSQL sSQL End If Next i Sortir: Set otbl = Nothing Set odb = Nothing Debug.Print Now() DoCmd.SetWarnings True End Sub
L’idée
1re étape
(ligne 15) On importe le fichier .xls dans une table tTransit (renouvelée à chaque exécution), Access, recrée une table qui a cette composition :
Remarquez le nom attribué par Access aux colonnes qui contiennent les moments dans le fichier Excel : il se compose du nom du capteur suivi de 15 caractères « :Control Module ».
Les colonnes contenant les mesures sont appelées « Timeaverage » pour le premier capteur et « Timeaveragei » pour les 39 suivants.
2e étape
(lignes 20 à 29) Exécution de 40 requêtes écrites à la volée pour « pomper » les 20 trios de colonnes.
SVP ne m'envoyez pas de messages privés pour poser des questions techniques, vous n'aurez pas de réponse !
Je vois pas dans le code, la notion de répertoire, càd le programme il va vers chaque fichier excel selon un critère ( le nom par exemple ) pour l'importer, Aussi, s'il y a plusieurs feuilles, comment il va savoir qu'il s'agit de la feuille "Hourly log Calc" ?
Si je comprends bien votre code: vous importez toutes les colonnes dans la table Ttransite, donc pour chaque installation, il y une table de 40*3 =120 champs !!
avec cette conception, tu trouves pas que c'est la même chose que créer une table sur access avec 120 champs et utiliser le bouton d'importation classique ?!
Je remarque aussi, que vous utilsez un pas de 3 pour avoir les mesure, je veux plus d'explication sur cette partie de code servant à pomper les valeurs dans la table trio, et où est cette table?
Ce que je montre (pour la critique éventuelle des collègues qui suivraient la discussion) c'est la routine qui sera appelée autant de fois que de fichiers xls. dans le répertoire.Je ne vois pas dans le code, la notion de répertoire
Pas besoin de cette notion puisque le nom du capteur suffit pour retrouver l'installation. (Un capteur appartient à une et une seule installation).s'il y a plusieurs feuilles, comment il va savoir qu'il s'agit de la feuille "Hourly log Calc"
Non, il n'y a qu'une seule table tTransit qui est supprimée et recrée à chaque exécution.Si je comprends bien votre code: vous importez toutes les colonnes dans la table Ttransit, donc pour chaque installation, il y une table de 40*3 =120 champs !!
Si ça te plait davantage, tu peux tenter l'expérience...avec cette conception, tu ne trouves pas que c'est la même chose que créer une table sur Access avec 120 champs et utiliser le bouton d'importation classique ?!
i ne varie pas par pas de 3, mais, par défaut, par pas de 1 !Je remarque aussi, que vous utilisez un pas de 3 pour avoir les mesures, je voudrais plus d'explication sur cette partie de code servant à pomper les valeurs dans la table trio,
Quand on connait le i qui correspond à la position d'une colonne avec des moments, il suffit de diviser i par 3 pour construire le nom de la colonne qui contient les mesures correspondantes : TimeAverage suivi du quotient (sauf pour le 1er (i/3 vaut alors 0 ! et la colonne s’appelle TimeAverage tout court).
Je rédigerai un texte explicatif plus détaillé plus tard quand on saura que la solution est viable.
C’est « la » table (tMesures), bien sûr !... et où est cette table ?
As-tu la main pour corriger à la source ?Je crois, c'est une erreur de frappe au niveau de fichier original du système qui génère les données, vous pouvez la changer par TimeAverage.
--------
L’import des données d’une installation dure moins d’une seconde, mais au fur et à mesure que la table grossit, ça risque de se détériorer : il faudrait tester avec des données de plusieurs jours et de toutes les installations (pour intégrer tous les capteurs), pour confirmer que la solution tient la route.
--------
Où en es-tu avec la correspondance Capteur => Installation ?
SVP ne m'envoyez pas de messages privés pour poser des questions techniques, vous n'aurez pas de réponse !
Merci Claude pour tes efforts, j'essayerai de bien analyser cette solution.
cet après midi je vais vous transmettre une table (Installation-Capteur) pour 15 installations.
Je crée un bouton sur un petit formulaire pour tester ce code ?
C'est quoi la démarche pour le tester? tu m'excuses, car chui pas trop habitué avec le VB ACCESS.
Entre temps, ché po si t'as pris en considération le cas ou il n y a pas des colonnes affectées par des capteurs, là ou ça génère ABBOPCDA...., ceci fo po l'intégrer dans la table, car ça ne contient aucune mesure.
En d'autre mots: Le nombre max de capteurs par fichiers excel et 40, mais il se peut avoir moins que 40.
La colonne dont l'intitulé est mauvais.As-tu la main pour corriger à la source ?
Le plus simple, serait que tu me fasses parvenir les fichiers de toutes les installations pour 2 ou 3 jours. Cela me permettra aussi de voir si on est capable de stocker deux mois de données.C'est quoi la démarche pour le tester?
Celles-là sont rejetées ainsi que celles qui mentionnent une erreur. (Je suppose que c'est le système en amont qui donne l'alerte dans ce cas.)Entretemps, je ne sais pas si t'as pris en considération le cas où il n'y a pas des colonnes affectées par des capteurs
SVP ne m'envoyez pas de messages privés pour poser des questions techniques, vous n'aurez pas de réponse !
Très simplement : puisque la valeur n'a pas le format d'une date, la mise à jour de la table est refusée.
SVP ne m'envoyez pas de messages privés pour poser des questions techniques, vous n'aurez pas de réponse !
Claude, je t'ai envoyé un ZIP des installations sur ton email.
J'attend ton code explicité avec des commentaires.
Bonjour,
Un peu de patience : ici, il fait beau temps et comme c'est pas aussi souvent que chez toi, j'en profite !
SVP ne m'envoyez pas de messages privés pour poser des questions techniques, vous n'aurez pas de réponse !
Les 15 fichiers que tu m’as transmis ont tous une erreur en BJ4 (différent de « Timeaverage »).
Dans la procédure actuelle, j’ai ajouté une routine qui corrige cette anomalie.
Son exécution dure 17 sec pour les 15 fichiers.
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 Public Sub CorrigerBJ4() Dim obFSO As Scripting.FileSystemObject Dim obRep As Scripting.Folder Dim obFichier As Scripting.File Dim xlApp As Excel.Application Dim xlSheet As Excel.Worksheet Dim xlBook As Excel.Workbook Dim dtDepart As Date dtDepart = Time 'Initialiser les variables Set xlApp = CreateObject("Excel.Application") Set obFSO = New Scripting.FileSystemObject Set obRep = obFSO.GetFolder(CurrentProject.Path & "\Fichiers") 'Lire chaque fichier du répertoire pour corriger l'erreur en BJ4 For Each obFichier In obRep.Files If Right(obFichier.Name, 3) <> "xls" Then GoTo AuSuivant Set xlBook = xlApp.Workbooks.Open(CurrentProject.Path & "\Fichiers\" & obFichier.Name) Set xlSheet = xlApp.Sheets("Hourly Log Calc") xlSheet.Cells(4, 62) = "Timeaverage" '4,62 pour BJ4 ! BJ =>62e colonne : 2*26 + 10 xlBook.Close True AuSuivant: Next obFichier Debug.Print "durée de la correction " & CDate(Time - dtDepart) Sortir: Set xlSheet = Nothing Set xlBook = Nothing Set xlApp = Nothing Set obFSO = Nothing Set obRep = Nothing End Sub
L’import dans tMesures des 15 fichiers (220.000 mesures) demande maximum 15 sec.
Cette durée ne semble pas être influencée par le nombre d’enregistrements déjà contenus dans tMesures. (J’ai testé avec 15 millions de mesures déjà présentes !)
Pour ces 15 fichiers, il y a 463 capteurs.
Avec 15 millions d’enregistrements (soit 68 jours !) le db occupe 1 Go.
Les requêtes pour sélectionner des échantillons s’exécutent en 2 sec.
Seul bémol avec 15 millions d’enregistrements, la fermeture de la db (avec compactage) dure 3 minutes.
Voici le code qui réalise la correction et l’import des fichiers présents dans le sous-répertoire « fichiers ».
Et la routine pour importer 1 fichier (légèrement modifiée) :
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 Public Sub ImportTousLesFichiers() On Error GoTo GestionErreur Dim obFSO As Scripting.FileSystemObject Dim obRep As Scripting.Folder Dim obFichier As Scripting.File Dim dtDepart As Date 'Initialiser les variables Set obFSO = New Scripting.FileSystemObject Set obRep = obFSO.GetFolder(CurrentProject.Path & "\Fichiers") 'Lire chaque fichier du répertoire pour corriger l'erreur en BJ4 Call CorrigerBJ4 'Importation dtDepart = Time For Each obFichier In obRep.Files Call ImportXLS(CurrentProject.Path & "\Fichiers\" & obFichier.Name) DoCmd.DeleteObject acTable, "A4:DP485_ImportErrors" 'Colonnes ABBGetOPCHDA: Invalid log name Next obFichier Debug.Print "durée de l'import " & CDate(Time - dtDepart) MsgBox "Import terminé durée : " & CDate(Time - dtDepart) GestionErreur: Select Case Err.Number Case 0 ' pas d'erreur Exit Sub Case 7874 'A4:DP485_ImportErrors n'existe pas Resume Next Case Else MsgBox "Erreur dans Sub ImportTousLesFichiers " & Err.Number & " " & Err.Description End Select End Sub
Pour que tu puisses tester :
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 Public Sub ImportXLS(Fichier As String) On Error GoTo GestionErreur Dim i As Integer Dim otbl As TableDef Dim odb As Database Dim sSQL As String DoCmd.SetWarnings False 'Supprimer l'ancienne tTransit DoCmd.DeleteObject acTable, "tTransit" 'Importation DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tTransit", Fichier, True, "A4:DP485" ' liste des capteurs Set odb = CurrentDb Set otbl = odb.TableDefs("tTransit") For i = 0 To otbl.Fields.Count - 1 If otbl.Fields(i).Name Like "*:Control Module" Then sSQL = "INSERT INTO tMesures ( Capteur, Moment, Mesure ) " _ & "SELECT """ & Left(otbl.Fields(i).Name, Len(otbl.Fields(i).Name) - 15) & """ AS Expr1, " _ & "[" & otbl.Fields(i).Name & "],Timeaverage" & IIf(i / 3 = 0, "", i / 3) & " FROM tTransit;" DoCmd.RunSQL sSQL End If Next i DoCmd.DeleteObject acTable, "tTransit" Sortir: Set otbl = Nothing Set odb = Nothing DoCmd.SetWarnings True GestionErreur: Select Case Err.Number Case 0 ' pas d'erreur Exit Sub Case 7874 'tTransit" n'existe pas Resume Next Case Else MsgBox "Erreur dans Sub ImportTousLesFichiers " & Err.Number & " " & Err.Description End Select
- télécharge la db ici : http://claudeleloup.developpez.com/&99/Moudden.mdb ;
- tu la loges dans n’importe quel répertoire ;
- dans ce répertoire, tu crées un sous-répertoire appelé « fichiers » dans lequel tu loges tes fichiers .xls (autant que tu veux : ceux d’un mois complet si ça te chante !) ;
- quand c’est prêt, tu cliques le bouton « Importer » et tu attends le message de fin :
SVP ne m'envoyez pas de messages privés pour poser des questions techniques, vous n'aurez pas de réponse !
Une idée à creuser
Conserver les fichiers .xls originaux dans un répertoire jusqu’au moment où tu estimes qu’ils ne te seront plus utiles.
Quand tu décides de faire une analyse, un formulaire permet, en fonction de tes choix du moment (Installation(s), capteur(s), plage horaire…), Access copie les fichiers originaux nécessaires dans le répertoire « fichiers » et lance le processus de chargement de la table tMesures (préalablement vidangée).
=> la table tMesures ne contiendrait alors que les données indispensables à la confection de ton analyse ponctuelle.
SVP ne m'envoyez pas de messages privés pour poser des questions techniques, vous n'aurez pas de réponse !
Bonjour Claude;
J'obtiens ces de messages d'erreur lors de l'exécution.
Il ne reconnait pas la table tTransit, comme s'elle n'est pas déclarée.
Pour infos, sur l'ordi actuel j'ai un access 2013.
Comme tu le sais déjà, chaque jour j'ai mes 15 fichiers que je ramène depuis le système, il y a des formules dans les excel qui permettent d'extraire les données depuis le serveur, alors une fois que j'ai les fichiers sur mon ordi, ils perdent les valeurs après quelques jours, car il n y a pas de connexion avec le serveur, tu px le constater on ouvrant les fichiers aujourd'hui. du cout, chaque jour dans mon répertoire fichiers (dossier nommé fichiers) je colle les 15 nouveaux fichiers excel et je supprime les anciens, je crois que la table tMesure garde les enregistrements, c'est ça?
Lorsque j'ai les fihciers de 3 jours par exemple, savhant que j'ai déjà enregistré celui de la 1er et la 2eme journée, est ce que la base va ajouter encore une fois les même données?!
Pour ton billet N° 55, c'est comme si tu n'avais pas téléchargé la dernière version du programme !
Oublie temporairement Mon billet N° 54.
Dans la version actuelle, tMesures garde l'historique de tous les fichiers présentés à l'importation.
SVP ne m'envoyez pas de messages privés pour poser des questions techniques, vous n'aurez pas de réponse !
As-tu téléchargé la nouvelle version ? Il y a des modifications subtiles et elles concernent précisément les deux messages que tu montres.
Je suis désolé, mais je ne serai plus disponible à partir de 10:30 jusqu'à ce soir.
SVP ne m'envoyez pas de messages privés pour poser des questions techniques, vous n'aurez pas de réponse !
Vous avez un bloqueur de publicités installé.
Le Club Developpez.com n'affiche que des publicités IT, discrètes et non intrusives.
Afin que nous puissions continuer à vous fournir gratuitement du contenu de qualité, merci de nous soutenir en désactivant votre bloqueur de publicités sur Developpez.com.
Partager