XL 2019 Fichier ".txt" de plus de 2.000.000 de lignes !

Titof06

Titof06

XLDnaute Junior
Bonjour,

J'ai à traiter un fichier texte de plus de 2.000.000 de lignes.

Excel n'accepte qu'environ 1.500.000 lignes.

Je dois extraire des informations relatives à des mises à jour de fournisseurs et par rapport à une date précise.

Est-ce que quelqu'un pourrait me conseiller un logiciel ou une façon de sortir des informations de ce méga fichier texte, svp ?

Par avance, Merci 👍

Bonne Journée, Titof06 :)
 
Trier par date Trier par votes
oguruma

oguruma

XLDnaute Occasionnel
Cousinhub à dit:
Hi,
Le seul AV, c'est Windows Defender
Et j'utilise FireFox...
Par contre, avec Chrome, pas de soucis...
J'ai un tout petit peu regardé les données, et je ne vois pas de champ "Date"
Or il me semble (j'ai lu en diagonale le roman) que l'objet de la question est une recherche de différence de prix entre 2 dates...
Cliquez pour agrandir...
Oui en effet mais il en est ressorti dans les discussions le sujet des Perfs en ce qui concerne le chargement d'un fichier .csv quel qu'il soit sous Excel (Powerpivot dans le modèle de données).
La question à se poser a-t-on réellement besoin d'avoir 1 045 000 lignes dans une feuille de calculs Excel sachant qu'il très probable qu'elle ne soit jamais lue....
 
Vote positif 0 Vote négatif
oguruma

oguruma

XLDnaute Occasionnel
oguruma à dit:
Oui en effet mais il en est ressorti dans les discussions le sujet des Perfs en ce qui concerne le chargement d'un fichier .csv quel qu'il soit sous Excel (Powerpivot dans le modèle de données).
La question à se poser a-t-on réellement besoin d'avoir 1 045 000 lignes dans une feuille de calculs Excel sachant qu'il très probable qu'elle ne soit jamais lue....
Cliquez pour agrandir...
Bon aller pour le fun je prépare une solution intégrée en pwq avec un fichier contenant des dates et une sélection entre début et fin.... du reste ça servira d'utilitaire ou tout au moins s'en inspirer pour ceux qui voudraient faire le pas sur pwq
 
Vote positif 0 Vote négatif
oguruma

oguruma

XLDnaute Occasionnel
Lien du fichier de données : https://www.cjoint.com/c/NFsr1O3EglK
Lien du fichier Excel / PowerQuery/Pivot : https://www.cjoint.com/c/NFssaOjETEK

Tout ceci mérite quelques explications sur le fonctionnement

Concernant le fichier de données j'ai livré un fichier de 149 000 lignes qu'il faudra dupliquer les lignes via notepad++ par exemple pour atteindre les 2 000 000 de lignes

Le pilotage de l'importation se fait par ce tableau de paramètres
1718733860179.png


Le code de la requête de chargement :
PowerQuery: 
let
    FileName=fnGetParameters("TB_PARAMS","FULLFILEPATHNAME"), 
    FileEncoding=fnGetParameters("TB_PARAMS","FILEENCODING"),
    DateDeb=fnGetRangeValue("DATE_DEB"),
    DateFin=fnGetRangeValue("DATE_FIN"),
    ToDateDateDeb=#date(Date.Year(DateDeb),Date.Month(DateDeb),Date.Day(DateDeb)),
    ToDateDateFin=#date(Date.Year(DateFin),Date.Month(DateFin),Date.Day(DateFin)),
    Source = Csv.Document(File.Contents(FileName),[Delimiter=";", Columns=null, Encoding=FileEncoding, QuoteStyle=QuoteStyle.None]),
    #"En-têtes promus" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Type modifié" = Table.TransformColumnTypes(#"En-têtes promus",{{"date", type date}}),
    //#"Lignes filtrées" = Table.SelectRows(#"Type modifié", each [date] >= #date(2005, 1, 1) and [date] <= #date(2010, 12, 31))
    #"Lignes filtrées" = Table.SelectRows(#"Type modifié", each [date] >= ToDateDateDeb and [date] <= ToDateDateFin)
in
    #"Lignes filtrées"

A vous par conséquent d'aligner le paramétrage par rapport à votre environnement (localisation du fichier de données)

puis ensuite saisir les dates
1718733984112.png


Activer l'importation à l'issue de votre paramétrage :
1718734465891.png


Mes Perfs :
Date de fin : 31/12/2020 selon ma duplication donne 1 874 000 environ, Excel vous prévient à l'issue de l'importation qu'il ne pourra pas tout charger. Donc 1 045 000 présentes. 55 secondes environ... sur mon pc un I7 et 8 Go de mémoire

Date de fin 31/12/2010 : 40 secondes

Certes, les Perf sont un peu plus longues... certainement aussi sur vos PC mais cette solution a le gros avantage : ZERO VBA ==> maintenance relativement facile, solution non propritétaire, tout se règle via les paramètres dans une table, juste un peu d'investissement en Pwq...

Voir le code....

Les paramètres de chargement
1718734220708.png


==> Noter que les donnée sont
1718734241634.png
donc elles figurent dans PowerPivot
On pourrait aussi se contenter de cette option
1718734277144.png
et utiliser les données via des TCD ou des KPI en langage DAX pour calculer des mesures etc etc


Je vous livre en annexe les deux fonctions utilitaires qui m'ont permis de gérer les paramètres. Elles ont été aussi livrées à maintes reprises dans le forum Trucs & Astuces

PowerQuery: 
let fnGetParameters =
    (pTable as text, pName as any) =>
    let
        ParamSource = Excel.CurrentWorkbook(){[Name=pTable]}[Content],
        ParamRow = Table.SelectRows(ParamSource, each ([PARAMETRE] = pName)),
        Value=
        if Table.IsEmpty(ParamRow)=true
            then null
        else Record.Field(ParamRow{0},"VALEUR")
    in
        Value
in
    fnGetParameters
  
  
let fnGetRangeValue = (
            pRangeName as text,
            optional pRow as number,
            optional pColumn as number
        ) =>
    
        let
            //***********************************************************************************************************
            // Gestion des paramètres
            //***********************************************************************************************************
            RangeName=pRangeName,
            Row=if pRow is null or pRow = 0 then 0 else pRow - 1,
            Column=if pColumn is null or pColumn = 0 then 1 else pColumn,
            WbExcel=Excel.CurrentWorkbook(),

            //***********************************************************************************************************
            // Construction de la liste des objets connus dans Excel
            //***********************************************************************************************************
            TbList=WbExcel[Name],
            bFound=List.Contains(TbList,RangeName),

            //***********************************************************************************************************
            // La recherche est uniquement effectuée si le champ est trouvé dans la liste des objets connus dans Excel
            //***********************************************************************************************************
            Value=if bFound
                      then
                            let
                              //--------------------------------------------------------
                              // Traitements de recherches si le nom de champ est connu
                              //--------------------------------------------------------
                              StrRange="WbExcel{[Name=RangeName]}[Content]{Row}[Column" & Text.From(Column) & "]",
                              RecEval=[WbExcel=WbExcel, Row=Row, Column=Column, RangeName=RangeName],
                              Eval=Expression.Evaluate(StrRange,RecEval)
                            in
                              Eval
                      else
                            "#N/A"
 
        in
            Value
in
    fnGetRangeValue
 
Dernière édition:
Vote positif 0 Vote négatif
oguruma

oguruma

XLDnaute Occasionnel
laurent950 à dit:
Bonsoir @oguruma

Sur 2 millions de lignes en récupérer 1 874 000 environ il n y a pas trop d intérêt
Se serait bien d en récupérer moins pour avoir un vrais résultat exploitable.
Cliquez pour agrandir...
C'était juste un exemple de tests de perfs donc uniquement technique pour évaluer le fait de traiter une grosse volumétrie.
Comme je le martèle depuis le début dans une feuille Excel il est inutile de charger autant de lignes, le but était de tester une forte volumétrie.
Donc en phase avec toi sur les aspects fonctionnels que tu soulignes.
Personnellement dans un projet j'ai été amené à exploiter un historique qui arrivait à 16 000 000 de lignes je suis passé par du PowerPivot et du powerquery pour le nettoyage des données puis je les ai exploité par des requêtes ou TCD par le calculs de KPI et en filtrant ces données. Les calculs étaient développés en langage DAX.
Ces 16 000 000 lignes concernaient des traitements de Jobs (25 000 traitements au plan CtrlM chaque jour sur MVS zOS IBM .. du 3270 pour les anciens - CtrlM c'est le pendant d'OPC pour les connaisseurs) afin de montrer l'évolution des différentes campagnes de certifications dynamiques. Les noms des JOBS et des chaines étaient construits selon des domaines fonctionnels. Donc par agrégation je déterminais l'évolution de la résolutions des anomalies et de leurs nombres par domaine d'une campagne à une autre. Cela donnait donc ce que j'appelais un cycle de vie glissant de campagnes en campagnes de certifications.
Pour conclure la certifications de données c'est quelque part ma spécialité et je l'ai mené dans de gros projets financiers. Voilà pour la petite explication fonctionnelle/métier.
J'espère t'avoir rassurer sur les doutes que tu évoquais. :) : ) :)
 
