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

[Titof06]

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

 

[oguruma]

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...

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....

 

[oguruma]

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....

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

 

[laurent950]

Bonsoir @oguruma

J'ai réalisé le code mais bon pour 2 millions de lignes à traiter j ai pas assez de ressources mémoires et donc même le code en Poste #48 ne fonctionne pas pour cause de manque de ressources mémoire et vous ?

 

[oguruma]

je prépare un truc avec un fichier de 2000 000 de lignes avec une date et une sélection entre ces dates
patience c'est en chantier actuellement..... pour ce soir ce sera dans le forum

 

[oguruma]

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

Le code de la requête de chargement :

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

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

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

==> Noter que les donnée sont

donc elles figurent dans PowerPivot
On pourrait aussi se contenter de cette option

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

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

 

[laurent950]

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.

 

[oguruma]

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.

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. : )

 

[laurent950]

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.

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

 

[laurent950]

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

 

[oguruma]

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.

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

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

 

[oguruma]

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

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

 

[laurent950]

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

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

 

[laurent950]

Je crois que cela te rebute un peu non ?

Non absolument pas je vais apprendre votre principe que je connais pas encore.

 

[oguruma]

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

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

 

[laurent950]

re @oguruma

je vais recuperer votre fichier texte en poste combien pour avoir les mêmes bases ?