Blog de développement

On a vu comment télécharger l'annuaire Chorus Pro de façon régulière ( Lire... →) : on obtient un fichier Excel XLSX de 27 Mb. Dans un premier temps, on veut juste consulter pour savoir si la facture doit (ou peut) être deposée sur Chorus. C'est simple... Si l'identifiant, en fait le SIRET Client, est dans la liste c'est obligatoirement pour Chorus, Sinon... non...

On envisage 3 stratégies, sans jamais utiliser Excel, qu'on n'a pas sur le serveur: par DTS (MSSQL) ( Lire... →), par le Driver ODBC de Microsoft ou par BCP (Bulk Copy Program) de Microsoft (gratuit et efficace) ( Lire... →)

Difficulté : il y a 2 feuilles dans le XLSX, qui est quand même bien gros. Moins que son alter-ego XML, proposé aussi par Chorus et qui fait... 200 Mb !

Pourquoi utiliser un utilitaire en ligne de commande ?

C'est simple, et en plus on veut aboutir à un fichier simple. La ligne de commande, c'est universel! Il existe pléthore de solutions payantes ou pas. Beaucoup en OpenSource (et Linux). On va en choisir 2: CSVKT et xlsxio_xlsx2csv qui convertit toutes les feuilles de tous les fichiers .xlsx spécifiés en fichiers CSV individuels. Les 2 sont utilisables aussi bien sur Windows que sur Linux

Un avantage par rapport à DTS et le driver Excel: l'execution reste possible même si l'équipe Chorus apporte des modifications au schéma (ainsi en Janv. 2023). Dans un tel cas, il faut refaire les fichiers DTS ou reprogrammer le driver. La ligne de commande sera plus stable.

Beaucoup de solutions ont un inconvenient irrémédiable: elles sont incapables de traiter des feuilles multiples. C'est en particulier le cas de Libre Office (soffice, en ligne de commande) qui ne traite que la première feuille, de même que les nombreux convertisseurs en ligne.

Projet Is_Chorus et liste enrichie

Lors e la saisie de commande ou de la facturation, avec ou sans automate, il est important de documenter si vous avez des obligations vis-à-vis de Chorus (et bientôt des autres plateformes à venir). A cette fin, Chorus n'offre qu'un réferentiel lourd et fastidieux, et pas d'API simple. On lui substitue un réferentiel en simple liste

La liste simple des identifiants est enrichie d'un code de vos obligations. On aura donc besoin de la description de variables dans Chorus Lire → (elle est aussi dans le ZIP de ressources de ma page initiale Lire... →)

Le fichier structure a 3 header: 7 GestionEngagement, 8 GestionService, 9 GestionServiceEngagement

• - GestionEngagement : le fournisseur devra obligatoirement renseigner numéro d'engagement. C'est ue variable de STRUCTURE. Même si ce n'est pas obligatoire à ce niveau, un service ( = sous structure) peut, elle, le rendre obligtoire!
• - GestionService : le fournisseur devra obligatoirement renseigner "code service"
• - GestionServiceEngagement

Les informations qui manquent à ce niveau là: savoir si la STRUCTURE a des SERVICES (que ceux ci soient obligatoires ou pas)

Les differents cas et obligations possibles:

• numéro d'engagement : 3 possibilités: pas obligatoire, pas obligatoire sauf pour quelques services, toujours obligatoire
• service : 4 possibilités: pas de services, a des services mais mention pas obligatoire, a des services et certains en rendent la mention obligatoire, la mention "code service" est toujours obligatoire.

Le seul tableau STRUCTURE, seul, ne permet pas de caractériser vos obligations. Il faut appliquer des opérations logiques sur les 2 tableaux et les flags qui y sont.

Ancienne Méthode : xlsxio_xlsx2csv et ce qui va avec...

En Janvier 2024, je constate que cette methode ne marche plus sur le XLSX de Chorus. Entretemps, je suis passé à QSV...

