XL 2019 Power Query - Comment formater un fichier TXT

Flx1er

XLDnaute Junior
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: 7
  • Exemple multi version.xlsx
    18.4 KB · Affichages: 6

Hasco

XLDnaute Barbatruc
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: 8
Dernière édition:

Flx1er

XLDnaute Junior
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: 1
  • file.txt
    378 bytes · Affichages: 2

Hasco

XLDnaute Barbatruc
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: 1

Hasco

XLDnaute Barbatruc
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: 5

Flx1er

XLDnaute Junior
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: 1
  • Flx1er-file-v1.txt
    646 bytes · Affichages: 2

Hasco

XLDnaute Barbatruc
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: 1

Flx1er

XLDnaute Junior
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
 

Statistiques des forums

Discussions
294 444
Messages
1 938 538
Membres
188 914
dernier inscrit
Durludrude