Fichiers de données volumineux

[Davidc57]

Bonjour,

Je dois travailler avec des fichiers csv de plus d'1 million de ligne et près de 80 champs. Un csv fait environ 800 Mo. C'est beaucoup trop gros et je dois faire des contrôles en tout genre sur chaque champ pour valider les fichiers. Egalement je dois songer aux personnes qui vont ensuite devoir contrôler ces données.

Pas moyen d'importer un csv dans Access car ça dépasse largement les 2Go. En liant le csv, ça marche mais je vous raconte pas la lenteur d'exécution des requêtes de contrôle.

Question 1 : Comment faire pour gérer des fichiers de données gigantesques avec des petits moyens svp ?

Question 2 : Vu les dimensions des csv, par lot de combien de lignes découperiez-vous les csv pour avoir un volume supportable afin de pouvoir exécuter des contrôles dans une perspective Access d'une part (contrôle par exécution de requêtes successives) et dans une perspective Excel d'autre part (contrôle par formules et/ou vba - fonctions personnelles) ?

Je précise que je vais devoir traiter environ 80 fichiers csv. Alors si je dois divise chaque csv par 10 je vais devoir lancer le programme de contrôle 800 fois. Si je découpe en 100 je vais devoir lancer le programme de contrôle 8000 fois. Je préfère lancer 8000 fois un programmes très rapide que 800 fois un programme très lent (plus de 10 fois trop lent par rapport au premier

Je ferai bien des tests mais mon programme de contrôle n'est pas encore créer car justement je ne sais pas encore quel techno choisir. Dans Access, par exemple, je peux faire un programme qui va boucler sur les 800 csv (ou 8000), changeant la liaison et lançant le contrôle par requête. Dans Excel, je peux par exemple créer un masque de contrôle par formule que j'enregistre dans un classeur, et je change la source en bouclant sur tous les fichiers csv. Exemple, en Feuille1 la source à contrôler (import csv), en feuille 2 les résultats automatiques du contrôle par un jeu de formules. Et je boucle sur les fichiers csv ...

J'en appelle donc à vos retours d'expériences sur Excel et sur Access pour traiter des gros volumes.

Dans tous les cas je vais boucler mais globalement vous feriez cela où ?

Merci d'avance,
David

 

[poulpor78]

Re : Fichiers de données volumineux

Re,

Je vois que ça progresse en passant par le pénible 'Afficher le sous-total'.

concernant VBA, sur ce que j'ai lu et dans mes souvenirs, donc à corriger si besoin :

- on ne peut manipuler Powerpivot, si ce n'est mettre à jour les données
- on peut certainement créer des tcd via vba, chose que je n'ai pas faite
- de mon coté, j'ai créé du code que permet de modifier le filtre d'un TCD plat (et même modifier un filtre multi-selection, ce qui a été une galère absolue).


Mais revenons à ton problème. Personnellement, je déteste le tcd. Moins j'en ai et mieux je me porte. Donc si on peut résoudre le problème avec un seul tcd, ce serait cool.

Ce que je propose :

1/ tu fais tes 80 colonnes de test dans un ordre précis. Ton résultat sera "0" ou "1" (les lettres, pas les chiffres)
2/ tu fais une 81 ème colonne qui est la concaténation des 80 premières
3/ Tu fais un tcd où tu vas sortir toutes les lignes qui sont différentes de "000000000000000.....00" sur le champ de concaténation, en imaginant que cette liste devrait être raisonnable de par sa taille.

Et comme tu as tous les positifs dans excel, tu peux bosser dessus via excel / VBA en t'évitant 80 TCD.



Après, je n'ai pas bien compris ce que tu voulais exactement :tu veux une liste par type d'anomalie ? tu veux juste une liste des lignes qui comportent au moins une erreur ? Quand une ligne a deux anomalies, tu veux deux lignes de sortie ?

Poulpor

PS : si tu restes sur ton idée des 80 TCD, j'utiliserais la fonction de duplication d'un onglet d'un premier TCD et j'irais juste modifier le nouveau TCD.

 

[poulpor78]

Re : Fichiers de données volumineux

Je n'ai pas répondu aux autres questions.

Aujourd'hui, ma petite expérience sur Powerpivot est une liaison à une base SQL d'un ERP. En gros, Powerpivot travaille en local et je mets à jour mes données quand je le souhaite (il esxiste des options de MAJ automatique je crois).
J'imagine donc que le fonctionnement est le même pour un csv : on change le csv source, on met à jour la connexion powerpivot.

Powerpivot est fait pour ça.


A noter une chose, ça pourra te donner des idées : il est possible dans Powerpivot :

- d'importer plusieurs tables et de les lier (on peut donc faire facilement des sortes de 'somme.si' ou 'recherchev')

- d'importer des tables depuis excel => c'est pour moi très utile. Dans ton exemple, on peut imaginer créer une table des codes erreurs dans excel et l'envoyer dans Powerpivot...même si je ne saurais pas l'exploiter dans le cas où on a plusieurs erreurs sur la même ligne; c'est juste pour l'exemple.
L'idée est que le travail effectué dans Powerpivot est beaucoup lus rapide que dans excel => autant essayer de traiter la problématique au maximum dans powerpivot et juste d'extraire le résultat dans excel au final.

Poulpor

 

[poulpor78]

Re : Fichiers de données volumineux

Pour finir :

une idée me vient en tête : je ne vois pas comment exploiter l'idée de concatener; pas sûr donc que ce soit la bonne voie.

Par contre, il n'est pas interdit de faire un seul TCD plat où on met 80 colonnes (autant qu'il y a de tests). Tu remarqueras que dans le TCD aplati, tu peux faire glisser le champ de test dans 'VALEURS' mais également dans 'LIGNES'.

