Gros volume de données

Bonjour,

Je travaille sur un fichier qui fait 85 MO.

Je souhaiterais intégrer un très gros volume de données (tables de références) : environ 30 millions de lignes, sur 6 colonnes environ. Le plan est d'appeler ces données avec BDLIRE().

J'ai testé sur 600.000 lignes : le poids de mon fichier augmente de 6MO.

Après un produit en croix, j'estime le poids de monfichier après import des données à 1GO+85MO !!!

Je bosse avec excel 2007 avec cette machine : Intel Core i5-4440 CPU @ 3.1GHz, 16MO RAM, Windows 10, 64 bits OS, x64 processor

Quelqu'un a-t-il déjà travaillé avec de si gros fichiers excel?

Cela vous semble-t-il jouable de bosser avec de tels fichiers?

Sachant que je devrais avoir 6 versions de ce fichier ouvertes en permanence sur mon ordi, et fonctionnement séparément en temps réel.

Stocker ces données dans une BDD acess vous semble-t-il + rapide à l'utilisation?

Merci,

Bonjour,

J'ignore si ce que vous demandez est faisable. Je peux cependant vous donner une piste, suite à une expérience personnelle.

Je travaille depuis quelques semaines avec un certain volume de données.

Mes tableaux se répartissent sur plusieurs colonnes mais dépassent rarement les 300K lignes.

Dans votre post vous parlez de dizaines, voire centaines de millions de lignes sur 6 colonnes. Or, à ma connaissance, vous ne pouvez pas dépasser le million de ligne (1,068 millions très exactement?).

La principale difficulté à laquelle j'ai été confronté fut qu'à chaque manipulation des tables de données, fut ce qu'un simple filtre ou l'application d'une fonction logique dans excel, prenait environ 30 à 40 minutes à se finaliser, les 4 processeurs tournant à fond.

Ma compo n'est certes pas aussi évoluée que la votre en terme de RAM, mais tout de même x64, W10, i5, 5300U, CPU@2.30Ghz, 8GO RAM.

C'était tellement compliqué de travailler dans cette situation qu'on ma conseillé de passer sur PowerBI, permettant de gérer des volumes plus conséquents. Ce que j'ai fait. Depuis, le retraitement des données est beaucoup plus rapide.

D'autres outils, répondant à des besoins particuliers, pourraient s'avérer utiles dans votre cas, tel qu'ACL par exemple.

Pincho a écrit :

Dans votre post vous parlez de dizaines, voire centaines de millions de lignes sur 6 colonnes. Or, à ma connaissance, vous ne pouvez pas dépasser le million de ligne (1,068 millions très exactement?).

Bonjour Pincho,

Merci de ta réponse,

Je pense stocker les données soit sur plusieurs feuillets, soit par tranches de 6 colonnes dans le même feuillet.

Je n'ai pas de filtre à réaliser sur ces doonées : elle servent just à être appelées comme valeur de référence par certains algos de mon fichier.

Pour ce qui est de PowerBi, je ne connais pas, mais le coeur de mon fichier doit rester sous excel. Les données, en revanche pourraient être stockées en externe si la solution est rapide pour une utilisation en temps réel (pas possible de dépasser 3 secondes de calcul).

Si t'arrives à traiter tes calculs en excel, avec autant de données, soient-elles divisées dans plusieurs feuilles ou non, ta solution m'intéresserait vraiment. Malheureusement je ne pense pas qu'il soit possible avec un tel volume de données, sauf si t'utilises une machine configurée pour ce type d'utilisation. Les PC de nos IT ont une configuration similaire à la tienne, avec 16GO de RAM, i7, etc. Malheureusement l'utilisation de ces données, appelées via une formule, macro, algorithme, nécessite quasi autant de temps que sur mon PC. J'ignore si excel permet d'indexer les valeurs, ce qui augmenterait le temps de traitement significativement, mais je ne suis pas suffisamment calé pour comprendre/utiliser cela. Il se peut également que le temps de traitement que j'évoque est le résultat d'un nombre de colonnes largement supérieur à ce que tu mentionnes. Mais dans tous les cas, traiter des centaines de millions de valeurs, en 3 secondes, ca me semble compliqué. Mais il y a peut-être moyen. Des avis plus propices pourront t'être fournis par les experts du forum.