Xlsxio_xlsx2csv de Brecht Sanders est en 32 ou 64 bits et peu documenté, par contre l'utilisation est simplicime (en pratique, il n'admet qu'un seul argument). Il n'y a aucune dépendance vis-à-vis de Microsoft(R) Excel(TM). Il est multiplateforme et fonctionne sur Windows, macOS et Linux. Point important: il transforme les 2 feuilles, en même temps, ce qui n'est pas fréquent dans les solutions gratuites...

Le point dur est qu'il nous faut 5 programmes : xlsxio_xlsx2csv.exe , libexpat-1.dll , zlib1.dll et libgcc_s_dw2-1.dll, ainsi que CSVTK (voir ci dessous). On peut passer un temps non négligeable à chercher, télécharger, installer, alors qu'il suffit de les avoir tous les 5 dans un même répertoire, sans installation, et basta! J'ai mis mes 5 lascars (Windows, 64 bits) dans un ZIP: il suffit de télécharger mon dossier ZIP, tout est dedans!

On le télécharge depuis la page Latest Release Télécharger... →

Sauvé par QSV

En fait, QSV est de loin le plus pratique en ce moment. Je l'utilise pour tous les référentiels. Je lui ai consacré une page Lire... →

On notera que quand la mémoire disponible vient à manquer, QSV peut générer un CVS vide au lieu de bien extraire un XLSX. Je vérifie systématiquement la qualité du travail après extraction, et, si besoin, je relance avec CSVTKv

Télécharger CSVKT

CSVKT se veut un concurrent de CSVKIT. On le télécharge ici. ou sur GitHub à la page Latest Release Télécharger... → Il est directement disponible pour Windows et Linux, sans installation. Il dispose d'un module permettant de transformer un XLSX en CSV, mais, l'annuaire Chorus semble trop grospour lui. Il ne prend pas les XLSX avec le point virgule comme deliminateur. On ne peut donc pas non plus l'utiliser pour l'annuaire des codes postaux de La Poste.