Si tu fais glisser le champ dans la partie 'LIGNES' du 'TCD', tu pourras filtrer sur le champ; ce qui n'est pas le cas si tu le fais glisser dans 'valeurs'.

Une méthode pourrait être de ne créer qu'un seul TCD où tu mets tes 80 colonnes dans la partie 'LIGNES' (attention : utiliser la case à cocher 'différer la mise à jour de la disposition'+ il faudra bien enlever ce satané 'Afficher le sous-total')

Ensuite, tu peux faire un code :

- qui va filtrer les positifs sur ton premier test, puis aller copier les données filtrées sur une feuille avec un code erreur
- qui va enlever le filtre du tcd
- qui va filtrer les positifs sur ton second test, puis aller copier les données filtrées sur une feuille avec un code erreur, juste en dessous de la première partie
- ...


Il y a certainement mieux. Ma difficulté personnelle étant mon manque de dextérité sur les TCD. Je ne vois pas comment faire apparaître plusieurs lignes, si, dans ton tableau powerpivot, il y a plusieurs erreurs sur la même ligne.

Un spécialiste des TCD pourrait certainement débloquer la situation.

Poulpor

 

[Davidc57]

Re : Fichiers de données volumineux

Merci pour tes réponses.

Je vais devoir relire à tête reposé mais tu m'as donné des idées. j'aime l'histoire du 81è champ qui concatène les 0 et les 1. J'ajouterai même un ";" entre chaque 0 et 1. Comem cela j'en fait un csv et les 0 et 1 correspondent à l'ordre de mes champs ..... ca devient exploitable.

Moi aussi les TCD j'ai du mal ;-)


"Dans ton exemple, on peut imaginer créer une table des codes erreurs dans excel et l'envoyer dans Powerpivot...même si je ne saurais pas l'exploiter dans le cas où on a plusieurs erreurs sur la même ligne; c'est juste pour l'exemple."
-> Pas certain d'avoir saisi. L'idée est de demander à Power Pivot d'écrire dans cette table des erreurs ? Cela est-il possible ?


Oui si il y a deux erreurs sur la même ligne alors j'attends deux anomalies signalées sur deux lignes différentes dans mon gros rapport final de contrôle.

"concernant VBA, sur ce que j'ai lu et dans mes souvenirs, donc à corriger si besoin :

- on ne peut manipuler Powerpivot, si ce n'est mettre à jour les données
- on peut certainement créer des tcd via vba, chose que je n'ai pas faite
- de mon coté, j'ai créé du code que permet de modifier le filtre d'un TCD plat (et même modifier un filtre multi-selection, ce qui a été une galère absolue)."

En fait en VBA je ne pensais pas à contrôler PowerPivot mais ... à contrôler les fichiers sources et à contrôler les TCD qui eux sont dans Excel. L'idée c'est de changer la source (csv), rafraichir PowerPivot et les TCD et normalement c'est gagné !?!
Je ne pense même pas avoir besoin de modifier les TCD (filtre ou titre) car ils seront fait une fois pour toute à la main et fi;ltreront le code erreur (donc valeur 1). A moi de m'arranger dans Power Pivot pour que mes formules DAX renvoient soit 0 si c'est ok soit 1 si c'est une anomalie.




" Une méthode pourrait être de ne créer qu'un seul TCD où tu mets tes 80 colonnes dans la partie 'LIGNES' (attention : utiliser la case à cocher 'différer la mise à jour de la disposition'+ il faudra bien enlever ce satané 'Afficher le sous-total')

Ensuite, tu peux faire un code :

- qui va filtrer les positifs sur ton premier test, puis aller copier les données filtrées sur une feuille avec un code erreur
- qui va enlever le filtre du tcd
- qui va filtrer les positifs sur ton second test, puis aller copier les données filtrées sur une feuille avec un code erreur, juste en dessous de la première partie
- ...
"

