Fichiers de données volumineux

Davidc57

XLDnaute Occasionnel
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
 

Victor21

XLDnaute Barbatruc
Re : Fichiers de données volumineux

Bonsoir, Davidc57.

Je vois que vous travaillez sur Excel2003. Pour information, les dernières versions d'Excel accueillent maintenant 1048576 lignes sur 16384 colonnes par page.
Peut-être serait-il intéressant de savoir quels types de contrôles vous devrez effectuer...
 

Davidc57

XLDnaute Occasionnel
Re : Fichiers de données volumineux

Bonsoir Patrick,

Ma fiche n'est pas à jour :-( Je suis sous Office 2010 à la maison et 2007 au travail.

Les contrôles portent sur les contenus des csv, chaque enregistrement, champ par champ. Je dois vérifier si il est au format attendu (date, numérique, ... ), parfois la valeur est issue d'une liste de choix et je dois vérifier que c'est le cas, parfois elle est obligatoire parfois non, parfois elle doit être dans une langue parfois dans une autre, parfois elle dois être une chaîne de caractère d'une certaine longueur, ...

David
 

Victor21

XLDnaute Barbatruc
Re : Fichiers de données volumineux

Re,

Mis à part l'appartenance à une langue (sauf si les termes utilisés appartiennent à une liste pré-définie), Excel saura exécuter tous ces contrôles.
Quant aux ressources nécessaires, et au temps de calcul, j'avoue ne pas avoir l'expérience d'un fichier aussi volumineux pour pouvoir vous renseigner.
Je ne doute pas que nos amis VBAistes prendront bientôt le relai pour vous conseiller plus avant :)
 

Davidc57

XLDnaute Occasionnel
Re : Fichiers de données volumineux

Re,

Bon vu les volumes, j'optais de prime abord pour une base de données mais comme Access rame et que je dois certainement découper mes csv en petits bouts, alors la question d'excel se pose de nouveau.
 

poulpor78

XLDnaute Junior
Re : Fichiers de données volumineux

Bonjour,

J'ai passé pas mal de temps sur Powerpivot ces derniers temps.

Powerpivot est un add-in d'excel (dispo dès Excel 2010), qui est est un gros conteneur de données. Et sur ce que j'ai lu (mais pas testé), Powerpivot permet de travailler sur des millions de lignes.

On se retrouve alors avec deux univers :
- l'univers Powerpivot où on travaille les données - on parle même de modèle de données (on peut importer des tables et les mettre en relation, on peut créer des colonnes calulées)
- l'univers Excel qui accède aux données de Powerpivot par tableau croisé dynamique


En pratique, on peut importer les lignes du csv dans Powerpivot (on peut même filtrer quelles lignes et quelles colonnes on veut importer, moins il y a de données, plus powerpivot répondra vite). Cela va donner un tableau gigantesque - tant mieux, Powerpivot a été créé pour cela.

Dans l'interface Powervivot, on peut donc créer des colonnes calculées, qui contiendraient les tests de validité de la ligne. Ces formules sont en DAX: pour les formules simples, ça ressemble à des formules excel en anglais.

On pourrait alors interroger dans excel les positifs au test de validité par le biais d'un tableau croisé.


Poulpor
 

pyfux

XLDnaute Occasionnel
Re : Fichiers de données volumineux

Bonjour,

Le choix de la techno pour ce type de sujet repose essentiellement sur vous...
Si vous maîtrisez le VBA, je ne vois pas de limite. Tout est faisable.

Concernant le temps de traitement, il faudra dédier une machine qui moulinera le tout en 24h, 48h...

Par contre je serais intéresse de connaitre les tenants et aboutissant de votre démarche. C'est rare de devoir manipuler hors base de données des centaines de milliers de lignes de données...

Courage.

Note: SQL Serveur Express et requêtes SQL dans Excel ? :)
 

Davidc57

XLDnaute Occasionnel
Re : Fichiers de données volumineux

Oui poulpor, Powerpivot est une solution qui semble appropriée et de ce que j'ai pu en voir il serait très rapide.... mais il n'est pas capable de travailler avec des sources de types csv !?! Alors si je dois transformer mes sources avant de les passer dans Powerpivot je vais avoir le même problème.

Pourquoi je me prend la tête avec tout ça ? Parce que je suis censé centraliser des données (pour un usage unique) qui proviennent de filiales de ma société présentes dans le monde entier. Le format csv a été imposé pour les réponses des filiales. J'intervient à partir de là :-(

David
 

poulpor78

XLDnaute Junior
Re : Fichiers de données volumineux

Re,

Effectivement, Powerpivot est fait pour travailler sur un maximum de sources de données. Je suis donc étonné de cet oubli.

Je viens d'ouvrir Powerpivot (attention, je suis sur 2013 qui est différent du Powerpivot sur 2010). Si je fais : Obtenir des données à partir d'autres sources, je descends tout en bas : il est proposé fichier plat. Dans la boîte de dialogue, *.txt est proposé par défaut mais je peux choisir *.csv.

Poulpor
 

Davidc57

XLDnaute Occasionnel
Re : Fichiers de données volumineux

Il me reste à apprendre à utiliser PowerPivot. Je l'ai installé chez moi. J'arrive à capter des csv en effet. Demain je test en situation réelle avec les vrais csv.

