Importer des données de plusieurs fichier CSV dans un classeur

Le 19/05/2020 à 17:12

Bonjour,

J'aurai besoin via une Macro d'importer les données de plusieurs fichiers (environ 70) entre les colonnes A à M des fichiers commençant par 186 pour ensuite les regrouper sur un classeur qui sert de base de données.

Actuellement j'y arrive pour faire un fichier mais c'est laborieux.

Je voudrais que la Macro charge ces données automatiquement pour tous les fichiers commençant par 186 et fasse une base de données pour ensuite avoir un BOARD de synthèse.

Ainsi, ouvrirai les fichiers les uns après les autres en copiant les colonnes A à M et en remplissant mon onglet formulaire, celui-ci créant un copie sur un onglet dans le classeur avec l’appellation du fichier.

Merci pour votre aide

29186328.zip (38.74 Ko)
29186329.zip (38.74 Ko)
Le 19/05/2020 à 21:29

Bonjour,

Un essai avec ceci ...

Sub importCSV()
Dim CH, Rep As FileDialog, F$, T() As String, D, debut As Integer

Set Rep = Application.FileDialog(msoFileDialogFolderPicker)
Application.FileDialog(msoFileDialogFolderPicker).Title = "Choix du répertoire des fichiers ..."
Rep.Show
If Rep.SelectedItems.Count = 0 Then Exit Sub
CH = Rep.SelectedItems(1) & "\"

If Not ActiveSheet.ListObjects(1).DataBodyRange Is Nothing Then ActiveSheet.ListObjects(1).DataBodyRange.Delete
Range("A12").Select

debut = 5
F = Dir(CH & "*.csv")
Do While F <> ""

    Open CH & F For Input As #1
        ligne = 1
        Do While Not EOF(1)
            Line Input #1, ContenuLigne
            T = Split(ContenuLigne, ";")
            nItems = UBound(T) + 1
            If ligne >= debut Then
                If ligne = debut Then ReDim D(1 To nItems, 1 To 1)
                If ligne > debut Then ReDim Preserve D(1 To nItems, 1 To ligne - debut + 1) ' Preserve ne permet de redimensionner que la dernière dimension d'un tableau avec conservation des données
                For i = 0 To UBound(T)
                    D(i + 1, ligne - debut + 1) = T(i)
                Next i
            End If
            ligne = ligne + 1
        Loop
    Close #1
    D = Application.Transpose(D)
    Selection.Resize(UBound(D), UBound(D, 2)) = D
    Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select

    F = Dir
Loop

End Sub

J'ai mis cette macro dans le module 8

Elle demandera à sélectionner le dossier où sont enregistrés les fichiers csv

Nota : il n'est pas utile d'avoir des lignes vierges pour un tableau. Lors de l'import des données, les formules seront actives (sauf pour les 3 dernières qu'il faudrait retravailler pour ne pas faire appel à une autre ligne du tableau !)

Le 19/05/2020 à 21:44

J'ai retravailler les formules pour qu'elles n'amènent pas un #REF! lors de la réinitialisation du tableau

32compteur-d-energie-br-reduit.xlsm (602.95 Ko)
Le 19/05/2020 à 22:16

Bonjour à tous

Avec 2016, pourquoi t'enquiquiner alors que PowerQuery fait cela très facilement ?

Ton filtre Pays me semble est faux : tu calcules sur la colonne et non la ligne du tnleau (manquent les @) et comparer du texte avec l'opérateur > n'est pas fiable

Par exemple +49.2418 +006.96848 correspond à l'Allemagne mais ta formule le situe en France

Tes lignes sans latitude ni longitude sauf attribuées à la France...

Du coup je n'obtiens pas les même totaux pour Le 16,7 Hz en France

Le 19/05/2020 à 22:21

C'est super !!!

Merci beaucoup.

Est-ce qu'il serait possible de pouvoir mettre dans une colonne le numéro de BR186 auxquelles les lignes appartiennent ?

Par exemple dans la colonne "O" appelée locomotives.

Cela me permettra de pouvoir différencier les données.

Le 19/05/2020 à 22:52
78chris a écrit :

Bonjour à tous