Oui en effet, c'est le principe que je pensais utiliser mais en utilisant une feuille par TCD je n'ai pas besoin de toucher aux filtres des TCD (les filtres des positifs étant déjà mis au départ). Bon ca ferait beaucoup d'onglet ... Il me suffirait de "Rafraichir TOUT" puis de scanner les résultats de chaque TCD de chaque onglet !


Bon je vais relire cela ce soir après le dîner ...

 

[Davidc57]

Re : Fichiers de données volumineux

Re

"
Ce que je propose :

1/ tu fais tes 80 colonnes de test dans un ordre précis. Ton résultat sera "0" ou "1" (les lettres, pas les chiffres)
2/ tu fais une 81 ème colonne qui est la concaténation des 80 premières
3/ Tu fais un tcd où tu vas sortir toutes les lignes qui sont différentes de "000000000000000.....00" sur le champ de concaténation, en imaginant que cette liste devrait être raisonnable de par sa taille.

Et comme tu as tous les positifs dans excel, tu peux bosser dessus via excel / VBA en t'évitant 80 TCD.
"
--> Waw je vois c'est puissant ! A mettre en oeuvre après ... je débute et j'ai pas trop le temps d'en comprendre tout les tenants et les aboutissants. Le DAX là c'est quand même un langage particulier. Ce n'est pas intuitif (à part un IF). J'aimerai bien pouvoir y utilise des fonctions perso développés en vba :-(
Comment faire pour vérifier si un ID est unique dans une colonne, en DAX ?
Comment faire pour vérifier qu'une valeur dans un champ fait bien partie d'une liste prédéfinie dans une table, en DAX ?
Bref avant de parvenir à la situation idéale, c'est-à-dire cette suite 80 petit 0 et 1, la matrice des anomalies, il me faudrait surmonter beaucoup trop de problème si seul le DAX permet d'exploiter les données côté PowerPivot .... je veux dire il n'y a pas de système de requêtage, ...


"
Après, je n'ai pas bien compris ce que tu voulais exactement :tu veux une liste par type d'anomalie ? tu veux juste une liste des lignes qui comportent au moins une erreur ? Quand une ligne a deux anomalies, tu veux deux lignes de sortie ?
"
--> Au niveau de l'exploitation des résultats. Il y aura deux types de cas d'anomalie. Les cas où toute la colonne est en anomalie (donc non respect des spec techniques par l'émetteur du csv) et alors là je ne tiens pas à lister tous les cas d'anomalie mais une juste une alerte GRAVE sur le champ en question. Et il y a les cas où seulement quelques enregistrements sont en erreur car il y aura des interventions manuelles de la part d'opérateurs divers avant la création du csv par l'émetteur, pour faire des surcharges, des transcos, ...

"
PS : si tu restes sur ton idée des 80 TCD, j'utiliserais la fonction de duplication d'un onglet d'un premier TCD et j'irais juste modifier le nouveau TCD.
"
--> Oui merci du conseil ! Je ne l'exclu pas car c'est la seule que je conçois pour le moment.

A+

 

[poulpor78]

Re : Fichiers de données volumineux

Re,


Ok pour ton idée des 80 tcd. De toute façon, je suis très mal placé pour te guider sur la sortie en tcd, étant mauvais dans ce domaine.



Par contre, je peux t'aider à débuter dans Powerpivot .

" Le DAX là c'est quand même un langage particulier. Ce n'est pas intuitif (à part un IF). J'aimerai bien pouvoir y utilise des fonctions perso développés en vba :-(
Comment faire pour vérifier si un ID est unique dans une colonne, en DAX ?
Comment faire pour vérifier qu'une valeur dans un champ fait bien partie d'une liste prédéfinie dans une table, en DAX ?"


Au niveau des fonctions personnalisées VBA dans Powerpivot, je n'en sais rien. J'ai quand même du mal à croire que ce soit possible. Maintenant, je ne vois pas ce qui bloque avec l'utilisation des fonctions DAX.


Si cela peut t'aider, voici comment aller un peu plus loin dans Powerpivot.

---------------------------------
Comment faire ?

Déjà, le mieux est de commencer par importer ta liste prédéfinie dans powerpivot (depuis excel ,depuis un autre csv, depuis une autre source).

Tu obtiens une table dans Powerpivot => direction la partie diagramme en cliquant sur l'icone en bas à droite. Tu crées une relation entre les deux tables, un peu comme dans access.

Mon exemple de recherchev() :

J'avais dans ma table d'origine qui comportait des N° de Business Unit (BU) mais il me fallait obtenir des noms compréhensibles.

=> je me suis créé une table nommée 'BU' avec deux colonnes : le N° et le nom de BU. J'ai fait un lien dans la partie diagramme sur le N° de BU entre les deux tables. Et j'ai tapé cette formule :

=related(BU[Name]) => cette formule m'a ramené le nom de ma BU à partir de la liste

Tu remarqueras qu'à la différence de recherchev(), on ne précise pas le champ de recherche. En effet, le champ de recherche est celui du lien entre les deux tables.


Mon exemple de somme.si() :

J'ai deux tables liées : 'Job' et 'Job_Entry'
Job : la définition d'un projet (date, nom du client, ...)
Job_Entry: les lignes de facturation et de coût de chaque job (projet)

Evidemment, je souhaite faire des sommes de valeurs dans ma table job pour en sortir des analyses. Le lien entre les deux tables est l'identifiant de projet.


Dans Job, j'ai créé l'équivalent d'un somme.si : =CALCULATE(sum(Job_Entry[My_Amount2]);Job_Entry[My_Type_lv4]="ASS TEAM LEADER")+0

Là, j'ai calculé la somme de la colonne 'My_Amount2' de la table 'Job_Entry', avec la condition que le champ My_Type_Lv4="ASS TEAM LEADER".

En gros, je souhaitais calculer dans la table Job, la somme des coûts salariaux d'une catégorie d'employé par projet.

C'est donc bien un somme.si. Tu remarqueras la souplesse de la fonction : le premier argument est le calcul désiré, le second est le filtre.

Je m'aperçois que je n'ai pratiquement utilisé que ces deux fonctions, agrémenté de '+', CONCATENATE et IF...comme souvent sur excel.

Quant à nb.si, je repartirais de mon équivalent de somme.si en remplaçant sum par count.

---------------------------

Ce pdf mémo d'une page devrait t'aider : http://powerpivot-info.com/public_files/cheat-sheet/powerpivot-dax-functions-short.pdf


Il est vrai que le web n'évoque pas encore trop souvent Powerpivot. J'ai galéré de bonnes heures sur cet outil pour obtenir des fonctions toutes simples. Mais je pense que ça vaut le coup d'investir dans Powerpivot, MS souhaitant que ce soit l'avenir.

Poulpor

 

[Davidc57]

Re : Fichiers de données volumineux

Bonjour le forum, bonjour Poulpor,

Merci encore une fois pour ta réponse qui m'apporte beaucoup pour moi-même . Car je pense bien explorer PowerPivot à l'avenir. Je crois que c'est un outil révolutionnaire, end-users, et que donc les gens pour qui je bosse habituellement (des ends-users côté métiers) et qui abusent déjà des TCD vont forcément apprécier cet outil et l'utiliser dans un futur proche. DOnc si je veux pouvoir continuer de les aider, je vais devoir certainemetn apprendre à l'utiliser.

Pour revenir à mon cas, j'ai laissé tomber PowerPivot (par manque de connaissances et peut-êter aussi car ce n'est peut-être pas le meilleur outil pour faier des contrôles. Si c'est bien un outil End-Users alors il a été pensé en considérant que les sources de données sont "propres". Il est certainement très efficace pour ce qu'il sait faire (traiter des gros volumes, afficher de l'information aggrégée, ..) mais à priori pas pour du contrôle.... Enfin à confirmer par un pro de PowerPivot !

Je suis donc revenu au fondamentaux .... un petit programme Excel :
Découpage des csv (via un batch file) par paquet de 50000 lignes
Boucle sur les csv découpé
Je rapatrie les données d'un csv en feuille 1 (copié-collé)
En feuille 2 j'ai un jeu de formule dans excel pour tester la case "vis-à-vis" de la feuille 1. J'obtiens ma matrice de 0 et de 1.
En feuille 3, je compte le nomber d'erreur par champ (colonne) et je synthétise les résultats.

Ca marche bien, c'est très rapide à mette en oeuvre, et très rapide à l'exécution à condition de :
Les formules de la feuille sont prépositionnées uniquement sur la première ligne. Et le programem après avoir copié collé le CSV en feuille 1 va déployer les formules dans la feuille 2. AInsi mon programme excel prend une place ridicule sur le réseau (quelques Ko) alosr que je déploie d'avance les formules la tailles passe à plusieurs Mo). Ce principe assure la rapidité d'exécution.

Le seul hic c'est qu'il y a un type de contrôle que je ne peux pas faire : Les contrôle du type, l'ID est-il unique dans le fichier ! Comme j'ai découpé les csv je ne peux plus le faire. Mais devant l'urgence de la situation, on m'a donné le feu vert pour déveloper cet outil de contrôle car c'est mieux que rien ! Ma DL est pour milieu de semaine prochaine !!!

Merci