XL 2019 Power Query - Comment formater un fichier TXT

Flx1er

XLDnaute Occasionnel
Bonjour,
J'ai besoin de votre aide pour essayer de résoudre mon problème insoluble (pour mon niveau).
Tous les mois, je réceptionne un fichier d'activité de plusieurs milliers de lignes (nommé DATA.txt) au format texte, mais sans séparateur. Les particularités de ce fichier sont :
  • Ce fichier peut posséder plusieurs versions de données.
  • Chaque version correspond à une structure de formatage de la ligne.
  • L'information de structure de formatage (version) est toujours positionnée au 6e caractère et de longueur 3 caractères.
  • D'une version à une autre, la structure de la ligne évolue.
Ma question est la suivante : est-il possible avec power query de structurer ce genre de fichier ?

En pièce jointe, ce trouve 2 fichiers d'exemples (qui ont été simplifiés au maximum):
  • un fichier DATA.txt
  • un fichier xlsx
    • le 1er onglet contient la structure du formatage de chaque version, puis le résultat escompté.
    • le second onglet contenu l'importation du fichier DATA.
Je vous remercie pour votre aide et le temps que vous allez me consacrer,
Bien cordialement,
 

Pièces jointes

  • DATA.txt
    138 bytes · Affichages: 13
  • Exemple multi version.xlsx
    18.4 KB · Affichages: 15
Solution
Bonjour

Voici

P.S. dans votre version finale, il vaudra mieux isoler chaque tableau plutôt que les disposer les uns sous les autres. Parfois Power Query ne sait pas gérer l'espace disponible à droite et en dessous de ses tableaux de données.

Hasco

XLDnaute Barbatruc
Repose en paix
Bonsoir,

Voici deux possibilités parmi d'autres avec power query.

La deuxième transforme et type les données

La colonne 'fin' des tables de versions n'est pas utile ici.

Cordialement
 

Pièces jointes

  • Flx1er multi version.xlsx
    39.5 KB · Affichages: 17
Dernière édition:

Flx1er

XLDnaute Occasionnel
Bonsoir,

Voici deux possibilités parmi d'autres avec power query.

La deuxième transforme et type les données

La colonne 'fin' des tables de versions n'est pas utile ici.