Avec 2016, pourquoi t'enquiquiner alors que PowerQuery fait cela très facilement ?

Ton filtre Pays me semble est faux : tu calcules sur la colonne et non la ligne du tnleau (manquent les @) et comparer du texte avec l'opérateur > n'est pas fiable

Par exemple +49.2418 +006.96848 correspond à l'Allemagne mais ta formule le situe en France

Tes lignes sans latitude ni longitude sauf attribuées à la France...

Du coup je n'obtiens pas les même totaux pour Le 16,7 Hz en France

C'est même Excel 365 (erreur de ma part)

Je ne connaissais pas PowerQuery.

Pour le filtre Pays, malheureusement je n'ai pas encore fini de la développer au niveau de la formule.

Je ne trouve aucun moyen de pouvoir faire ressortir un nom de Pays en fonction des coordonnées GPS.

J'arrive juste à faire un visu via 3D Maps, cenpendant j'ai besoin de pouvoir indiquer pour chaques coordonnées GPS le Pays associé

Le 20/05/2020 à 04:48

Je ne suis pas assez à l'aide avec PowerQuery pour t'aider dans cette voie, et puis il y a de vrais experts comme Chris (et d'autres).

Pour la version VBA, j'ai inclus la loco comme demande, et j'ai traité les dates correctement afin de ne pas laisser excel inverser les jours et les mois quand le jour est inférieur à 12 !

Sub importCSV()
Dim CH, Rep As FileDialog, F$, T() As String, D, debut As Integer

Set Rep = Application.FileDialog(msoFileDialogFolderPicker)
Application.FileDialog(msoFileDialogFolderPicker).Title = "Choix du répertoire des fichiers ..."
Rep.Show
If Rep.SelectedItems.Count = 0 Then Exit Sub
CH = Rep.SelectedItems(1) & "\"

If Not ActiveSheet.ListObjects(1).DataBodyRange Is Nothing Then ActiveSheet.ListObjects(1).DataBodyRange.Delete
Range("A12").Select
debut = 5
F = Dir(CH & "*.csv")
Do While F <> ""
If F Like "186*" Then
    Open CH & F For Input As #1
        ligne = 1
        Do While Not EOF(1)
            Line Input #1, ContenuLigne
            T = Split(ContenuLigne, ";")
            nItems = UBound(T) + 1
            If ligne >= debut Then
                If ligne = debut Then ReDim D(1 To nItems, 1 To 1)
                If ligne > debut Then ReDim Preserve D(1 To nItems, 1 To ligne - debut + 1) ' Preserve ne permet de redimensionner que la dernière dimension d'un tableau avec conservation des données
                For i = 0 To UBound(T)
                    If i = 0 Then
                        D(i + 1, ligne - debut + 1) = Mid(T(i), 4, 2) & "/" & Mid(T(i), 1, 2) & "/" & Mid(T(i), 7, 10)
                    Else
                        D(i + 1, ligne - debut + 1) = T(i)
                    End If
                Next i
            End If
            ligne = ligne + 1
        Loop
    Close #1
    D = Application.Transpose(D)
    Selection.Resize(UBound(D), UBound(D, 2)) = D
    Selection.Offset(0, 14).Resize(UBound(D)) = Replace(F, ".csv", "")
    Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
End If
F = Dir
Loop

End Sub
18compteur-d-energie-br-reduit.xlsm (20.34 Ko)
Le 20/05/2020 à 04:54

Question ... faut-il aussi importer le LEM Serial Number ?

Le 20/05/2020 à 08:48

Bonjour à tous

Mareq a écrit :

C'est même Excel 365 (erreur de ma part)

Je ne connaissais pas PowerQuery.

Pour le filtre Pays, malheureusement je n'ai pas encore fini de la développer au niveau de la formule.

Je ne trouve aucun moyen de pouvoir faire ressortir un nom de Pays en fonction des coordonnées GPS.

J'arrive juste à faire un visu via 3D Maps, cenpendant j'ai besoin de pouvoir indiquer pour chaques coordonnées GPS le Pays associé

J'attends avant de poster ma solution PowerQuery : elle n'utilise pas tes formules, et calcule directement, ce pourquoi il faut bien comprendre la logique de tes calculs.

Actuellement, tu as des lignes sans Voltage, sans Latitude, ou autres et néanmoins ta colonne pays donne une valeur...

Il faut que je retrouve, mais j'ai vu passer des fils où on récupere des données Google Map avec ou sans l'API.

Je pense qu'il faut effectivement calculer selon Latitude et Longitude et définir une règle plus fine quand des infos manquent...

Le 20/05/2020 à 10:15
Steelson a écrit :

Question ... faut-il aussi importer le LEM Serial Number ?

Non pas besoin c'est principalement le numéro en 186.

Superbe ta modification ca facilite les extractions de

78chris a écrit :

Bonjour à tous

Mareq a écrit :

C'est même Excel 365 (erreur de ma part)

Je ne connaissais pas PowerQuery.

Pour le filtre Pays, malheureusement je n'ai pas encore fini de la développer au niveau de la formule.

Je ne trouve aucun moyen de pouvoir faire ressortir un nom de Pays en fonction des coordonnées GPS.

J'arrive juste à faire un visu via 3D Maps, cenpendant j'ai besoin de pouvoir indiquer pour chaques coordonnées GPS le Pays associé

J'attends avant de poster ma solution PowerQuery : elle n'utilise pas tes formules, et calcule directement, ce pourquoi il faut bien comprendre la logique de tes calculs.

Actuellement, tu as des lignes sans Voltage, sans Latitude, ou autres et néanmoins ta colonne pays donne une valeur...

Il faut que je retrouve, mais j'ai vu passer des fils où on récupere des données Google Map avec ou sans l'API.

Je pense qu'il faut effectivement calculer selon Latitude et Longitude et définir une règle plus fine quand des infos manquent...

Actuellement il y a des lignes sans voltage car la machine doit être doit être à l'arrêt.

Pas de latitude et de longitude (la machine fait auto reset intempestif qui reset le GPS, du coup il faudrait pouvoir prendre la localisation précédente)

J'ai déjà lu plein de chose concernant les données Google Maps, mais il s'avère qu'apparemment Google a stoppé ou passer en service payant l'utilisation de son module API.

Merci à tous pour vos solutions, c'est très enrichissant pour un novice comme moi

Le 20/05/2020 à 11:28

mais poursuis l'exploration de PowerQuery qui est un outil intéressant !

Question @Chris : comment traites-tu la date avec PowerQuery ?

Le 20/05/2020 à 11:45

RE

Steelson a écrit :

Question @Chris : comment traites-tu la date avec PowerQuery ?

Plus précisément ? Tu parles de la valeur pour calculer l'écart entre J et J-1 ?

Sinon j'ai vu que tu as répondu sur des histoires de Google maps & autres : tu as pet-être un episte pour la pays lié au couple latitude, longitude ?

Le 20/05/2020 à 12:34
78chris a écrit :

RE

Steelson a écrit :

Question @Chris : comment traites-tu la date avec PowerQuery ?

Plus précisément ? Tu parles de la valeur pour calculer l'écart entre J et J-1 ?

Il y a des valeurs du type :

31/05/2019 23:55

et d'autres du type

10/05/2019 07:45

Dans le premier cas, excel importera au mieux le 31 mai, au pire une valeur textuelle qui sera 31/05/2019 23:55

Mais dans le second cas, excel lira le 5 octobre en inversant mois et jour puisque le premier nombre est inférieur ou égal à 12 !

J'ai pris le parti lors de l'import du fichier csv par VBA d'inverser mois et jour pour que l'interprétation par excel soit bien correcte.

Pour le lien entre lat/lon et pays ou plutôt fuseau horaire, je devais avoir une grosse base de données sous mysql exploitée en php. Il faudrait que je regarde !

Le 20/05/2020 à 13:26

Re

Les dates sont importées par PQ au format date et heure sans, me semble-t-il, de PB particulier

J'ai laissé les paramètres régionaux en Français, mais le test que j'ai fait en paramètres US pour voir si je pouvais récupérer directement latitude et longitude en numérique, posait problème sur la date (maintenant que j'y repense)...

Le 20/05/2020 à 15:29
78chris a écrit :

Je ne trouve aucun moyen de pouvoir faire ressortir un nom de Pays en fonction des coordonnées GPS.

J'arrive juste à faire un visu via 3D Maps, cenpendant j'ai besoin de pouvoir indiquer pour chaques coordonnées GPS le Pays associé

J'ai retrouvé ce que je faisais "jadis" en mysql et php : j'avais récupéré un fichier de 23000 villes mondiales avec leur coordonnées, région,pays et décalage horaire (mon but était de recaler le relevé des données GPS en heure UTC).

Je vais faire un extrait pour l'Europe ... sinon je n'arrive pas à le charger.

Cela peut-être une base de recherche du pays selon les coordonnées GPS en recherchant la ville la plus proche.

Le 20/05/2020 à 15:36

7000 villes européennes.

Il est écrit en html, donc les caractères diacritiques (et tous les caractères dont le code ascii est > 127) sont écrits en � mais on s'en fout car cette colonne n'est pas à utiliser.

13europe.zip (136.54 Ko)

Un extrait pour Belgique, France, Allemagne ...

13gps-villes-be-de-fr.xlsx (82.89 Ko)

Avec cela on doit pouvoir faire une recherche simple !

Le 20/05/2020 à 16:18

Détermination du pays (fait sur 4 villes frontalières)

Function pays(lat, lon)
    pays = "?"
    Data = Sheets("Europe").ListObjects(1).DataBodyRange.Value
    Min = 0
    For i = 1 To UBound(Data)
        If i = 1 Then
            Min = (lat - Data(i, 1)) ^ 2 + (lon - Data(i, 2)) ^ 2
            pays = Data(i, 3)
        Else
            prov = (lat - Data(i, 1)) ^ 2 + (lon - Data(i, 2)) ^ 2
            If prov < Min Then
                Min = prov
                pays = Data(i, 3)
            End If
        End If
    Next
End Function
12be-de-fr.xlsm (89.64 Ko)
Le 20/05/2020 à 21:57

Bonjour

Steelson

Sur 1477 coordonnées différentes dans le CSV ton fichier permet d'en situer 979

Les 498 autres peuvent être en France ou en Allemagne

Je je sais pas si on peut trouver une routine pour interroger ces 498 coordonnées...

Zone :

Latitude entre 48,5664 et 49,6438

Longitude entre 6,09331 et 8,54655

Le 20/05/2020 à 23:02
78chris a écrit :

Bonjour

Steelson

Sur 1477 coordonnées différentes dans le CSV ton fichier permet d'en situer 979

Les 498 autres peuvent être en France ou en Allemagne

Je je sais pas si on peut trouver une routine pour interroger ces 498 coordonnées...

Zone :

Latitude entre 48,5664 et 49,6438

Longitude entre 6,09331 et 8,54655

Exact tout est parfait, sauf que pour certaines coordonnées GPS, il nous situe dans le mauvais pays.

Le 21/05/2020 à 04:42

Ce n'est pas étonnant d'avoir des écarts car la routine cherche la ville référencée la plus proche !

Quel est le niveau d'incertitude obtenu par cette base de données ? et quelle est la tolérance pour ce que l'on veut faire ?

C'est clair que la géométrie des frontières est parfois fractale. A l'époque je n'avais pas trouvé mieux et la précision me suffisait.

Pour obtenir une plus grande précision, il faudrait affiner les villages proches des frontières, et s'en tenir à ces seules valeurs (car les autres villes éloignées des frontières ne nous intéressent pas). Notamment sur l'axe Strasbourg-Karlsruhe.

D'ailleurs, une solution toute simple consisterait à introduire les villages de Lauterbourg, Munchhausen, Seltz, Beinheim, Roppenheim, Stattmatten, Drussenheim, Offendorf, Gambsheim.

Refaites l'essai avec cette nouvelle base affinée côté français le long de la ligne :

12be-de-fr-bis.xlsm (90.93 Ko)

Autre solution, prendre le tracé des frontières sur

ou

et refaire une routine. Un excellent article de notre ami Pierrep56 :

Edit : voir page suivante !

Rechercher des sujets similaires à "importer donnees fichier csv classeur"