Dernière édition:
Vote positif 0 Vote négatif
laurent950

laurent950

XLDnaute Barbatruc
Bonsoir @oguruma

Vous sembler avoir un ordinateur très performant tu veux essayer avec mon code ?

j'ai laissé pour tous récupérer sans les dates. chez moi c'est très long car je peux pas stocké les 2 000 000 de lignes dans une variables tableau.

si vous arriver il vous faut combien de temps pour exécuter mon code avec votre ordinateur ?vos données, ou les données en Poste #48 de job75

le code UTF8 (n'est pas pris en compte) c'est juste une ébauche pour récupérer et tester la rapidité sur une machine plus puissante.

Code: 
Sub LireFichierTexteEtOrganiserDonnees8()
    Dim FSO As Object
    Dim File As Object
    Dim filePath As String
    Dim TextLine As String
    Dim Values() As String
    Dim ID As String
    Dim NumRubGlobal As Integer
    Dim RubGlobal As Collection
    Dim SRub As Collection
    Dim SsRub As Collection
    Dim RubItem As Collection
    Dim cpt As Long: cpt = 1
    Dim IDValueCollection As Collection
    Dim i As Integer
    Dim Flag As Boolean
    Dim Ligne As Long: Ligne = 2
    Dim j As Byte
   
    ' Initialisation des collections
    Set RubGlobal = New Collection

    ' Demander à l'utilisateur de sélectionner un fichier txt
    filePath = Application.GetOpenFilename("Fichiers TXT (*.txt), *.txt")
   
    ' Initialisation de l'objet FileSystemObject
    Set FSO = CreateObject("Scripting.FileSystemObject")
   
    ' Ouverture du fichier texte
    Set File = FSO.OpenTextFile(filePath, 1)
   
    ' Lecture du fichier ligne par ligne
    Do While Not File.AtEndOfStream
        TextLine = File.ReadLine
        ' Division de la ligne par point-virgule
        Values = Split(TextLine, ";")
       
        ' Extraction de l'ID (première chaîne avant le point-virgule)
        ID = Values(0)
       
        ' Si l'ID n'est pas déjà dans la collection des IDs, l'ajouter
        On Error Resume Next
            Set SRub = RubGlobal(CStr(ID))
            Set SsRub = SRub(CStr(ID))
         If Err Then
            Set SRub = New Collection
                RubGlobal.Add SRub, CStr(ID)
           Set SsRub = New Collection
               SRub.Add SsRub, CStr(ID)
          Set RubItem = New Collection
              SsRub.Add RubItem, CStr(CStr(ID))
        Else
            Set RubItem = New Collection
            SsRub.Add RubItem, CStr(cpt)
            cpt = cpt + 1
        End If

        ' Ajouter chaque valeur de la ligne dans la collection correspondante
        For i = LBound(Values) To UBound(Values)
            RubItem.Add Values(i)
        Next i
        On Error GoTo 0
    Loop
'
    ' Fermeture du fichier texte
    File.Close
    Set File = Nothing
    Set FSO = Nothing
    filePath = Empty
    TextLine = Empty
    Erase Values
    ID = Empty
   
    ' Affichage des éléments pour vérification
    'For Each SRub In RubGlobal
     For NumRubGlobal = 2 To RubGlobal.Count
        Set SRub = RubGlobal(NumRubGlobal)
        For Each SsRub In SRub
            Debug.Print SRub.Count ' Nombres de lignes
            For Each RubItem In SsRub
                Debug.Print SsRub.Count ' Nombres de colonnes
                If Flag = False Then ReDim Values(1 To SsRub.Count + 1, 1 To RubItem.Count): Flag = True
                For j = 1 To RubItem.Count
                    Debug.Print RubItem.Item(j) ' Premier Item
                    Values(Ligne, j) = RubItem.Item(j)
                Next j
                Ligne = Ligne + 1
            Next
        Next
        Flag = False
        Ligne = 2
        For j = 1 To RubGlobal.Item(1).Item(1).Item(1).Count
            Debug.Print RubGlobal.Item(1).Item(1).Item(1).Item(j) ' Premier Item
            Values(1, j) = RubGlobal.Item(1).Item(1).Item(1).Item(j)
        Next j
        nomfeuille = "F" & "-" & Values(2, 1)
        On Error Resume Next
        Application.DisplayAlerts = False
        Sheets(nomfeuille).Delete ' Supprime la feuille si elle existe
        Application.DisplayAlerts = True
        On Error GoTo 0
        Sheets.Add After:=Sheets(Sheets.Count)
        ActiveSheet.Name = nomfeuille
        Cells(1, 1).Resize(UBound(Values, 1), UBound(Values, 2)) = Values
        Erase Values
    Next NumRubGlobal
    Sheets(1).Activate
End Sub
 
Vote positif 0 Vote négatif
laurent950

laurent950

XLDnaute Barbatruc
re @oguruma

il faut ajouter la condition pour le filtre sur les dates ici

' Définir les dates de début et de fin
datedeb = DateValue("01/01/2005") ' dates de début
datefin = 31/12/2010 ' dates de fin

' Division de la ligne par point-virgule
Values = Split(TextLine, ";")

If CDate(TSpl(7)) >= datedeb And CDate(TSpl(7)) <= datefin Then

[ Le code ]

end if

si vous avez compris le principe
 
Vote positif 0 Vote négatif
oguruma

oguruma

XLDnaute Occasionnel
laurent950 à dit:
Bonsoir @oguruma

Vous sembler avoir un ordinateur très performant tu veux essayer avec mon code ?

j'ai laissé pour tous récupérer sans les dates. chez moi c'est très long car je peux pas stocké les 2 000 000 de lignes dans une variables tableau.

si vous arriver il vous faut combien de temps pour exécuter mon code avec votre ordinateur ?vos données, ou les données en Poste #48 de job75

le code UTF8 (n'est pas pris en compte) c'est juste une ébauche pour récupérer et tester la rapidité sur une machine plus puissante.

Code: 
Sub LireFichierTexteEtOrganiserDonnees8()
    Dim FSO As Object
    Dim File As Object
    Dim filePath As String
    Dim TextLine As String
    Dim Values() As String
    Dim ID As String
    Dim NumRubGlobal As Integer
    Dim RubGlobal As Collection
    Dim SRub As Collection
    Dim SsRub As Collection
    Dim RubItem As Collection
    Dim cpt As Long: cpt = 1
    Dim IDValueCollection As Collection
    Dim i As Integer
    Dim Flag As Boolean
    Dim Ligne As Long: Ligne = 2
    Dim j As Byte
  
    ' Initialisation des collections
    Set RubGlobal = New Collection

    ' Demander à l'utilisateur de sélectionner un fichier txt
    filePath = Application.GetOpenFilename("Fichiers TXT (*.txt), *.txt")
  
    ' Initialisation de l'objet FileSystemObject
    Set FSO = CreateObject("Scripting.FileSystemObject")
  
    ' Ouverture du fichier texte
    Set File = FSO.OpenTextFile(filePath, 1)
  
    ' Lecture du fichier ligne par ligne
    Do While Not File.AtEndOfStream
        TextLine = File.ReadLine
        ' Division de la ligne par point-virgule
        Values = Split(TextLine, ";")
      
        ' Extraction de l'ID (première chaîne avant le point-virgule)
        ID = Values(0)
      
        ' Si l'ID n'est pas déjà dans la collection des IDs, l'ajouter
        On Error Resume Next
            Set SRub = RubGlobal(CStr(ID))
            Set SsRub = SRub(CStr(ID))
         If Err Then
            Set SRub = New Collection
                RubGlobal.Add SRub, CStr(ID)
           Set SsRub = New Collection
               SRub.Add SsRub, CStr(ID)
          Set RubItem = New Collection
              SsRub.Add RubItem, CStr(CStr(ID))
        Else
            Set RubItem = New Collection
            SsRub.Add RubItem, CStr(cpt)
            cpt = cpt + 1
        End If

        ' Ajouter chaque valeur de la ligne dans la collection correspondante
        For i = LBound(Values) To UBound(Values)
            RubItem.Add Values(i)
        Next i
        On Error GoTo 0
    Loop
'
    ' Fermeture du fichier texte
    File.Close
    Set File = Nothing
    Set FSO = Nothing
    filePath = Empty
    TextLine = Empty
    Erase Values
    ID = Empty
  
    ' Affichage des éléments pour vérification
    'For Each SRub In RubGlobal
     For NumRubGlobal = 2 To RubGlobal.Count
        Set SRub = RubGlobal(NumRubGlobal)
        For Each SsRub In SRub
            Debug.Print SRub.Count ' Nombres de lignes
            For Each RubItem In SsRub
                Debug.Print SsRub.Count ' Nombres de colonnes
                If Flag = False Then ReDim Values(1 To SsRub.Count + 1, 1 To RubItem.Count): Flag = True
                For j = 1 To RubItem.Count
                    Debug.Print RubItem.Item(j) ' Premier Item
                    Values(Ligne, j) = RubItem.Item(j)
                Next j
                Ligne = Ligne + 1
            Next
        Next
        Flag = False
        Ligne = 2
        For j = 1 To RubGlobal.Item(1).Item(1).Item(1).Count
            Debug.Print RubGlobal.Item(1).Item(1).Item(1).Item(j) ' Premier Item
            Values(1, j) = RubGlobal.Item(1).Item(1).Item(1).Item(j)
        Next j
        nomfeuille = "F" & "-" & Values(2, 1)
        On Error Resume Next
        Application.DisplayAlerts = False
        Sheets(nomfeuille).Delete ' Supprime la feuille si elle existe
        Application.DisplayAlerts = True
        On Error GoTo 0
        Sheets.Add After:=Sheets(Sheets.Count)
        ActiveSheet.Name = nomfeuille
        Cells(1, 1).Resize(UBound(Values, 1), UBound(Values, 2)) = Values
        Erase Values
    Next NumRubGlobal
    Sheets(1).Activate
End Sub
Cliquez pour agrandir...
Ahhhh je n'ai pas testé.... mais bloquerais-tu à 16 000... par hasard.... si j'ai bien tout compris....
Les problèmes se trouvent ici

Values(1, j) = RubGlobal.Item(1).Item(1).Item(1).Item(j)
Cells(1, 1).Resize(UBound(Values, 1), UBound(Values, 2)) = Values

En effet il y a une limitation à 16000 lignes par la méthode que tu utilises. Il y a une solution un peu bricolé pour passer cette limite liée à Excel. (une nouvelle fois si j'ai bien tout compris.
J'ai été confronté à ce problème dans le passé.
J'avais développé... de mémoire un ensemble de classes ou autre en VBA....
Je dois encore avoir cela.... laisses-moi le temps de retrouver cela

Mon PC oui en effet une bête de course : un Asus VivoBook 14 avec un disque de 1To en flash :)
Donc c'est vrai rapport à un disque classique il démarre comme une fusée ;)
 
Vote positif 0 Vote négatif
oguruma

oguruma

XLDnaute Occasionnel
laurent950 à dit:
re @oguruma

il faut ajouter la condition pour le filtre sur les dates ici

' Définir les dates de début et de fin
datedeb = DateValue("01/01/2005") ' dates de début
datefin = 31/12/2010 ' dates de fin

' Division de la ligne par point-virgule
Values = Split(TextLine, ";")

If CDate(TSpl(7)) >= datedeb And CDate(TSpl(7)) <= datefin Then

[ Le code ]

end if

si vous avez compris le principe
Cliquez pour agrandir...
Personnellement si j'avais cela à mettre en production.... je n'ouvrirai même l'éditeur VBE je passerai direct par du Pwq.
Je crois que cela te rebute un peu non ?

Côté maintenance moins j'en fais moins j'ai d'emmmmm.....des ;)
 
Vote positif 0 Vote négatif
oguruma

oguruma

XLDnaute Occasionnel
laurent950 à dit:
non cela ne bloque pas je récupères les informations par récursivité ici
la première lignes du fichier texte en faite stocké dans la variable collection
Cliquez pour agrandir...
SI ça te tente.... je peux améliorer mon code pwq afin de passer le nom du champ date à filtre... ainsi on a réellement qq chose de dynamique et non propriétaire. Cela fonctionne avec n'importe quel fichier qui comporte un champ date ... voir on peut pousser le bouchon ça n'est pas un problème en spécifiant plusieurs champs date et préciser lequel on souhaite filtrer avec les dates déb et fin....
j'essaie de faire cela pour demain... si j'ai temps.... ça servirait au moins pour la communauté du Forum :)
 
Vote positif 0 Vote négatif

Discussions similaires

L
  • Question
XL 2013 Macro pour exporter sur excel certaines données d'un fichier txt
Réponses
1
Affichages
462
sylvanu
sylvanu
Akortys
  • Question
Microsoft 365 RESOLU - Macro - Recherche de lignes identiques et ressortir les lignes uniques
Réponses
2
Affichages
333
Akortys
Akortys
F
  • Résolu(e)
Microsoft 365 Supprimer des lignes en fonction de la date et de l'heure
Réponses
8
Affichages
395
AtTheOne
AtTheOne
L
  • Question
XL 2021 Export vers bloc note, met une virgule à la place des points
Réponses
3
Affichages
490
Lu K
L
M
  • Question
Microsoft 365 Recherche multicritères et résultat sous forme de croix (si possible via formule SI et ET)
Réponses
4
Affichages
303
danielco
D
R
  • Question
XL 2019 Calcul Turf. PMU Trouver la plus performant selon les critères de course
Réponses
2
Affichages
892
Compte Supprimé 979
C
B
  • Question
Microsoft 365 Bug dans export TXT
Réponses
6
Affichages
365
job75
job75
C
  • Question
XL 2021 EXCEL IMPORTER du texte de plusieurs fichier en 1 fichier
Réponses
6
Affichages
477
Cousinhub
Cousinhub
F
  • Question
Microsoft 365 Compilation de données avec des sauts de lignes sur le fichier source
Réponses
1
Affichages
513
sylvanu
sylvanu
T
  • Question
Microsoft 365 Empêcher Excel de formater automatiquement les dates
Réponses
2
Affichages
566
Phil69970
Phil69970

Membres actuellement en ligne

17 membres et 154 invités.

Partager cette page

Tutos vidéos

Power Query : Ajouter des requêtes (Excel et Power BI)
Créez votre première fonction LAMBDA dans Excel en 3 étapes!
LAMBDA, l’arme secrète de Microsoft pour Excel !
Office Insider - Découvrez les nouveautés Excel avant tout le monde

Statistiques des forums

Discussions
313 004
Messages
2 094 326
Membres
105 992
dernier inscrit
Fred60600