Je vais devoir apprendre à utiliser ce bel outil.

Merci pour votre aide
 

poulpor78

XLDnaute Junior
Re : Fichiers de données volumineux

Excellent choix. Tu vas voir, c'est sympathique de se mettre dans une nouvelle voie. Franchement, ce n'est pas difficile dans la mesure où MS a fait attention à ne pas trop nous dérouter.

Pour s'y mettre rapidement :

1/ Savoir importer une source (tu l'as déjà fait)

2/ Savoir lier des tables dans le modèle (ça ne te concerne pas puisque tes tests ne se font que sur une table)

3/ Utiliser les formules. Lors de l'import, il y a des colonnes vides à droite : on peut les utiliser pour créer des champs personnalisés à l'aide de formules. La synthaxe est très proche d'Excel. En ce qui te concerne, j'imagine que tu veux faire des tests sur des champs.

On ne tape qu'une fois la formule pour toute la colonne : la validation de la formule prend donc parfois un peu de temps


Premières formules :

='All Sales'[Amount]

Le résultat est égal au champ Amount de la table All Sales

=[Amount]*1.10

Le résultat est égal au champ Amount * 1,10 de la table où on tape la formule


Pour la suite, voici un pdf d'aide sur les formules : http://powerpivot-info.com/public_files/cheat-sheet/powerpivot-dax-functions-short.pdf


Enfin, pour exporter le tout, je te conseille de tester le tableau croisé dynamique plat.


poulpor
 

Davidc57

XLDnaute Occasionnel
Re : Fichiers de données volumineux

Bonjour,

Alors mes premiers tests sont intéressants. En terme de performance rien à redire. En terme dfe prise en main, ca va aussi c'est assez intuitif.

En revanche l'élément bloquant avant que je puisse confirmer à mes boss que je peux lm'en sortir avec cette techno est le suivant :

Une fois que j'ai défini un nouveau champ calculé avec formule DAX pour contrôler un des champs de ma source comment je peux exploiter tout cela pour lister les différentes anomalies détectées ?

Je simplifie ma question. A chaque champs de ma source va correspondre un (ou peut-êter plus) champ de contrôle. Admettons que je rédige ma formule de telle sorte que si le résultats vaut 1 alors c'est une anomalie sinon c'est bon. Visuellement je peux filter tour à tour sur chacun de mes nouveaux champs de contrôle pour avoir al liste des anomalies. Mais de manière automatique comment je peux faire ? Je vais avoir 80 champs de contrôle minimum, rempli de 0 et de 1 !

Est-il possible depuis VBA de contrôler PowerPivot ? Si oui avez-vous des liens pour m'aider à démarrer ?
PowerPivot renferme t'il des solutions que je n'imagine même pas et qui pourrait m'aider ? (dans un contexte d'automatisation de ces contrôles de masse)

Merci
 

Davidc57

XLDnaute Occasionnel
Re : Fichiers de données volumineux

Bon je progresse ! J'ai testé les TCD plat ^^

En effet, c'est excellent.

Je peux dans mon classeur excel, définir autant de TCD plat que j'ai de champs de contrôle et chaque fois filtrer sur les anomalies (exemple valeur = 1).

Je comprends maintenant comment je peux organiser toute la mécanique pour utiliser à la fois la puissance de power pivot, et VBA dans Excel pour contrôler mes TCD plats.

J'ai d'autres petits soucis à régler par exemple. Quand dans un TCD plat je met plus de 1 colonnes, alors j'aperçois des lignes "TOTAL" qui viennent parasiter mes résultats d'ensemble. Peut-on jouer sur une option/paramètre pour ne pas avoir ces totaux ?
 

Davidc57

XLDnaute Occasionnel
Re : Fichiers de données volumineux

Je me répond à moi-même :

Pour ne pas afficher les sous-totaux dans le TCD, ca se passe dans le menu contextuel clic bouton droit sur la case du titre. puis il faut décocher "Sous-totaux ....."

Bon et bien je pense que je n'ai plus d'obstacle !

Je pense donc faire comme ceci (pour un fichier csv) :
1. Créer 80 champs calculés avec des formules DAX pour avoir un 0 si c'est bon et un 1 si c'est une anomalie.
2. Créer 80 TCD plat dans 80 onglets diférents d'excel (si ca ne dépasse pas la limite !!!!!?????!!!!!) et paramétrer manuellement ces TCD avec les filtres sur champ calculé = 1, les colonnes à afficher, les sous-totaux à désactiver, ...
3. Faire un programme VBA qui parcourt chaque onglet excel (boucle) et va analyser les TCD et les anomalies qu'ils listent.
4. Copier ces anomalies dans un nouveau classeur

Il me reste à ajouter une boucle sur l'ensemble de mes fichiers csv à vérifier et ca devrait le faire. Normalement si j'ai bien compris Power Pivot et d'après mes tests, il suffit de remplacer la source csv dans le répertoire et de rafraichir Power Pivot et c'est gagné. Pas besoin de refaire les laisons, ni les TCD.

Poulpor, à votre avis c'est jouable tel que je présente les choses ?

Un grand merci dans tous les cas.

David
 

Discussions similaires

Statistiques des forums

Discussions
311 709
Messages
2 081 754
Membres
101 812
dernier inscrit
trufu