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.
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.
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 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
SelectToTablePour 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.
Dernière édition:
