EXCEL - Formule pour trouver la dernière occurrence d'un caractère

Voici une formule pour Excel permettant de récupérer les derniers caractères, à partir de la dernière occurrence d'un caractère ("/" pour illustrer l'exercice):
Par exemple:

"AA/BB/CC/DD" donne le résultat "DD".

Voici donc la formule:

=SIERREUR(DROITE(A1;NBCAR(A1)-TROUVE("\";SUBSTITUE(SUBSTITUE(A1;"\";" ");"/";"\";NBCAR(A1)-NBCAR(SUBSTITUE(A1;"/";"")))));"")

On utilise la possibilité de la fonction Excel SUBSTITUE de remplacer une seule occurrence, en particulier la dernière occurrence. Pour cela on récupère le nombre d'occurrence du caractère "/", à l'aide de la formule:

=NBCAR(A1)-NBCAR(SUBSTITUE(A1;"/";""))


Ensuite il suffit de remplacer cette dernière occurrence de "/" par un caractère unique (par exemple "\"), pour déterminer la position du dernier "/", en recherchant ensuite la position du caractère unique "\"). Pour garantir l'unicité e "\", il faut aussi remplacer la totalité des "\" par des espaces par exemple).

Formule pour la position de la dernière occurrence:

=TROUVE("\";SUBSTITUE(SUBSTITUE(A1;"\";" ");"/";"\";NBCAR(A1)-NBCAR(SUBSTITUE(A1;"/";""))))

Ce genre de formule serait plus simple à réaliser en VBA, mais l'exécution en VBA d'une formule peut être très coûteux en temps d’exécution, et de surcroît oblige à autoriser le VBA. Donc malgré la complexité de cette formule, elle est indispensable dans certain cas.

L'art et la manière du JOIN en SQL

En SQL, rien de plus simple et de plus compliquée à la fois que l'association de 2 tables.
Au début de mon apprentissage SQL, je ne connaissais que l'association de type:
    SELECT * FROM A,B WHERE C1=C2;

Comme Monsieur Jourdain, je faisais des jointures sans le savoir. J'étais à cette époque un homme inconscient de la terrible complexité de l'association de 2 tables.  Depuis j'ai découvert la syntaxe suivante (massivement utilisée actuellement):
  SELECT * FROM A LEFT JOIN B ON C1=C2;
C'est en étudiant ces "OUTER, INNER FULL, RIGH, LEFT JOIN and co" que j'ai pu apprécier la douce complexité d'un produit de matrice.

Plutôt qu'un long discours, voici 2 copies d'écrans permettant de faire un état de l'art des jointures SQL:

Le pré-requis pour comprendre ces différentes jointures, est la notion de "NULL", qui désigne une valeur (une cellule) qui ne peut être renseignée (déterminée) lors d'une jointure. Si vous voyez "null" suite à un SELECT, cela signifie qu'une valeur n'a pas pu être renseignée. Dans le cas d'une jointure à gauche par exemple (LEFT JOIN), cela signifie certainement qu'une ligne à gauche n'a pas trouvée de correspondance à droite (selon le critère ON spécifié). A noté que suite à de simple INSERT, on peut éventuellement créée des lignes avec des valeurs "null". La notion "null" est une notion centrale dans la logique SQL, essentielle bien au delà des jointures.


Voici la version texte (testée avec SQL Server 2012) :
DECLARE @T1 TABLE ( A int , B varchar(2) );
DECLARE @T2 TABLE ( C int , D varchar(2) );

INSERT INTO @T1
VALUES
(1,'a'),
(2,'b'),
(3,'c'),
(4,'d');

INSERT INTO @T2
VALUES
(1,'f'),
(1,'g'),
(4,'h'),
(5,'i');

SELECT * FROM @T1
    FULL OUTER JOIN @T2 ON A=C;

SELECT * FROM @T1
    RIGHT JOIN @T2 ON A=C;

SELECT * FROM @T1
    LEFT JOIN @T2 ON A=C;

SELECT * FROM @T1, @T2;

SELECT * FROM @T1, @T2
 WHERE A=C;

SELECT * FROM @T1
    FULL JOIN @T2 ON A=C
    WHERE A is null OR C IS NULL;

La révolution de l'électronique moderne en 4 étapes

L'électronique moderne a révolutionné notre civilisation, et transformé notre quotidien dans des proportions qu'aucun livre de science fiction n'avait envisagé. Cela a été possible grâce à une science devenu centrale dans notre progrès technique et social, et qui a bénéficié en 25 ans d'une révolution que je découpe arbitrairementen 4 étapes:

• 1947: La découverte du transistor
• 1958: le premier circuit intégré
• 1971: le premier microprocesseur
• 1973: le premier micro-ordinateur

En une seule génération, l'électronique a ouvert des possibles technologiques jusqu'à là insoupçonnés. C'est avec le recul, le bon technologique le plus impressionnant en terme de rapidité dont a bénéficié l'humanité. L'électronique est une science relativement récente datant du 19ème. Mais pour ma part, l'électronique moderne commence en 1947, une date qui a provoqué une rupture technologique comparable et même supérieur à celle de la machine à vapeur.

Pour le plaisir, voici le déroulé de ces 4 étapes.

1947 : le transistor

Conçu dans les laboratoires Bells par les américains John Bardeen, William Shockley et Walter Brattain, cette invention est passée relativement inaperçue. Son impact technologique n'a pas été comprise immédiatement. Le prix Nobel ne leur fut attribué qu'en 1956. Le nom même de transistor (contraction de transfer-resistor) démontre une volonté de trouver un nom simple, mais relativement éloigné de sa fonction. L'importance de cette invention était de remplacer le "tube radio" (triode), élément essentiel de l'électronique naissante, mais diablement peu pratique. Le transistor a résolu tous les inconvénients du tube radio: il n'est pas fragile, il consomme peu d'énergie, il est minuscule et pas cher. Avant le transistor, un ordinateur (le nec plus ultra de l'électronique de l'époque) était un immense câblage de milliers de tube radio, consommant beaucoup d'énergie, et nécessitant une armée de petites mains pour remplacer constamment les lampes hors d'usage. Les enjeux du transistor étaient la miniaturisation, la fiabilité, et la réduction du coût de fabrication. A partir de ce moment, un grand nombre d'application pour le plus grand nombre étaient envisageables.
Pour simplifier, un transistor est un robinet. Il a deux fonctions:  fermer ou ouvrir un passage pour le courant (c'est une fonction Oui/Non d'un point de vue logique), mais aussi amplifier le courant, en se basant sur la deuxième fonction d'un robinet, cad la possibilité d'ouvrir plus ou moins grand le débit du courant.
Le transistor dans ce domaine n'a rien inventé, il a simplement permis d'améliorer ce principe aussi vieux que l'électronique dans des domaines innombrables que le tube ne pouvait satisfaire à cause de son coût, de son encombrement, et de sa fragilité.

1958: le circuit imprimé

Pour Texas Instrument, Jack Kilby inventa le principe du circuit intégré, c'est à dire l'intégration de circuits électroniques dans des blocs fermés.  Cette nouveauté apportait 3 avancées:

• réduire les coûts d'un circuit électronique
• simplifier sa conception en modularisant sa conception
• sécuriser la fabrication en utilisant des blocs fermés contenant les composants d'une sous-logique.

Le principe était relativement simple, envisagé dès la création du transistor car ce dernier permettait la miniaturisation. Parmi les avantages de ce composant, le plus important est certainement la possibilité de simplifier la conception d'un montage électronique en permettant de décomposer un circuit électronique en sous-module. C'est cela qui a permis aux électroniciens de concevoir années après années, des montages de plus en plus sophistiqués, sans avoir à se soucier de la complexité de certaines fonctions prises en charge par des circuits intégrés. Cerise sur le gâteau, ce composant est souvent bon marché car produit à grande échelle.
La réussite du circuit imprimé, n'a pas été d'intégrer des montages complexes dans un bloc, mais d'intégrer des fonctions complexes réutilisables dans de nombreux montages. La réussite est aussi dans la standardisation de certaines fonctions, permettant ainsi de concevoir rapidement et bon marché. Le principe des circuits intégrés est une mise en œuvre de l'antique maxime reprise par Isaac Newton dans ces termes:

"Si j'ai vu plus loin, c'est en montant sur les épaules de géants".

C'est l'invention du transistor qui naturellement a poussé l'idée de ce principe. Jack Kilby, passionné par la miniaturisation, en a été l'initiateur.

1971: le microprocesseur

Le microprocesseur est la victoire d'une intuition. A l'origine, une jeune société, Intel, prend conscience que les circuits intégrés ont atteint certaines limites. Dans le cas de la conception d'un circuit intégré gérant par exemple une calculatrice (à l'époque, une application très populaire de l'électronique), il fallait dépenser beaucoup de temps pour concevoir une première version du composant, et presque autant quand il était nécessaire de modifier légèrement son fonctionnement. Pour une très jeune société désireuse de répondre aux besoins de ses clients, ils ont eu l'intuition qu'il serait profitable de concevoir un circuit intégré que l'on pourrait "reprogrammer" et ainsi permettre à un même circuit intégré de réaliser des fonctions complexes différentes. Grâce à ce principe, le client pouvait alors modifier son cahier des charges sans obliger Intel à concevoir un nouveau circuit intégré. C'est donc à l'occasion d'une commande pour un circuit intégré d'une calculatrice qu'Intel a réalisé ce pas de géant: concevoir un circuit intégré que l'on pouvait reprogrammer en usine pour lui attribuer différentes fonctions complexes, en fonction d'un micro-code. Le 4004 venait de naître.
Comme souvent lors de grandes découvertes, Intel n'a pas tout de suite eu conscience du potentiel gigantesque de ce principe. Le brevet de ce nouveau circuit intégré, le "4004", avait été vendu à la société ayant commandé ce circuit intégré, et Intel n'a pu récupérer in extremis son brevet qu'à l'occasion d'un revers de fortune de son client.
Le microprocesseur est le premier pas, le véritable basculement de l'électronique classique (dite analogique) vers l'univers de l'électronique numérique. Le principe de l'électronique numérique existait bien avant (rappelons nous Ada Loelace née en 1815 qui fut la pionnière dans ce domaine). Le microprocesseur a donné subitement les moyens à tous les électroniciens de créer aisément ce nouvel univers. Il restait pour cela, la 4ème et dernière révolution, apparu à peine quelques mois plus tard.

1973: le micro-ordinateur

Avant 1973, un ordinateur était gigantesque, et incroyablement coûteux. Les tentatives de réduire la taille et les coûts d'un tel système (par l'entreprise Digital par exemple), n'ont pas été de complètes réussites. Les mini-ordinateurs ainsi créés restait des objets d'environ 1m x 1m x 2m, demandant une armée de programmeurs et d'assistants pour les faire fonctionner. On concevait à grand prix un ordinateur à l'aide de circuit intégré et de composant électronique souvent peu pratique et fragile. D'ailleurs le mot français "ordinateur" ne donne pas l'image réaliste de ce qu'était ce que l'on appelait en anglais un "computer" à l'époque. Le mot français "ordinateur" inventé par IBM France en 1955 pour des raisons marketing, a été choisi car ce mot était noble, bien formé, et déjà présent dans les dictionnaires pour décrire la fonction divine d'organiser les choses. Alors que le mot français fait référence à Dieux, le mot anglais décrit brutalement sa fonction initiale: organiser trier et calculer (fonction que l'on peut traduire par le verbe français compulser).
Pour simplifier, avant 1973 un ordinateur (malgré son nom français) était un objet rustique manquant de noblesse et de souplesse. On utilisait ces gros objets pour leur puissance brutale permettant des calculs mathématiques complexes, comme par exemple ceux nécessaires dans le domaine de la recherche spatiale ou nucléaire.
C'est dans ce contexte que le microprocesseur créé par Intel est apparu. Intel n'a pas eu le problème du transistor. Dès sa création, la jeune génération d'électroniciens a vite compris son potentiel. Le plus rapide d'entre eux fut André Truong, au sein d'un petite société française, qui tout comme Intel, avait besoin de réaliser à peu de frais et rapidement une fonction relativement simple de gestion automatique de température et d'hydrométrie pour des cultures agricoles. C'est le célèbre Micral qui fut crée par André Truong, avec une demi-douzaine d'électroniciens aussi efficaces que géniaux. Mais là encore, même si leur intuition a été la bonne, l'application n'a pas été la bonne. Le Micral n'était pas pleinement un micro-ordinateur, mais plutôt un circuit imprimé que l'on pouvait reprogrammer soi-même, sans devoir passer par le concepteur du composant (Intel en l'occurrence). Le microprocesseur était un circuit imprimé que l'on pouvait reprogrammer avant la livraison à un client (en résumant grossièrement). Le Micral était un système équivalent, mais que l'on pouvait reprogrammer encore plus facilement pour changer sa fonction, mais aussi à posteriori, après avoir été livré aux clients.

Même si le Micral n'était pas réellement destiné à être un micro-ordinateur, puisque la première version n'avait ni clavier, ni système de sortie (imprimante ou écran cathodique), il reste le premier établissant le principe : on pouvait acheter un système électronique, que l'on pouvait reprogrammer à sa guise, et sans effort.

Le micro-ordinateur n'invente pas vraiment ce principe, puisque les gros ou mini-ordinateurs de l'époque pouvait le faire. Mais la rupture se résumait à ces quelques avantages:

• le système coûtait 10 fois moins chers que le moins chers des ordinateurs de l'époque, 
• sa fiabilité était meilleur
• la programmation facile.

Tout cela était possible grâce au 4004 d'intel. André Truong jeune homme passionné par les transistors (entre autre) a eu la chance de se trouver au états-unis au moment de la sortie massive des premiers circuits imprimés, dont celui qui changea la mise, le 4004. C'est en se basant sur le 8008 (la célèbre version 8 bits du 4004) qu'il réalisa avec son équipe très réduite une révolution autant intellectuelle que technique, en créant le premier micro-ordinateur, bien que sans clavier et écran.
Comme souvent pour les inventeurs géniaux, il fut incompris en France. Les banques, les services publiques les entreprises ont refusé d'y croire. C'est à l'occasion d'une communication aux US que son innovation reçu l'accueil le plus intéressé, avec un article dans le magazine "Electronics". Mais ce succès d'estime n'a que peu changé les courtes vues du milieu industriel et économique français. En 1973, les US n'ont pas fait la même erreur: le Micral fut copier ou amélioré rapidement (Altair par exemple).
Le plus incroyable c'est qu'André Truong eu ensuite au moins deux intuitions géniales, chacune aurait pu apporter à la France une place dans le monde anglo-saxons de l'informatique:

• la volonté de réaliser un compatible PC (sur le modèle IBM) avant la plupart des entreprises qui se sont engouffré dans l'aubaine.
• La volonté de concevoir d'une version "portable" du Micral, avec un ordinateur réellement portable en 1980 intégrant cette fois un clavier et un écran (ou une imprimante) dans le même boîtier (PORTAL de R2E CCMC).

Pour la petit histoire, le Micral était destiné à être autonome et transportable. Cette version portable était donc logique pour cette jeune société. Avoir raison trop tôt sans en avoir les moyens de ses ambitions est tellement plus cruel que d'avoir tout simplement tort.

Pourquoi le micro-ordinateur est si important?

Cette révolution de 1973 est importante, car le "computer" est devenu un "ordinateur". Il allait devenir simple, fiable, abordable, et ainsi un ordinateur "personnel" pour la plupart des gens. Il ne fallut pas beaucoup d'année avant que le micro-ordinateur déferle sur la planète, avec le plus emblématique le ZX 80 de Synclair. Beaucoup préfère parler de l'Apple II, mais ce dernier restait réservé à une minorité ayant un minimum de moyen. Un peu comme les postes radios réservés à ses débuts à une élite, et qui après l'invention du transistor, a pu rentrer dans toutes les maisons en réduisant le prix d'achat. En devenant abordable, l'ordinateur est devenu autant un jeu intellectuelle, qu'un outils permettant de réaliser des fonctions complexes.

Avant, le computer était un objet industriel réservé à un univers d'entreprise. Après les 1973, l'ordinateur allait devenir un objet de consommation de masse, tout comme les petits postes radio à transistor, donnant toutes sortes d'idée et de possibilité au plus grand nombre. Le micro-ordinateur est l'avènement d'un univers qui allait devenir entièrement numérique, ou tout allait devenir peu à peu une suite de 0 et de 1.

Avec la démocratisation de l'objet "ordinateur", un nombre incalculable de personne a pu contribuer à l'extension du champs des possibles. Avant l'ordinateur, seul des électroniciens pouvaient concevoir des objets complexes. Cela nécessitait de souder des composants, de créer des circuits imprimées, et etc.. L'informatique et les ordinateurs ont peu à peu dépossédé les électroniciens d'une grande part de leur prérogatives. C'est d'ailleurs pour cette raison que les vrais électroniciens (que j'admire sincèrement) déteste l'informatique. Selon eux, il ne sont loin de penser qu'il n'y pas de noblesse à réaliser un système complexe entièrement numérique alors qu'avant il fallait un multitude de circuits intégrées qu'il fallait assembler patiemment.

Extraire les "CREATE TABLE" d'un dump SQL

oici un petit exercice d'application des utilitaires sed, awk et tr. L'exercice est de créer de réaliser un inventaire des colonnes d'une base de données mySQL, à partir du fichier de requête SQL, que l'on obtiens dans par PHPmyAdmin, et qui contient toutes les requêtes SQL pour reconstruire la base de données (avec ou sans les données). Voir en annexe, un exemple de ce type de fichier.
Fichier CSV à obtenir (exploitable ensuite dans excel par exemple):
Table 1; colonne 1, type
Table 1; colonne 2, type
...
Table 2; .....
......

Le but de l'exercice est donc de transformer ce fichier de requête SQL en fichier de type csv contenant au moins 3 colonnes: nom_table, nom_colonne, type_colonne. Il est clair que beaucoup de "boite à outils" mySQL peuvent le faire à peu de frais, mais ma première motivation est de m'exercer aux utilitaires sed, awk et tr.

La méthode pour y parvenir consiste à réaliser cette transformation par étapes successives. Pour cela on utilise 3 utilitaires: sed, tr, awk:

• sed est parfait pour effectuer des filtres ligne par ligne
• tr est idéal pour les remplacements ou les suppressions de caractère
• awk est efficace pour les traitements impliquant des fichiers de type csv (suite de ligne contenant des champs délimités par un caractère séparateur).

Pour effectuer un traitement complexe, le plus simple est de décomposer ce traitement en plusieurs élémentaires. Chaque traitements élémentaires permet de réaliser des tests et d'effectuer un travail progressif beaucoup plus agréable. Cela facilite également le travail de correction des erreurs.

Voici la liste des traitements élémentaires à réaliser à partir du fichier SQL:

1. Filtrer toutes les lignes parasites (en dehors des requêtes SQL)
2. Supprimer les retours à la ligne, sauf ceux en fin de requête SQL
   (cela permet d'avoir une requête SQL complète par ligne, et une seule)
3. Filtrer les requêtes autre que CREATE TABLE
4. Préparer chaque CREATE TABLE pour délimiter les 3 informations
   (avec par exemple des ;)
5. Transposer chaque ligne en autant de lignes sur la base d'une par colonne

Voici donc la liste des commandes en ligne élémentaires permettant de réaliser chacune de ces taches:

Supprimer les lignes commençant par "/*" et avec plus loin "*/". Voici la commande:
sed '/^\/\*.*\*\//d'

Suppression des lignes débutant par "--":
sed '/^--/d'

Suppression des lignes blanches ou ne contenant que des caractères blancs: sed '/^[:blank:]*$/d'

Suppression des retours à la lignes pour les fichiers de type DOS et linux.
tr "\n\r" "  "

Remplacement des ; (signifiant la fin d'une requête SQL) par un retour à la ligne. Cette commande est à faire seulement après la précédente. On aurait pu imaginer réaliser ces deux commandes "tr" en une seule fois, mais il y aurait un risque de comportement imprévisible concernant le "\n". A noter que le résultat sera un fichier ligne (texte) compatible linux (cad sans le caractère CR pour une fin de ligne):
tr ";" "\n"

Suppression des espaces en début de lignes:
sed 's/^[ ]*//'

Filtrer (supprimer) les requêtes SQL autre que CREATE TABLE:
sed -n '/^CREATE TABLE/p'

On extrait de chaque CREATE TABLE le nom de la table, et la liste des colonnes que l'on repère par des parenthèses qui son en début et fin de requête.
On sépare ces deux éléments par un point virgules pour faciliter l'extraction des colonnes CSV par la suite.
"CREATE TABLE nom_table ( liste_colonne) "
=> (sed)
 "nom_table;liste_colonne
Cela donne la ligne:
sed 's/^[^()]* \([^() ]*\) (\(.*\))[^()]*$/\1;\2/'

On supprime ensuite les virgules parasites pouvant se trouver dans la liste des colonnes, par exemple dans le type de colonne et que l'on repère à l'intérieur d'une chaîne entourée par les caractères "(" et ")" . Le but est de pouvoir ensuite remplacer les virgules de la liste des colonnes par des point-virgules pour permettre un traitement aisé par awk.
Par exemple un type de colonne  FLOAT[(length,decimals)]
contient une virgule parasite (en dehors des virgules séparant une colonne) qui sera remplacé par un espace:
sed 's/(\([^(),]*\),\([^(),]*\))/(\1 \2)/g'

Il reste pour en finir avec les virgules parasites, éliminer les virgules à l'intérieur des déclarations INDEX et KEY, qui peuvent contenir de nombreuses virgules. Pour simplifier, on supprime tout simplement les groupes de parenthèses ouvrantes/fermantes contenant au moins une virgule:
sed 's/([^()]*,[^()]*)//g'


Pour remplacer les caractères virgules par des point-virgules:
tr "," ";"


La dernière étape est de transposer chaque lignes en autant de ligne que de colonnes sur le schéma suivant, avec une ligne de départ:
"A;B;C;D;E;......" :
=>(awk)
A;B;
A;C;
A;D;
A;E;
.....
Voici la ligne de commande correspondante:
awk 'BEGIN { FS= ";"; OFS="\n" }
{ for (i=2;i<=NF;i++) {print $1 ";" $i } }'


Un peu de toilettage pour finir en supprimant les espaces répétitifs:
sed 's/[ \t][ \t]*/ /g'

Ajout d'un ; après le nom de la colonne qui est entouré par des espaces:
sed 's/^\([^ ]* [^ ]*\) \(.*\)$/\1;\2/'

Et pour finaliser, suppression des lignes INDEX et KEY:
sed -e '/;PRIMARY;/d' -e '/;KEY;/d'

Pour finir, il suffit de mettre toutes ces lignes de commande dans un script bash. Ce script aurait pu se résumer à une longue succession de tubes ("|") avec en entrée le fichier source SQL, mais j'ai préféré conserver 1 fichier intermédiaire contenant les requêtes SQL "CREATE TABLE" seules et complètes dans chaque ligne. Ce script a besoin en entrée du nom du fichier source.  Deux fichiers sont crée ensuite par la procédure: un contenant seulement les CREATE TABLE; puis le fichier CSV final contenant l'inventaire des noms de colonnes de chaque table.

Cela donne un bricolage relativement indigeste mais pouvant servir par la suite d'une base pour un travail équivalent. Rien ne vaut le bricolage pour apprendre le métier tout en s'amusant.


Liens:
Fichier script correspondant en UTF8 pour bash
Introduction à awk d'une grande clarté
Introduction idéale à bash

Exemple de fichier de requête SQL:


-- http://www.phpmyadmin.net
--
-- Client: localhost
-- Généré le : Lun 06 Juillet 2015 à 18:04
-- Version du serveur: 5.5.16
-- Version de PHP: 5.3.8
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
--
-- Base de données: `45tours`
--
-- --------------------------------------------------------
--
-- Structure de la table `artiste`
--
CREATE TABLE IF NOT EXISTS `artiste` (
`ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
.....

Export / import de fichier CSV vers une base SQL : eternel soucis du caractère délimitant un texte

La vie d'un fichier CSV serait si simple, si les colonnes ne pouvaient pas contenir du texte libre. Pour rappel, la structure d'un fichier CSV est simple

• Une ligne correspond à un enregistrement
  (cad une ligne d'une table de la base de donnée)
• Les lignes sont délimitées par un ou 2 caractères de fin de ligne
  (au choix parmi CR et LF - code ASCCI 13 et 10).
• Les colonnes sont délimités par un caractère spéciale
  (traditionnellement ; espace tabulation ou virgule).

Si aucune de vos colonnes (cad les champs d'un enregistrement) ne peuvent contenir l'un de ces 3 caractères, aucun soucis. Cette structure de fichier CSV serait indestructible. Malheureusement, rien n’empêche un champ numérique de contenir des virgules, et pour un champs texte de contenir des CR, LF ou autre point-virgule. Dans ces conditions, le formats CSV devient un enfer pavé de bonnes intentions.

Pour résoudre ce problème des caractères parasites à l'intérieur d'une colonne (cad d'un champ d'une ligne/enregistrement), la plupart des fichiers CSV encapsule les colonnes par des guillemets (ou des simples quottes). Ce caractère devient alors une barrière contre le problème des caractères parasites (fin de ligne, délimiteur de colonne). Encore faut-il pouvoir également s'affranchir du caractère guillemet parasite, c'est à dire s'affranchir du problème des caractères guillemets présent dans des colonnes du CSV. A ce niveau, seul l'outil d'export de fichiers CSV peut gérer le problème des caractères parasites. Pour résoudre ce soucis, les outils d'export de CSV (Excel par exemple) échappent les guillemets (le caractère choisi pour encapsuler les colonnes du CSV) avec un caractère spécial. Dans Excel par exemple, le caractère d'échappement du guillemet est le guillemet lui-même. Un guillemet présent dans une colonne sera alors remplacé par un double guillemet.C'est ainsi qu'un double guillemet dans un CSV, ne sera pas considéré comme un caractère encapsulant une colonne.

Pour décrire cette mécanique, voici un exemple d'un ligne d'un fichier CSV à 2 colonnes, avec comme délimiteur de colonne le point-virgule:
CODEnnn;texte libre  .
Ce premier exemple est présenté sans aucun caractère encapsulant les colonnes. Si une des colonnes contenait un point virgule, il deviendrait impossible de déterminer les limites d'une colonne comme par exemple:
CODEnnn;texte libre avec un ; (point-virgule) ...
Pour cette raison, les colonnes sont encapsulées par un caractère (dans notre exemple, le guillemet) signifiant le début et la fin d'une colonne de type texte, et qui permet d'annuler le ; inclus dans la colonne:
"CODEnnn";"texte libre avec un ; (point-virgule) ..."
Dans ce cas tout se passe bien, sauf si un guillemet est lui même présent dans la colonne:
"CODEnnn";"texte libre avec un ";" (point-virgule) ..."
Dans ce cas, c'est à l'outil d'export de CSV de faire le travail d'inclure un caractère d'échappement du guillemet. Pour Excel, le caractère d'échappement par défaut est ". Notre exemple devient alors:
"CODEnnn";"texte libre avec un "";"" (point-virgule) ..."

A partir de ces deux caractères (celui d'échappement et celui d'encapsulation d'une colonne), la structure d'un CSV devient robuste, et résiste même aux caractères CR et LF parasites.

Encore faut-il vérifier ou paramétrer votre outil d'export de fichiers CSV pour que ce mécanisme s'active convenablement. Si vous maitrisez votre outil d'export pour appliquer ce mécanisme, vous pourrez alors paramétrer votre outils d'import de fichier CSV. Dans le cas d'Excel (en tant que import et export), pas de soucis, le " est le caractère d'échappement et encapsulation de colonne. Mais dans le cas d'une base de données SQL, cela peut jouer des tours. Si par exemple vous passez par un outil client SQL (par exemple l'excellent HEIDI), pour réaliser des imports, 2 choix s'offrent à vous: un import coté client (en exécutant des UPDATE généré par la lecture local du fichier CSV) ou un import déporté (en utilisant l'utilitaire d'import native de la base de données). Vous serez étonné de découvrir que le même fichier CSV pourra être en échec dans un cas et OK dans l'autre. D'où la nécessité de réaliser de nombreux test.

Le format CSV comme format pivot pour des bases de données est intéressant pour sa simplicité, mais à la seule et unique condition de maitriser parfaitement votre chaine d'export ET d'import de ces fichiers.

PS:
Pensez au problème du charset. Problème relativement simple mais tout aussi critique que nos petits caractères parasites. Et pour ce problème, les outils sont souvent mal documentés.

Filtrer les guillements parasites d'un fichier CSV destiné à être importé dans une base de donnée

Les imports de bases de données à l'aide de fichiers CSV sont toujours critiques, car de nombreux facteurs peuvent déraper. Pour rappel, un fichier CSV est:

• 1 fichier texte
• dont chaque ligne correspond à un enregistrement d'une table
• et dont les lignes sont découpées en colonne par un délimiteur

Pour importer correctement un fichier CSV, il faut se mettre d'accord sur de nombreux éléments: le caractère délimiteur de colonne (souvent le ;), le ou les délimiteurs de ligne (CR-LF sous DOS, LF sous unix, ou CR pour d'autre), et sur le "charset" (la table de correspondance ASCCI, UTF8, unicode, etc...).

Malheureusement, il faut également se préoccuper du caractère délimiteur des chaines de caractères que l'on trouve dans la plupart des colonnes d'un CSV, comme par exemple des libellés. Traditionnellement, on utilise les guillemets (") pour délimiter une colonne de type texte (chaine de caractère).  Ainsi, une ligne de fichier CSV peut se présenter comme cela:
"CODEnnn";"xxxxxxxxx xxxx xxxx"
Le problème est que très souvent, il y a des caractères guillemets dans ces colonnes textes. Voici un exemple d'un CSV de 2 colonnes, contenant des guillemets parasites:
"CODEnnn";"xxxxxxxxx "xxxx" x""xxx""
Dans ce cas, l'utilitaire d'import ne pourra pas fonctionner correctement. Le but de cet article est de filtrer ces caractères guillemets parasites, présents dans des colonnes d'un CSV.

Pour filtrer ces guillemets parasites, utilisons l'utilitaire SED, outils unix rapide et pratique pour ce genre d'actions. SED applique des actions ligne par ligne sur des fichier texte. L'action (la commande) SED qui nous intéresse est la substitution de caractère. On va demander à SED, de remplacer tous les guillemets parasites par une simple quotte ('). On demande à SED de réaliser cette substitution uniquement sur les guillemets précédés par un caractère différent de ";", et suivi par un caractère lui aussi différent de ";". Le ";" étant le délimiteur de colonne du fichier CSV. En résumé, tous les guillemets seront remplacés, sauf ceux en début de ligne, sauf ceux en fin de ligne, et sauf ceux qui se présente comme cela (";"), cad entourés par au moins 1 point-virgule.

La syntaxe SED que j'utilise est la suivante:
sed -f instructions.sed fichier_CSV
Je préfère utiliser la syntaxe permettant d'écrire les instructions (les commandes) SED dans un fichier, car il est plus facile à éditer, et permet d'insérer des commentaires salutaires pour une relecture quelques jours ou quelques mois plus tard.

La syntaxe de la commande de substitution que j'utilise est la suivante:
s/Exp_Reg_chaine_à_remplacer/nouvelle_chaine/g
Le dernier caractère "g" signifie que cette action de substitution se fera pour toutes les sous-chaines de la ligne respectant l'expression régulière.

L'expression régulière dans notre cas est relativement simple:
[^;]"[^;]
Cela correspond à une sous-chaine de 3 caractères, dont le premier et le dernier ne sont pas un ;, et dont le deuxième est obligatoirement un guillemet.
Cela donne une commande sed:
s/\([^;]\)"\([^;]\)/\1'\2/g

\1 et \2 correspondent respectivement au premier caractère et au 3ème caractère de la sous-chaine correspondante à l'expression régulière. Il suffit de mettre cette commande dans un fichier texte, et d’appeler la commande SED
sed -f instruction.sed fichier.csv > fichier_filtre.csv

Malheureusement, cette commande est inefficace sur certains caractères guillemet parasite.
Comme par exemple cette ligne CSV:
"CODEnnn";"xxxxxx"""""xxx "xxxx" x""xxx""

L'explication est simple: le parseur d'expression régulière ne s'applique pas 2 fois sur la même zone texte. Quand une expression régulière est trouvé par le parseur, la zone trouvée est supprimée pour les recherches suivante du parseur (pour la même instruction sed). Pour une chaine de 4 guillemets consécutifs (xx""""xx), le parseur détectera le premier guillemet, mais pas les deux suivants. Il détectera tout de même le 4 ième.

Pour résoudre le problème, il faut appliquer la commande de substitution 2 fois pour obliger le parseur à refaire la recherche. Il faut même relancer une 3ème fois pour supprimer le cas de guillemets parasites qui serait encore une fois espacés d'un seul caractère.
s/\([^;]\)"\([^;]\)/\1'\2/g  # 1er fois
s/\([^;]\)"\([^;]\)/\1'\2/g  # une 2ème fois
s/\([^;]\)"\([^;]\)/\1'\2/g  # 3ème et dernière fois

Et pour finir, il faut gérer également le problème des CR-LF dans SED. Si votre fichier CSV vient de DOS, SED va considérer le CR (code ASCII 13) comme un caractère comme un autre. Ce qui déclenchera la substitution de dernier guillemet. Il faut donc supprimer préalablement le CR:

s/\x0D$//g

Ne pas oublier si nécessaire de réaliser la commande inverse.
Pour être complet, penser à filtrer les ; en fin de ligne. Le CSV peut parfois avoir ce genre de colonne vide. 2 choix s'offre à vous: les supprimer
 s/;$//
ou les remplacer
 s/;$/; / # on ajoute un espace

Tout ça pour ça. Voici le fichier d’instruction SED pour filtrer nos guillemets parasites:
s/\x0D$//g    # conversion fichier DOS => unix (\r = 13 en ascii)
s/;$// # suppression des ; en fin de ligne
s/\([^;]\)"\([^;]\)/\1'\2/g # conversion des " en ' si le précédent n'est pas ;, ni le suivant
s/\([^;]\)"\([^;]\)/\1'\2/g # à refaire pour les "" contigus
s/\([^;]\)"\([^;]\)/\1'\2/g # à faire en tout 3 fois
s/$/\r/    #conversion fichier texte unix => DOS

La dernière ligne peut poser une soucis selon le SED que vous utilisez. Si votre sed est un exécutif unix, pas de soucis. En revanche, si c'est une version DOS, cela peut poser problème, car ce dernier ajoutera en fin de ligne des CR en plus du LF. Si c'est le cas, voici la commande pour supprimer les CR à postériori:
sed -i "s/\r//" fichier_filtré.csv


L'histoire serait belle si seulement, il n'y avait le cas des délimiteurs parasites.
Exemple de CSV avec 2 colonnes seulement:
"CODEnnnnn";"libellé ";" titre"
Dans ce cas, les 2 guillemets parasites ne seront pas filtrés par la commande sed. Concrètement, les sous-chaines de 2 caractères associant 1 guillemet et 1 point-virgule rendrons inefficace la commande sed conçu ci-dessus. Pour ce dernier cas, je n'ai pas de solution simple. D'ailleurs, comment humainement faire la différence entre un délimiteur et un simple caractère dans ce cas précis: la ligne précédente peut autant être interprétée comme une ligne de 2 colonnes ou une ligne de 3 colonnes. De quoi rendre fou un outil d'import de CSV. Ce cas particulier mérite un article à part entière.

Contentons-nous aujourd'hui de jouir de notre ignorance concernant ce cas perfide, et rappelons nous que dans tout voyage, le chemin est plus important que la destination ;)


PS :
Pour info, voici la commande SED permettant de vérifier la présence de caractère parasite:
/[^;]"[^;]/!d
Cette commande signifie "supprimez toutes les lignes qui ne contienne pas au moins une sous-chaines de 3 caractère décrite par l'expression régulière en question.

liens;
Exemples sed d'une grande clarté 
Introduction rapide à sed
Explication approfondie du fonctionnement de sed
Bases pour comprendre les Expressions Régulières
Boite à outils sed (liste d'exemples très variés)

HTML et le désespoir des TABLE en folie: IMG dont la hauteur ne veut pas s'adapter verticalement dans un TD

C'est le drame du HTML, langage qui se voulait simple à l'origine, et qui avec le temps est devenu complexe. Avant de pleurer sur le temps perdu et la nostalgie informatique, voici le drame qui me chagrine.

En ressortant un vieux développement PHP, j'ai découvert que le code HTML basé sur un tag TABLE classique provoquait un affichage aberrant. Voici le principe en pseudo HTML de cet affichage:

<TABLE>
<TR><TD> <IMG src="coin_0.gif"></TD>
<TD> <IMG src="barre_h_h.gif"> </TD>
<TD> <IMG src="coin_1.gif"></TD> </TR>
<TR><TD> <IMG src="barre_v_g.gif" /></TD>
<TD>...texte sur n lignes....</TD>
<TD> <IMG src="barre_v_d.gif" /></TD> </TR>
<TR><TD> <IMG src="coin_3.gif"></TD>
<TD> <IMG src="barre_h_b.gif"></TD>
<TD> <IMG src="coin_2.gif"></TD> </TR>
</TABLE>

Je sais que beaucoup vont parler de code archaïque (en insistant sur le caractère péjoratif), mais j'aime la simplicité, et la logique implacable d'un TABLE sur un DIV, ou autres éléments HTML servant pour les mises en page. Mais cela est une autre histoire. Le code ci dessus marchait à l'aide des attributs permettant de réduire le "border", le "spacing", ou autre "margin" ( border="0" cellspacing="0" cellpadding="0"). Pour que les images s'adaptent parfaitement à la taille dynamique des cellules du tableau, il suffisait de mettre des "height="100%" ou width="100%" sur les bons TD. Le principe est de mettre des dimensions fixes pour les 4 "coins", et de mettre ensuite 100% sur les witdh des images devant s'élargir horizontalement, et 100% sur les "height" des images devant s'élargir en hauteur. En revanche, la case du milieu contenant le texte sur plusieurs lignes, est sans dimensions.

Cela donnait tout simplement cela:

La même chose avec un "border=1" pour visualiser les 9 cases de mon tableau:

Pour la petite histoire, ce type de code HTML, me permettait d'afficher simplement des graphsets, de plusieurs types, avec des bords ronds, carrées ou autres. En final, j'ai amèrement découvert que mon code HTML après quelques années de mise en sommeil donnait ce résultat :

Le plus étonnant, c'est que les images s'adaptent parfaitement en largeur (sauf 0.2px sur la droite, Dieu sait pourquoi), mais seulement partiellement sur la hauteur. En utilisant l'inspecteur d'éléments dans le navigateur, on constate une ligne blanche en dessous de l'image, et une autre au dessus, interprétées par l'inspecteur comme un "padding" (en vert sur la copie d'écran ci-dessus).
Pour Tenter de résoudre mon problème, j'ai donc nettoyé le code en utilisant des propriétés CSS à la place des attributs (souvent devenu obsolètes, mais opérationnel théoriquement). Voici le code en question:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">

<HTML> <HEAD> <style type="text/css"> table { border-collapse: collapse; /* Colle les bordures entre elles */ border-spacing: 0; margin: 0 0 0 0 ; padding: 0 0 0 0 ;
border: 0; } td { margin: 0; padding: 0 ; border-spacing: 0; } tr { margin: 0; padding: 0 ; border-spacing: 0; } </style> <TITLE>Table avec images extensibles </TITLE> </HEAD> <BODY> <BR> <BR> <CENTER> <TABLE> <tbody> <TR><TD width="19" height="19"><IMG alt="" width="19" height="19" src="el/fin_coin_0.gif"></TD> <TD height="19"><IMG alt="" height="19" width="100%" src="el/fin_barre_h_h.gif"></TD> <TD width="19" height="19"><IMG alt="" width="19" height="19" src="el/fin_coin_1.gif"></TD> </TR> <TR><TD width="19" height="100%"><IMG alt="" width="19" height="100%" src="el/fin_barre_v_g.gif" /></TD> <TD>Les plus désespérés sont les chants les plus beaux,<BR>Et j'en sais d'immortels qui sont de purs sanglots.<BR><BR>La muse<BR>Alfred de Musset (1810-1857)</TD> <TD width="19" height="100%" style="font-size: 0;line-height:0;"><IMG style="font-size: 0;line-height:0;" alt="" width="19" height="100%" src="el/fin_barre_v_d.gif" /></TD> </TR> <TR><TD width="19" height="19"><IMG alt="" width="19" height="19" src="el/fin_coin_3.gif"></TD> <TD height="19"> <IMG alt="" height="19" width="100%" src="el/fin_barre_h_b.gif"></TD> <TD width="19" height="19"><IMG alt="" width="19" height="19" src="el/fin_coin_2.gif"></TD> </TR> </tbody> </TABLE> </CENTER> </BODY>

Je peux vous assurer avoir essayé toutes les propriétés CSS possibles sur les TD et IMG pour faire disparaitre ces deux lignes blanches non désirées, sans succès. (display, box-sizing, line-height, font-size, ...). J'ai tenté également de mettre un DIV pour encapsuler l'IMG, avec un "height" à 100%, sans succès.

Après quelques heures éprouvantes, j'ai testé le même code HTML sur un linux, les tests précédents ont été réalisés sous Windows 7 avec Opera, Chrome, IE. Et bingo, le code HTML s'affichait correctement avec firefox. De nouveau de retour sur W7, et bing encore: Firefox affiche correctement le code HTML. La malchance d'avoir testé sur 3 navigateurs, et d'oublier FF.

Maintenant j'ai une piste. Le problème vient donc de certains navigateurs, certainement racistes envers les tag TABLE. Je m'en doutais, car depuis l'inquisition espagnole, on a rarement vue un tel sectarisme religieux concernant ces pauvres TABLE parmi la communauté HTML.

Me voila donc reparti sur une recherche de solution pour tous les navigateurs.
Suite au prochain épisode (je l'espère, sinon cela veut dire que le sectarisme a gagné ;)

Zip contenant l'exemple