Cordialement
Bonjour Hasco,
Désolé pour ce retour tardif. Votre méthode répond à mon besoin, je vais l'appliquer à mes différents fichiers d'activités.
Par contre, je dispose également de fichiers d'activité plus complexes. En effet, ces fichiers ont la particularité d'avoir des lignes de longueur différentes. Chaque ligne est toujours structurée de la manière suivante :
  • une partie fixe (méthode que vous m'avez fournie)
  • 3 parties variables (DA, CS, CC)
Sur l'onglet "Version" du document joint, se trouvent les résultats attendus pour chaque partie variable.
Mon problème réside dans le fait, qu'il faut restructurer ces 3 parties variables pour pouvoir les exploiter, et je n'y arrive pas.

Explication d'une ligne
  • Chaque ligne se compose :
    • d'1 partie fixe d'une longueur 1 à 41 (pour la version M1B) et 1 à 39 (pour la version M1A) avec plusieurs champs
    • l'arrêt de partie fixe prend fin au
      • 42e caractères (pour M1B) : Fin PF = 42
      • 40e caractères (pour M1A) : Fin PF = 40
    • le format des parties variables dépendent également des versions (M1B, M1A).
    • d'une partie variable DA
      • dans la partie fixe se trouve le champ nombre d'occurrences de DA : Nbre DA
      • Taille d'1 DA est : T DA = 8 caractères
      • Longueur maxi de la chaine DA est : L Max DA = Nbre DA x T DA
    • d'une partie variable CS
      • dans la partie fixe se trouve le champ nombre d'occurrences de CS : Nbre CS
      • plusieurs champs composent le CS.
    • d'une partie variable CC
      • dans la partie fixe se trouve le champ nombre d'occurrences de CC : Nbre CC
      • plusieurs champs composent le CC
Je vous remercie de votre aide et de votre expertise,
 

Pièces jointes

  • Exemple multi version avec ligne longueur variable v1.xlsx
    242.2 KB · Affichages: 7
  • file.txt
    378 bytes · Affichages: 6

Hasco

XLDnaute Barbatruc
Repose en paix
Bonjour,

Bon ben voilà une autre tentative pour vos chaînes de caractères

Un premier jet pour la partie fixe, mais vous verrez qu'il y a un blème sur la première et deuxième ligne de M1B pour Nbre CC :
La valeur aux limites que vous donnez renvoie 2E7 pour la première, en quoi PQ reconnait une notation scientifique et convertit la valeur en 20000000 ( 2 suivi de 7 zéros)
La valeur aux mêmes limites pour la deuxième, renvoie 1Z5, ce que PQ convertit en erreur

A vous de voir ce qui ne va pas ce qu'il faut modifier.

Le fichier texte a été nommé : "Flx1er-file.txt" et doit avoir ce nom dans le répertoire du classeur (j'ai plusieurs "file.txt" en cours :) )

Pour le moment M1A et M1B sont traités ensembles. Ils pourront être séparés ultérieurement.

Cordialement
 

Pièces jointes

  • Flx1er-Exemple multi version.xlsx
    104.6 KB · Affichages: 9

Hasco

XLDnaute Barbatruc
Repose en paix
Bonjour,

Excusez-moi, c'est la technique d'import que j'avais utilisée qui n'était pas la bonne.
Voici qui est corrigé dans la requête "Partie Fixe"
Je vais travailler sur les parties variables maintenant.
Je ne vous promets rien pour aujourd'hui.

Cordialement
 

Pièces jointes

  • Flx1er-Exemple multi version.xlsx
    129.4 KB · Affichages: 9

Flx1er

XLDnaute Occasionnel
Bonjour,

Bon ben voilà une autre tentative pour vos chaînes de caractères

Un premier jet pour la partie fixe, mais vous verrez qu'il y a un blème sur la première et deuxième ligne de M1B pour Nbre CC :
La valeur aux limites que vous donnez renvoie 2E7 pour la première, en quoi PQ reconnait une notation scientifique et convertit la valeur en 20000000 ( 2 suivi de 7 zéros)
La valeur aux mêmes limites pour la deuxième, renvoie 1Z5, ce que PQ convertit en erreur

A vous de voir ce qui ne va pas ce qu'il faut modifier.

Le fichier texte a été nommé : "Flx1er-file.txt" et doit avoir ce nom dans le répertoire du classeur (j'ai plusieurs "file.txt" en cours :) )

Pour le moment M1A et M1B sont traités ensembles. Ils pourront être séparés ultérieurement.

Cordialement

Bonjour Hasco,
Je vous remercie sincèrement pour le temps que vous me consacrez, car l'exposé de mon problème est très mal défini.

Les champs Nbre DA; Nbre CS et Nbre CC fourni l'occurrence pour chaque partie variable et définira leur taille de chaque partie variable. Ces occurrences peuvent prendre comme valeur minimale 0 et maximale 99 pour Nbre DA et 999 pour Nbre CS et Nbre CC.
Les versions (M1A, M1B et avenir) peuvent impacter la structure des parties variables (ajout de champ, modification de taille, ...).

En fonction de la valeur présente dans les champs Nbre DA; Nbre CS et Nbre CC, il faudra concaténer à la partie fixe l'information de la première occurrence, puis de la deuxième puis de la N ième.
Chaque occurrence sera une nouvelle ligne.

Par exemple, pour la version de fichier M1B
  • si Nbre DA = 2, alors la partie variable PV1 à 2 occurrences de 8 caractères soit 16 caractères, soit 2 lignes comprenant la même partie fixe avec sur la
    • 1ere ligne, les 8 caractères de la 1ère occurrence
    • 2ième ligne, les 8 autres caractères de la 2ième occurrence
  • si Nbre CS = 3, alors la partie variable PV2 à 3 occurrences de 12 caractères soit 36 caractères, soit 3 lignes comprenant la même partie fixe avec sur la
    • 1ère ligne, les données structurées de la 1ère occurrence
    • 2ième ligne, les données structurées de la 2ième occurrence
    • ...
  • si Nbre CC = 4, alors la partie variable PV3 à 4 occurrences de 14 caractères soit 56 caractères.
    • idem
  • Si le nombre de l'occurrence (Nbre DA; Nbre CS et Nbre CC) est = 0, alors inscrire uniquement la partie fixe dans le traitement de la partie variable correspondante
La problématique réside (notamment) à calculer la position de départ de chaque partie variable.

Une exemple est plus parlant qu'une explication. Vous trouverez sur l'onglet "Version" les exemples des résultats attendus.

En pièce jointe,
  • le fichier Flx1er-file-v1.txt a été complété avec occurrences (Nbre DA; Nbre CS et Nbre CC) = à 0
  • le fichier Flx1er-Exemple multi version-vers Trav.xlsx : Sur l'onglet "Version", j'ai complété les exemples de résultats de la partie fixe et des 3 parties variables (avec les occurrences à 0).
En espérant avoir été un peu plus précis.
Bien cordialement,
 

Pièces jointes

  • Flx1er-Exemple multi version-vers Trav.xlsx
    99 KB · Affichages: 3
  • Flx1er-file-v1.txt
    646 bytes · Affichages: 4

Hasco

XLDnaute Barbatruc
Repose en paix
Re,

J'avais bien compris, mais il me semble que vous n'avez pas lu mon dernier message.

Voici qui est fait, enfin je crois, pour DA.

Mais franchement, il va falloir vous y mettre parce que pour le moment je suis le seul à travailler sur les requêtes et je ne pourrai pas couvrir toutes les changements que vous fournirez. Le forum, n'est qu'une aide.

Si vous êtes d'accord avec le résultat ci-joint, alors je continuerai pour PV2 et PV3

Cordialement
 

Pièces jointes

  • Flx1er-Exemple multi version.xlsx
    134.3 KB · Affichages: 6

Flx1er

XLDnaute Occasionnel
Re,

J'avais bien compris, mais il me semble que vous n'avez pas lu mon dernier message.

Voici qui est fait, enfin je crois, pour DA.

Mais franchement, il va falloir vous y mettre parce que pour le moment je suis le seul à travailler sur les requêtes et je ne pourrai pas couvrir toutes les changements que vous fournirez. Le forum, n'est qu'une aide.

Si vous êtes d'accord avec le résultat ci-joint, alors je continuerai pour PV2 et PV3

Cordialement
Re,
J'ai découvert récemment PQ, afin d'être autonome j'ai sollicité une formation sur PQ. Je suis désolé pour la gêné que je vous occasionne.

Le résultat répond à mon besoin.
Je vous remercie
Cordialement
 

Hasco

XLDnaute Barbatruc
Repose en paix
Bonjour,

Avançons pas à pas. Pour le moment adoptons des méthodes un peu brouillon et lorsque le résultat sera là, nous purgerons. Je pense entre autre à la nécessité de recréer un Index des lignes, si les valeurs de Idx (5 premiers caractères) sont toujours uniques ?

Si PV1 est réglé voyons quels problèmes passons à PV2.

Cordialement
 

Hasco

XLDnaute Barbatruc
Repose en paix
Bonjour,

Voici une autre formulation des requêtes.
Il y a problème avec les lignes M1B d'index 55841
Si vous comptez le nombre de caractères caractères des DA dans
55841M1B12TR550120125410032021F2502000001E782 Y423 TZMM485fx583G
à savoir NbCar("E782 Y423 " ) vous avez 15 et non 16 tel que nous le dit la 'Version file' M1B

Voir et corriger le problème avant de continuer.

Cordialement
 

Pièces jointes

  • Flx1er-Exemple multi version-vers Trav.xlsx
    416.4 KB · Affichages: 3

Flx1er

XLDnaute Occasionnel
Bonjour,

Avançons pas à pas. Pour le moment adoptons des méthodes un peu brouillon et lorsque le résultat sera là, nous purgerons. Je pense entre autre à la nécessité de recréer un Index des lignes, si les valeurs de Idx (5 premiers caractères) sont toujours uniques ?

Si PV1 est réglé voyons quels problèmes passons à PV2.

Cordialement
Bonjour Hasco,
Les valeurs de Idx ne sont pas uniques, plusieurs lignes peuvent avoir le même Idx.
Cordialement
 

Flx1er

XLDnaute Occasionnel
Bonjour,

Voici une autre formulation des requêtes.
Il y a problème avec les lignes M1B d'index 55841
Si vous comptez le nombre de caractères caractères des DA dans

à savoir NbCar("E782 Y423 " ) vous avez 15 et non 16 tel que nous le dit la 'Version file' M1B

Voir et corriger le problème avant de continuer.

Cordialement
Re,
Il manquait 1 espace sur le 1er DA. L'espace a été inséré.
La nouvelle source de données Flx1er-file-v2.txt est en pièce jointe
Cordialement,
 

Pièces jointes

  • Flx1er-file-v2.txt
    649 bytes · Affichages: 2

Hasco

XLDnaute Barbatruc
Repose en paix
Bonjour,

Voici une proposition. Par-contre vos formats pour CC ne semblent plus en rapport avec votre nouveau fichier texte. Je vous laisse corriger.

Cordialement
 

Pièces jointes

  • Flx1er-Exemple multi version-vers Trav.xlsx
    429.4 KB · Affichages: 8

Flx1er

XLDnaute Occasionnel
Bonjour Hasco,
Effectivement, la source de données était mal formatée. C'est corrigé. Le fichier Flx1er-file-v2.txt intègre ces corrections.
J'ai apporté une modification au niveau des étapes :
  • CS Longueur et CC Longueur de la requête "Commun" du fichier xls, en insérant respectivement les occurrences [Nbre CS] et [Nbre CC] afin d'y calculer la longueur.
  • CS String et CC String de la requête "Commun", en supprimant "+1"

En vous remerciant
Bien cordialement
 

Pièces jointes

  • Flx1er-file-v2.txt
    639 bytes · Affichages: 1
  • Flx1er-Exemple multi version-vers Trav2.xlsx
    421.5 KB · Affichages: 1

Discussions similaires

Statistiques des forums

Discussions
311 725
Messages
2 081 939
Membres
101 844
dernier inscrit
pktla