PowerBI est un outil permettant de gérer un large volume de données. Son utilisation, de ce que j'en fais, est vraiment similaire à une base de données Accès. Les résultats générés peuvent être exportés en Excel.

Bonjour à vous deux,

Je pense comme pincho aux dernières évolutions de excel avec power pivot / Bi. Mais je crois que tu bosses sous 2007 ?!

Du coup, je sais pas si tu vas pouvoir l'utiliser...

Voici un article qui traite du sujet.

C'est vraiment l’idéal dans ton cas.

@+

Bonjour

Sauf à utiliser Office 64 bits, Excel n'utilise que 4GO de mémoire, réduits par l'occupation de l'OS et autres logiciels actifs donc sachant qu'Excel ne travaille qu'en mémoire vive...

Découper des millions de lignes en colonnes me parait difficilement exploitable, mais tu n'indiques pas le type de formule que tu utilise.

Outre le volume de données il faudrait avoir une idée des traitements pour donner une piste sérieuse...

Outre la version Excel, je ne crois pas que PowerBI fonctionne sans serveur, en autonome sur un PC.

nonesofar13 a écrit :

Bonjour à vous deux,

Je pense comme pincho aux dernières évolutions de excel avec power pivot / Bi. Mais je crois que tu bosses sous 2007 ?!

Du coup, je sais pas si tu vas pouvoir l'utiliser...

Voici un article qui traite du sujet.

C'est vraiment l’idéal dans ton cas.

@+

Bonjour Nonesofar13,

Merci de ta réponse.

... ce qui impliquerait de passer à une version + récente d'excel. Pourquoi pas? Même si je crois qu'elles sont + lentes que 2007.

Je vais y réfléchir.


78chris a écrit :

Découper des millions de lignes en colonnes me parait difficilement exploitable, mais tu n'indiques pas le type de formule que tu utilise.

Outre le volume de données il faudrait avoir une idée des traitements pour donner une piste sérieuse...

Bonjour 78chris,

Ce sont des données brutes. Il n'y a pas de calcul, ni formule.

Bonjour à tous et toute

Ma pierre à l'édifice:

Dans le cas de millions de lignes (de toutes façons impossible à importer sur une seule feuille) il serait certainement préférable d'avoir les données à plat type csv ou directement txt, et ne récupérer que les données nécessaires par un traitement, voir effectuer des calculs directement dans le code et ne restituer que le nécessaire.

Dans un classeur on doit avoir tout ce qui est nécessaire et uniquement ce qui est nécessaire.

Après tu pourrais te tourner vers acces et créer des requêtes comme source de données de ton classeur.

Cordialement

Efgé a écrit :

Bonjour à tous et toute

Ma pierre à l'édifice:

Dans le cas de millions de lignes (de toutes façons impossible à importer sur une seule feuille) il serait certainement préférable d'avoir les données à plat type csv ou directement txt, et ne récupérer que les données nécessaires par un traitement, voir effectuer des calculs directement dans le code et ne restituer que le nécessaire.

Dans un classeur on doit avoir tout ce qui est nécessaire et uniquement ce qui est nécessaire.

Après tu pourrais te tourner vers acces et créer des requêtes comme source de données de ton classeur.

Cordialement

Bonjour Efgé,

Merci d'apporter ta pierre

Moi, ça me va très bien de garder les données sous csv ou txt, mais comment les appeler sous excel lorsque j'en ai besoin?

Re

Par une macro du type:

Sub Test()
Dim Texte1 As String, Ligne As String
Dim X As Integer

Texte1 = "C:\Users\Desktop\Test.txt" 'exemple de chemin complet
X = FreeFile()
Open Texte1 For Input As #X
    Do While Not EOF(X)
        Input #X, ligne
        'Ligne est la ligne lu séquentiellement,
        'à partir de là tu peux la manipuler
    Loop
Close #X
End Sub

Cela te permet un traitement ligne à ligne sans ouvrir le fichier txt et de ne conserver que les données utiles, voir faire des calculs le cas échéant.

Cordialement

bonjour,

[Efgé]

Tu peux développer cette solution avec le fichier joint SVP :

afficher les valeur1, valeur2, valeur3 correspondant à l'iD : 322vQJt (par exemple)

Merci.

25table1.zip (4.33 Ko)

Re

Bonjpur galopin01

Ton fichier texte présente effectivement une bizarrerie... un caractère spécial qui gêne la lecture. (je n'ai pas trouvé lequel, si quelqu'un a une idée je suis preneur )

On peux faire ainsi (Fonctionne sur ton exemple en sachant qu'il n'y a qu'une lige répondant au critère)

Sub Test()
Dim Texte1$, Ligne$, tmp As Variant, Critere$
Dim X%, i&
Dim Flag As Boolean
Critere = "322vQJt"
Texte1 = "C:\Users\Desktop\Test.txt" 'exemple de chemin complet
X = FreeFile()
Open Texte1 For Input As #X
    Do While Not EOF(X)
        Input #X, Ligne
        tmp = Split(Ligne, ";")
        With ActiveSheet ' a adapter
            If tmp(0) = Critere Then
                Flag = True
                i = i + 1
                .Cells(i, 1).FormulaLocal = tmp(0)
            Else
                If Flag = True Then
                    tmp = Split(Ligne, ";")
                    .Cells(i, 2).FormulaLocal = tmp(1)
                    .Cells(i, 3).FormulaLocal = tmp(2)
                    Flag = False
                End If
            End If
        End With
    Loop
Close #X
End Sub

Cordialement

Bonsoir,

Ce fichier est un Export standard d'une table ACCESS 2010 vers .txt

Bon je vais creuser un peu, je reviens vers toi si nécessaire.

Merci.

[Edit] Ça marche bien : Vu la tronche de la boucle If... Else... End if

Je suppose que ce qui gêne c'est la présence des guillemets dans l'ID car ce montage est carrément acrobatique !!!

Je vais pousser le débogage à donf et voir si on peut pas y faire plus rationnel.

Après il restera à voir avec l'intéressé si ce type de requête est plus rapide qu'une requête SQL...

A+

Finalement ça se confirme : J'ai modifié l'export d'ACCESS sans guillemets et ça passe comme une lettre à la poste.

Je donne donc ici ton code modifié dans un xlsm test et le fichier Texte modifié sans guillemets...

J'aimerai bien savoir quel est le rapport temps d'exécution de cette méthode par rapport à une requête SQL équivalente.

A tester sur un million de ligne au moins parce que sur 100 lignes je me doute bien que le résultat n'est pas significatif...

Merci encore.

21table1.zip (4.22 Ko)
25requeteverstxt.xlsm (19.93 Ko)

Merci, de mon côté, je crois que je vais utiliser access.

Bonjour le fil, le forum

Je pense que nicopat ne m'en voudras pas de continuer à utiliser sa question

à Galopin01

Une proposition d’amélioration de ton dernier code:

Tu ne sortiras jamais avec le exit do (quand tu arrives sur la condition flag est a False).

Il Semble que la recherche s’effectue sur des clés uniques on peux donc sortir dès que la valeur à été trouvée

Inutile de recréer Tmp dans la condition.

Comme nous sommes sur la feuille qui porte le bouton, on peux se passer de With Activesheet

Au cas ou la recherche ne renvoie rien,ne pas laisser les résultats précédents

Sub Test_3()
Dim Texte1$, Ligne$, tmp As Variant, Critere$
Dim X%

Critere = [C2]
[C4:C6].ClearContents
Texte1 = ThisWorkbook.Path & "\" & "Table1.txt"
X = FreeFile()
Open Texte1 For Input As #X

Do While Not EOF(X)
    Input #X, Ligne
    tmp = Split(Ligne, ";")
    If tmp(0) = Critere Then
        [C4].FormulaLocal = tmp(1)
        [C5].FormulaLocal = tmp(2)
        [C6].FormulaLocal = tmp(3)
        Exit Do
    End If
Loop
Close #X
End Sub

Cordialement

Rechercher des sujets similaires à "gros volume donnees"