Ceci est une page optimisée pour les mobiles. Cliquez sur ce texte pour afficher la vraie page.

POWERQUERY :: Importation d'un fichier texte .csv et sélection dynamique entre deux dates

oguruma

XLDnaute Occasionnel
Bonjour,
Ce post répond à ce fil https://excel-downloads.com/threads...0-000-de-lignes.20083249/page-6#post-20648552 dans lequel la problématique était d'importer 2 000 000 de lignes en précisant une date de début et de fin afin de réduire la volumétrie des lignes chargées dans le classeur Excel.

Vous trouverez un début de solution perfectible selon vos besoins.

On part pour cela d'une table de paramétrage comme celle-ci

Ps : les paramètres FIELD, CRITERIA et OPERATOR ne sont pas utilisés. Ils sont présents à titre d'évolution future.

Les paramètres parlent d'eux-mêmes.

L'idée est donc d'extraire au moment de l'importation les enregistrements répondant à :

Il faudra bien entendu avoir la connaissance de la structure du fichier texte pour alimenter :

PROMOTE : Le fichier texte comporte-t-il une ligne titre de colonne qu'il faut activer au moment de l'importation
FILEENCODING : 1252 pour l'encode Windows ou 65001 pour UTF8 (pour plus voir la documentation powerquery)
FIELDDATE : Le nom du champ doit exister. S'il n'existe pas afin d'éviter les erreurs de traitement le fichier est importé dans sont intégralité.

DATE_DEB et DATE_FIN : Ces champs représentent les dates de début et de fin ; si la date est incorrecte un message d'erreur est renvoyé comme résultat



si aucun enregistrement est trouvé - table vide

Si la date de début est renseignée uniquement ça devient une recherche >= à cette date
Si la date de fin est renseignée uniquement ça devient un recherche <= à cette date
Donc pour avoir une recherche = --> les dates début et fin sont égales


En cas de succès

Le code de la requête : il peut paraître long et compliqué. Cela passe comme dit maintes fois par un peu d'investissement. L'organisation du code présent est aussi pédagogique.
Il montre là comment fonctionne le bloc let..in et comment il peut s'imbriquer pour effectuer plusieurs actions à l'issue d'un test par exemple comme on le ferait en VBA.
Il a l'avantage de ne pas être propriétaire car le champ date est détecté automatiquement. La requête est aussi construite dynamiquement et exécutée par un ordre Expression.Evaluate. La prévention des erreurs est réalisée par le jeu d'instructions try...otherwise. Nous avons là un code sécurisé de la même manière qu'on le ferait en VBA avec ON ERROR GOTO....
Le nombre de lignes de code en VBA serait nettement supérieur à cette version en langage M.
Le langage M est un vrai langage qui se structure comme tous autres langage de scripting. Il nécessite aussi un peu d'algorithmie comme tous développements. Donc ce n'est pas simplement la pose de fonctions pour obtenir un résultat.

