XL 2019 Power Query - Comment formater un fichier TXT

[Flx1er]

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,

 

[Hasco]

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

 

[Flx1er]

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,

 

[Hasco]

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

 

[Hasco]

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

 

[Flx1er]

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,

 

[Hasco]

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

 

[Flx1er]

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]

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]

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

 

[Flx1er]

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]

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,

 

[Hasco]

Bonjour,

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

Merci, l'important étant de définir s'il s'agissait d'un simple manque ponctuel pour quelque chose qui pourrait se reproduire, auquel cas, il faudrait prévoir autre chose.

cordialement

 

[Hasco]

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

 

[Flx1er]

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