Conçu pour la bio-informatique, donc pour des fichiers CSV très gros, il est bien pratique une fois le CSV formé. Sa syntaxe est un peu déroutante au début. Le caractère barre (c'est-à-dire ' | ' ) permet à la sortie standard d'une commande d'être utilisée comme entrée standard d'une autre commande. C'est ce qu'on appelle un pipe.

Extraction du XLSX en ligne de commande

Avec xlsx2csv.exe, on transformait le fichier XLSX en 2 fichiers CSV avec la commande:

OBSOLETE ? "C:\MesLogiciels\xlsxio_xlsx2csv.exe"  "C:\MesData\Annuaire.xlsx"

A noter: si le fichier XLSX utilise un autre séparateur que la virgule, comme la base des codes postaux, on ajoute l'argument " -s ";" ". "C:\MesLogiciels\xlsxio_xlsx2csv.exe" "C:\MesData\laposte_hexasmal.xlsx" -s ";"

Avec QSV... Et -Q (Quiet) sinon le fichier d'erreur n'est pas vide même si tout c'est bien passé !

"c:\...\qsv.exe" excel --sheet "Services" --flexible -Q -o "E:\...\Chorus_Services.csv" "E:\...\AIFE-Chorus-Pro-Annuaire_20240208.xlsx"  2> "E:\...\Error.txt"

Important : Après QSV, il faut vrifier que le fichier obtenu est de taille! En effet,il lui arrive de générer un fichier vide, sans lever d'erreur!

Si besoin, avec CSVKT... Avec ces 2 utilitaires, il faut 1 ligne de commande par feuille, donc pour Chorus, il en faut 2!

"c:\...\csvtk.exe" xlsx2csv -o "E:\...\Chorus_Services.csv" "E:\...\AIFE-Chorus-Pro-Annuaire_20231220.xlsx" -n Services 2> "E:\...\error.txt" 

On dispose ainsi de 2 fichiers CSV: Structures.csv et Services.csv : on va reduire à 1 ou 2 colonne(s).

Transformation, simplification des CSV

On dispose maintenant de la base Chorus Structures! Le premier point est de savoir si oui ou non ce client (identifié avec son code SIRET ou à défaut SIREN) est affilié à Chorus. Chorus va s'ouvrir à toutes les entreprises, donc cette étape est importante.

L'idée ici, est d'avoir un outil rapide et fiable de vérification de notre obligation (ou pas) à utiliser Chorus. C'est la première étape. Si le client est Chorus, il faut ensuite déterminer s'il gère une subdivision en 'Services' et s'il l'impose ou pas. Sur ce billet je traite la première question, et on va le faire en générant une simple liste, aisément consultable par FSO, etc.

Pour la manipulation, QSV convient très bien! CSVTK, CSV kit, ou python sont de bonnes alternatives... QSV, programmé en RUST, disponible en ligne de commande est beaucoup plus rapide!

A noter : QSV et CSVTK tranforment la fin de ligne Windows (Cr+Lf) en fin de ligne LINUX (LF). On pourra soit corriger (petit utilitaire : Dos2Unix / Unix2Dos) soit s'assurer que les process ultérieurs sont bien compatibles (LF). J'ai détaillé dans ce billet... →

Le fichier comporte un flag (0 ou 1) pour indiquer si le SIRET (identifiant) est actif ou pas. On procède par étapes: on élimine tout sauf les colonnes Identifiant, GestionService, StructureActive.. Ensuite on partitionne selon la valeur de StructureActive.

Extraits et Headers : pour faciliter la compréhension des fichiers, je crée des extraits (10 lignes) et les entêtes (headers)

"c:\...\qsv.exe" slice -s 1 -l 10  -o "E:\...\Structures_Extrait.csv" "E:\...\Structures.csv"  2> "E:\...\Structures_Extrait_error.txt" 
"c:\...\qsv.exe" slice -s 1 -l 10  -o "E:\...\Services_Extrait.csv" "E:\...\Services.csv"  2> "E:\...\Services_Extrait_error.txt" 
"c:\...\qsv.exe" headers "E:\...\Structures_Extrait.csv" 1> "E:\...\Structures_headers.txt"  2> "E:\...\Structures_headers_error.txt" 
"c:\...\qsv.exe" headers "E:\...\Services_Extrait.csv" 1> "E:\...\Services_headers.txt"  2> "E:\...\Services_headers_error.txt.txt" 
"c:\...\qsv.exe" select Identifiant,GestionService,StructureActive -o "E:\...\Structures_Identifiant_GestionService_StructureActive.csv" "E:\...\Structures.csv"  2> "E:\...\Structures_Identifiant_GestionService_StructureActive_error.txt" 
REM Attendre un peu (par exemple, 10 secondes)
timeout /t 10 /nobreak > NUL
"c:\...\qsv.exe" partition 3 --drop --filename "{}.csv" "E:\..." "E:\...\Structures_Identifiant_GestionService_StructureActive.csv"  2> "E:\...\Structures_Identifiant_GestionService_error.txt" 
REM Attendre un peu (par exemple, 10 secondes)
timeout /t 1 /nobreak > NUL
REM Apers partition on renome...
IF EXIST  "E:\...\Structures_Identifiant_GestionService.csv"  del  "E:\...\Structures_Identifiant_GestionService.csv" 
REM Attention avec REN c'est le Name pas le Path !!!
ren "E:\...\true.csv" "Structures_Identifiant_GestionService.csv" 
IF EXIST  "E:\...\false.csv" del "E:\...\false.csv" 
"c:\...\qsv.exe" select Identifiant -o "E:\...\Structures_Identifiant.csv" "E:\...\Structures_Identifiant_GestionService.csv" 2> "E:\...\Structures_Identifiant_error.txt" 
REM Attendre un peu (par exemple, 10 secondes)
timeout /t 10 /nobreak > NUL
"c:\...\qsv.exe" select Identifiant,Code,Nom -o "E:\...\Services_Identifiant_Code_Nom.csv" "E:\...\Services.csv"  2> "E:\...\Services_Identifiant_Code_Nom_error.txt" 
REM Attendre un peu (par exemple, 10 secondes)
timeout /t 1 /nobreak > NUL
"c:\...\qsv.exe" select Identifiant,Code -o "E:\...\Services_Identifiant_Code.csv" "E:\...\Services.csv"  2> "E:\...\Services_Identifiant_Code_error.txt" 
REM Attendre un peu (par exemple, 10 secondes)
timeout /t 1 /nobreak > NUL

"C:\...\csvtk.exe" cat "C:\...\Structures.csv" | "C:\...\csvtk.exe" cut -I -f 2  -o "C:\...\MesData\Structures.csv"
"C:\...\csvtk.exe" cat "C:\MesData\Annuaire.xlsx.Services.csv" | "C:\...\csvtk.exe" cut -I -f 2,3  -o "C:\...\MesData\Services.csv"

Un pipe est crée: c'est le sens du caractere spécial | . D'abord on charge le CSV avec la commande CAT. Il fait environ 29 Mb, puis on sélectionne la colonne qui nous interesse avec la commande CUT.

La colonne qui nous interesse est la 2 ième : Identifiant, c'est à dire le SIRET Client. On ne garde que celle-là. C'est l'argument -f 2. Et respectivement, c'est -f 1,3 pour conserver les colonnes 1 (SIRET) et 3 (Code Service) des services.

L'argument -I permet d'ignorer les lignes comportant une erreur. En effet, il en reporte une au travers du message [WARN] file '-': 1 illegal rows ignored: [51253]
Aucune erreur détectée dans le module Services

L'argument -o spécifie le fichier de sortie. Cest tout!

Chorus: détection et correction des erreurs

Avant d'utiliser nos utilitaires ou d'entrer les données dans SQL, il convient de vérifier que le fichier CSV est conforme. En effet, il y a des erreurs dans Chorus! Chorus est géré avec Excel. L'identifiant est une séquence de 14 chiffres: c'est comme un nombre mais pas un nombre ! Il existe des SIRET qui commencent par ZERO !: un SIRET 0 + 13 chiffres se retrouve sous la forme de 13 chiffres...

Nos utilitaires (QSV ou CSVTK) présupposent que les fichiers sont en UTF-8. Le fichier XLSX de Chorus, généré dans un environnement Windows de chez nous, il n'y a probablement pas de problèmes. Pour s'en convaincre on dezippe un .XLSX, puis on navigue vers les 2 fichiers Sheet1.xlm et Sheet2.xlm. On lance une transformation iconv, sans l'informer de ce qu'il faudra faire en cas de problème. A la première difficulté, il lève une erreur telle que iconv: ... ne peut convertir. Il lèvera pas d'erreur si vous essayez de convertir de UTF-8 à Windows-1252. Toutefois, l'usage de BCP, par exemple, est problématique...

SevenZipPath e "sSourceFilePath" -aoa -o"sDossierUnZip"
"C:\...\iconv.exe" -f UTF-8 -t UTF-8 "E:\...\Unzip\Sheet1.xml" 1> NUL 2> "E:\...\Iconv_Errors\Sheet1.txt"
"C:\...\iconv.exe" -f UTF-8 -t UTF-8 "E:\...\Unzip\Sheet2.xml" 1> NUL 2> "E:\...\Iconv_Errors\Sheet2.txt"
"C:\...\iconv.exe" -f UTF-8 -t Windows_1252 "E:\...\Unzip\Sheet1.xml" 1> NUL 2> "E:\...\Iconv_Errors\Sheet1_Windows_1252.txt"
"C:\...\iconv.exe" -f UTF-8 -t Windows_1252 "E:\...\Unzip\Sheet2.xml" 1> NUL 2> "E:\...\Iconv_Errors\Sheet2_Windows_1252.txt"

Avec -aoa pour overwrite sans prompt, pas de blanc après -o. Ainsi, il dezippe sans sous-dossiers...