PowerQuery:
let

    //***********************************************************************************************************
    // On récupère les paramètres de la table TB_PARAMS
    //***********************************************************************************************************
    FileName=fnGetParameters("TB_PARAMS","FULLFILEPATHNAME"), 
    FileEncoding=fnGetParameters("TB_PARAMS","FILEENCODING"),
    Separator=fnGetParameters("TB_PARAMS","SEPARATOR"),
    FieldDate=fnGetParameters("TB_PARAMS","FIELDDATE"),
    Field=fnGetParameters("TB_PARAMS","FIELD"),
    Criteria=fnGetParameters("TB_PARAMS","CRITERIA"),
    t_DATE_DEB=fnGetParameters("TB_PARAMS","DATE_DEB"),
    t_DATE_FIN=fnGetParameters("TB_PARAMS","DATE_FIN"), 
    Region=fnGetParameters("TB_PARAMS","CULTURE"), 
    bPromote=if fnGetParameters("TB_PARAMS","PROMOTE")="OUI" then true else false, 

    //***********************************************************************************************************
    // Contrôle sur le format des dates saisies - on supprimer les espaces éventuels
    //***********************************************************************************************************
    DateDeb=if t_DATE_DEB is text then  Text.Trim(t_DATE_DEB) else t_DATE_DEB,
    DateFin=if t_DATE_FIN is text then  Text.Trim(t_DATE_FIN) else t_DATE_FIN, 
  
    //***********************************************************************************************************
    // On détermine l'interval de dates à retenir
    //***********************************************************************************************************
    ToDateDateDeb=if DateDeb <> null then #date(Date.Year(DateDeb),Date.Month(DateDeb),Date.Day(DateDeb)) else null,
    ToDateDateFin=if DateFin <> null then #date(Date.Year(DateFin),Date.Month(DateFin),Date.Day(DateFin)) else null,
    isDateBetween=if ToDateDateDeb is date and ToDateDateFin is date then true else false,
    isDateLessOrEqual=if ToDateDateDeb is null and ToDateDateFin is date then true else false,
    isDateGreaterOrEqual=if ToDateDateDeb is date and ToDateDateFin is null then true else false,

    //***********************************************************************************************************
    // Un champ date est-il renseigné dans les paramètres ?
    //***********************************************************************************************************
    isFieldDateExists = if Text.Trim(FieldDate) is null then false else true,

    //***************************************************************************************************************
    // Cas où la date n'est pas transmise dans les paramètres on prend toutes les données au moment de la sélection
    //***************************************************************************************************************
    isFullSource=if ToDateDateDeb is null and ToDateDateFin is null then true else false,
  
    //***********************************************************************************************************
    // On récupère la source de données
    // Columns à null ainsi n'importe fichier peut être importé sans restriction sur les colonnes
    //***********************************************************************************************************
    Source = try
                Csv.Document(File.Contents(FileName),[Delimiter=Separator, Columns=null, Encoding=FileEncoding, QuoteStyle=QuoteStyle.None])
             otherwise "#ERROR",

    //***********************************************************************************************************
    // Gestion de la ligne titre des colonnes
    //***********************************************************************************************************
    TableToPromote = try
                        if bPromote then Table.PromoteHeaders(Source, [PromoteAllScalars=true]) else Source
                    otherwise "#ERROR#", 

    //***********************************************************************************************************
    // On récupère la liste des colonnes
    //***********************************************************************************************************
    ListColumns=Table.ColumnNames(TableToPromote),
    PosField=List.PositionOf(ListColumns,FieldDate),

    //***********************************************************************************************************
    // Nettoyage du champ date et formatage type date si au moins un champ date est renseigné
    // Le champ date spécifié doit exister dans les colonnes de la table
    //***********************************************************************************************************
    TableToSelect = try
                        if isFieldDateExists and PosField <> -1
                        then
                            let
                                CleanText = Table.TransformColumns(TableToPromote,{{FieldDate, Text.Clean, type text}}),
                                DelSpaces = Table.TransformColumns(CleanText,{{FieldDate, Text.Trim, type text}}),
                                // On applique les paramètres de formatage régionaux
                                TableTypeRegion =  Table.TransformColumnTypes(DelSpaces, {{FieldDate, type date}}, Region)
                            in
                                TableTypeRegion
                        else
                            TableToPromote
                    otherwise "#ERROR",

    //***********************************************************************************************************
    // On prépare la sélection entre les dates pour les évaluer dans Expression.Evaluate
    //***********************************************************************************************************
    StrEvalFilterLessOrEqual="each [" & FieldDate & "] <= ToDateDateFin",
    StrEvalFilterGreaterOrEqual="each [" & FieldDate &"] >= ToDateDateDeb", 
    StrEvalFilterBetween="each [" & FieldDate &"] >= ToDateDateDeb and [" & FieldDate & "] <= ToDateDateFin",

    //***********************************************************************************************************
    // Calcul sur quelle tranche de date on doit appliquer la sélection
    //***********************************************************************************************************
    StrEval = try if isDateBetween
              // Deux dates ont été saisies
              then  StrEvalFilterBetween
              else
                    // Seule la date de début a été saisie
                    if isDateGreaterOrEqual
                    then  StrEvalFilterGreaterOrEqual
                    else
                    // Seule la date de fin a été saisie
                        if isDateLessOrEqual
                        then  StrEvalFilterLessOrEqual
                        else  "#N/A"
                otherwise "#ERROR#",

    //***********************************************************************************************************
    // On applique la sélection en tenant compte si des dates sont renseignées
    //***********************************************************************************************************
    //*************************************************************************************************************************************
    // si aucune sélection de date, si le champ date est inconnu, si on ne précise pas de champ date alors on renvoie la table complète
    // sinon on applique le filtre sur les dates
    //*************************************************************************************************************************************
    SelectToTable = try if isFullSource or not isFieldDateExists or PosField=-1 or StrEval is null or StrEval = "#ERROR#"
                    then TableToSelect
                    else
                        if StrEval = "#N/A"
                            then #table({},{})
                            else let
                                    EvalFilter=Expression.Evaluate(StrEval, [Table.SelectRows=Table.SelectRows, ToDateDateDeb=ToDateDateDeb, ToDateDateFin=ToDateDateFin, FieldDate=FieldDate]),
                                    Select = try Table.SelectRows(TableToSelect, EvalFilter) otherwise "#ERROR#",
                                    Sort = try Table.Sort(Select,{{FieldDate, Order.Ascending}}) otherwise "#ERROR#"
                                in
                                    Sort
                    // Et en cas d'impossibilité d'effectuer la requête on renvoie ce message
                    otherwise "Erreur de traitement. Vérifier vos paramètres." 
in
    SelectToTable

Pour vos tests voir le fichier Excel de l'outil et le fichier .csv
Pour les tests de volumétrie 2 000 000 lignes dupliquer les lignes autant de fois que nécessaire.
 

Pièces jointes

  • Consolider2M_V0.100.xlsm
    245.2 KB · Affichages: 5
  • fr-en-liste-diplomes-professionnels.zip
    17.6 KB · Affichages: 5
Dernière édition:

oguruma

XLDnaute Occasionnel
Bonjour une évolution se trouve dans ce lien https://excel-downloads.com/threads...tion-entre-deux-dates-possible-v2-0.20083458/
 

Discussions similaires

Les cookies sont requis pour utiliser ce site. Vous devez les accepter pour continuer à utiliser le site. En savoir plus…