Bash csvsql: parser un fichier csv avec des commandes sql
Description
Script php en ligne de commande, permettant de lire un fichier CSV en définissant des tris via une requète SQL.
Ex :
~$ csvSql.php -gzfile insee.csv.gz -sql "SELECT * WHERE Departement ~ 'vendee' LIMIT 10,20 "
Cette solution offre quelques possibilités tel que les alias, les fonctions sur les colonnes, plusieurs opérateurs pour la clause WHERE, un LIMIT, une sortie xml, la définition d'un séparateur d'entrée (et de sortie).
Ci-dessous l'aide (obtenue par l'argument -help):
php csvSql.php -help 'item' pour une rubrique particulière (ou item peut etre):
intro | lstArgs | lstOp | general | file | gzfile | sql | noColName | sep | enclosure | sepOut | toXml | specialXmlCols | patternSpecialCol | debug | help | annexe
La ligne de commande générale:
csvSql.php -[args] [value]
Liste des arguments possibles :
file gzfile sql noColName sep enclosure sepOut toXml specialXmlCols patternSpecialCol include debug help
Liste des opérateurs d'une clause WHERE:
a = 'b' equivaut a == b
a != 'b' equivaut a != b
a ~ 'b' equivaut stristr( a , b) !== false
a ~~ 'b' equivaut strpos( a , b) !== false
a ! 'b' equivaut stristr( a , b) === false
a !! 'b' equivaut strpos( a , b) === false
a <= 'b' equivaut a une comparaison si a et b
sont de type string ('az' <= 'za' == true ),
sinon comportement normal avec du numeric
a >= 'b' meme spécificité avec le strings
a > 'b' meme spécificité avec le strings
a < 'b' meme spécificité avec le strings
a IN ('b','c'...) meme spécificité avec le strings
a #> b strlen(a) > b
a #< b strlen(a) < b
a #= b strlen(a) == b
csvSql est un script PHP à utiliser en ligne de commande.
Il permet de lister un fichier CSV en prennant compte des valeurs contenues dans ce fichier a
l'aide d'une requète SQL, tout comme le fait mysql en client shell.
Toutefois, etant un script PHP, le but n'est pas de faire du SQL pur comme le ferait une base de données
(jointure et autre) mais bel et bien un filtre basé sur des commandes SQL, de type OR AND IN.
Un exemple sera plus parlant:
php csvSql.V2.php -gzfile 10000_Client.csv.gz -sql "SELECT CONCAT('prenom ','->', firstName)
AS PRENOM, strtoupper(lastName) AS NOM WHERE firstName ~~ 'F' LIMIT 10 "
PRENOM | NOM
--------------
prenom ->Frederic | BONJOU
prenom ->Fabrice | WORM
prenom ->Fabienne | SIVAD
prenom ->Full | BEAUTIFUL
prenom ->Faye | ROBBIE
prenom ->Fred | CATTLE
prenom ->Florent | SCENARIO
prenom ->Fly | NIGHT
prenom ->Filou | SAVALLI
prenom ->Florence | MODELLE
Nb result(s) (on Total read 51): 10. (time: 0.067447900772095s)
Notez qu'il est possible d'utiliser des ALIAS avec l'instruction AS,
et qu'il est possible d'ajouter des traitements par le biais de fonctions php, ou de fonctions
dédiées (voir fonctions dédiées).
il est aussi possible d'imbriquer des clauses OR / AND par des parenthèses, comme cela se fait en SQL.
Des exemples de commande sont dans la rubrique 'annexe'.
-file pathFile
L'option file définit le path d'un fichier CSV de type text. Si seule cette option est demandée,
seuls les noms colonnes seront affichés. (utile pour faire un gros select;)).
-gzfile pathFile
Même chose que l'argument -file pour les fichiers compressés GZ.
-sql "string"
L'argument SQL définit la chaine SQL (impérativement entre double-quote). Tous les mots clés
SQL sont en majuscule. La clause WHERE est optionnelle sauf si l'instruction LIMIT est demandée.
Ainsi, pour renvoyer les dix premières lignes d'un fichier sans filtre particulier:
SELECT * WHERE LIMIT 10
Notez que vous pouvez utiliser '*' qui renvoit toutes les colonnes. Vous noterez aussi que
l'instruction FROM n'existe pas, car nous avons a faire a un seul fichier (peut etre dans une future release;))
-noColName
Par défaut, csvSql recherche si des noms colonnes existent. Elles n'existent que si la première ligne du fichier
contient des valeurs qui ne contiennent pas d'espace (' '). C'est un test rudimentaire. Si vous savez que votre
fichier ne contient pas de nom de colonne, l'argument noColName force csvSql a nommer les champs par:
'c1' pour la première colonne, 'c2' pour la seconde ...
Ainsi vous pourrez toujours untiliser les colonnes:
SELECT c1, c6 WHERE c3 = 'test' LIMIT 10
-sep separator
Par défaut, le separateur d'un fichier CSV est fixé à ';', mais il est possible de changer le séparateur,
il peut comporter plusiers caractères.
-enclosure char
Définit le caractère d'encapsulage des données. Par défaut '"'.
-sepOut separator
Définit le séparateur de sortie. Peut permettre par exemple de reformater un fichiers CSV,
avec des séparateurs différents.
-toXml nodeName
Il est possible ecrire les résultats directement en XML. Dans ce cas, la représentation des noms de
colonnes ne sera pas affichée pour éventuellement enregistrer les résultats dans un nouveau fichier
a l'aide d'un ' > newXml.xml' en fin de commande.
nodeName est le nom du noeud pour chaque élement (voir exemple)
exemple :
php csvSql.php -gzfile 10000_Client.csv.gz -toXml CLIENT -sql "SELECT CONCAT('prenom ','->', firstName)
AS PRENOM, strtoupper(lastName) AS NOM WHERE firstName ~~ 'F' LIMIT 10 "
<CLIENT>
<PRENOM>prenom ->Frederic</PRENOM>
<NOM>BONJOU</NOM>
</CLIENT>
<CLIENT>
<PRENOM>prenom ->Fabrice</PRENOM>
<NOM> WORM</NOM>
</CLIENT>
<CLIENT>
<PRENOM>prenom ->Fabienne</PRENOM>
<NOM>SIVAD</NOM>
</CLIENT>
......
-specialXmlCols "string"
Certaines valeurs de colonnes peuvent contenir plusieurs infos qui peuvent etre mises en sous element
d'un noeud.
ex, pour une colonne nommée 'villes', on peut avoir une valeur de type '92160::Antony#75000::Paris'.
Avec l'argument specialXmlCols et l'argument patternSpecialCol, csvSql est a meme d'afficher un element fils
de type :
<villes>
<ville>
<name>Antony</name>
</ville>
<ville>
<name>Paris</name>
</ville>
</villes>
Pour ce faire, l'argument specialXmlCols devra avoir comme valeur les tagnames désirés.
Pour notre exemple ce sera: -specialXmlCols 'villes|name;;code'
suivi de pattern qui permet d'extraire les données de cette valeur:
-patternSpecialCol '/(\d+)::([\w ]+)/'
la commande complete :
php csvSql.php -gzfile 10000_Client.csv.gz -toXml CLIENT -specialXmlCols 'villes|name;;code' -patternSpecialCol '/(\d+)::([\w ]+)/'
-sql "SELECT CONCAT('prenom ','->', firstName)
AS PRENOM, strtoupper(lastName) AS NOM,villes WHERE firstName ~~ 'F' LIMIT 10 "
-patternSpecialCol "string pattern"
Voir 'specialXmlCols'
-debug
Affichage du découpage de parse (object parse)
-help "string item"
L'argument affiche l'aide (trop fort), si une valeur d'item est donnée, l'aide affichera exclusivement
cet item.
ANNEXE :
Le SELECT peut se composer de plusieurs colonnes, ainsi que plusieurs Alias. Une colonne a qui
on applique une fonction doit impérativement contenir un alias.
SELECT REPLACE('fred','toto', firstName) AS prenom
Vous pouvez imbriquer plusieurs fonctions pour une colonne:
SELECT CONCAT(REPLACE('fred','toto', firstName) ,' : ', lastName ) AS nomComplet
Tous les mots clé SQL doivent etre majuscule.
Vous pouvez utiliser des fonctions php, ou bien les fonctions dédiées nommées csvSql_function dans
le fichier SqlParser.inc. Vous avez aussi la possibilité de brider l'utilisation de fonctions qu'a
ces seules fonctions en passant l'attribut '$functionLocalOnly' a true.
La clause WHERE :
La clause WHERE se compose de noms de colonnes ou d'alias. Tous comme les fonctions, vous
pouvez imbriquer les clauses OR / AND .
SELECT firstName, firstName as prenom WHERE firstName ~ 'F' OR ( firstName ~ 'fred' AND prenom = 'frederic')
Sans doute certains trouverons la solution trop lourde pour parser un fichier CSV, mais si comme moi, vous manipulez souvent ce type de fichier, alors peut etre est ce une solution.
J'ai voulu par la suite, creer un Linq like, tel C#, j'ai vu que ca existait deja (phpLinq), inutile donc, de réinventer la roue. De plus, phpLinq offre des possibilités de 'sort' qui ne sont pas implémentées dans csvSql qui a vocation traiter de gros fichiers.
Si certains d'entre vous veulent un tuto sur ce bash ( comme le suggère Malalam ), n'hésitez pas a me le demander.
Ex :
~$ csvSql.php -gzfile insee.csv.gz -sql "SELECT * WHERE Departement ~ 'vendee' LIMIT 10,20 "
Cette solution offre quelques possibilités tel que les alias, les fonctions sur les colonnes, plusieurs opérateurs pour la clause WHERE, un LIMIT, une sortie xml, la définition d'un séparateur d'entrée (et de sortie).
Ci-dessous l'aide (obtenue par l'argument -help):
php csvSql.php -help 'item' pour une rubrique particulière (ou item peut etre):
intro | lstArgs | lstOp | general | file | gzfile | sql | noColName | sep | enclosure | sepOut | toXml | specialXmlCols | patternSpecialCol | debug | help | annexe
La ligne de commande générale:
csvSql.php -[args] [value]
Liste des arguments possibles :
file gzfile sql noColName sep enclosure sepOut toXml specialXmlCols patternSpecialCol include debug help
Liste des opérateurs d'une clause WHERE:
a = 'b' equivaut a == b
a != 'b' equivaut a != b
a ~ 'b' equivaut stristr( a , b) !== false
a ~~ 'b' equivaut strpos( a , b) !== false
a ! 'b' equivaut stristr( a , b) === false
a !! 'b' equivaut strpos( a , b) === false
a <= 'b' equivaut a une comparaison si a et b
sont de type string ('az' <= 'za' == true ),
sinon comportement normal avec du numeric
a >= 'b' meme spécificité avec le strings
a > 'b' meme spécificité avec le strings
a < 'b' meme spécificité avec le strings
a IN ('b','c'...) meme spécificité avec le strings
a #> b strlen(a) > b
a #< b strlen(a) < b
a #= b strlen(a) == b
csvSql est un script PHP à utiliser en ligne de commande.
Il permet de lister un fichier CSV en prennant compte des valeurs contenues dans ce fichier a
l'aide d'une requète SQL, tout comme le fait mysql en client shell.
Toutefois, etant un script PHP, le but n'est pas de faire du SQL pur comme le ferait une base de données
(jointure et autre) mais bel et bien un filtre basé sur des commandes SQL, de type OR AND IN.
Un exemple sera plus parlant:
php csvSql.V2.php -gzfile 10000_Client.csv.gz -sql "SELECT CONCAT('prenom ','->', firstName)
AS PRENOM, strtoupper(lastName) AS NOM WHERE firstName ~~ 'F' LIMIT 10 "
PRENOM | NOM
--------------
prenom ->Frederic | BONJOU
prenom ->Fabrice | WORM
prenom ->Fabienne | SIVAD
prenom ->Full | BEAUTIFUL
prenom ->Faye | ROBBIE
prenom ->Fred | CATTLE
prenom ->Florent | SCENARIO
prenom ->Fly | NIGHT
prenom ->Filou | SAVALLI
prenom ->Florence | MODELLE
Nb result(s) (on Total read 51): 10. (time: 0.067447900772095s)
Notez qu'il est possible d'utiliser des ALIAS avec l'instruction AS,
et qu'il est possible d'ajouter des traitements par le biais de fonctions php, ou de fonctions
dédiées (voir fonctions dédiées).
il est aussi possible d'imbriquer des clauses OR / AND par des parenthèses, comme cela se fait en SQL.
Des exemples de commande sont dans la rubrique 'annexe'.
-file pathFile
L'option file définit le path d'un fichier CSV de type text. Si seule cette option est demandée,
seuls les noms colonnes seront affichés. (utile pour faire un gros select;)).
-gzfile pathFile
Même chose que l'argument -file pour les fichiers compressés GZ.
-sql "string"
L'argument SQL définit la chaine SQL (impérativement entre double-quote). Tous les mots clés
SQL sont en majuscule. La clause WHERE est optionnelle sauf si l'instruction LIMIT est demandée.
Ainsi, pour renvoyer les dix premières lignes d'un fichier sans filtre particulier:
SELECT * WHERE LIMIT 10
Notez que vous pouvez utiliser '*' qui renvoit toutes les colonnes. Vous noterez aussi que
l'instruction FROM n'existe pas, car nous avons a faire a un seul fichier (peut etre dans une future release;))
-noColName
Par défaut, csvSql recherche si des noms colonnes existent. Elles n'existent que si la première ligne du fichier
contient des valeurs qui ne contiennent pas d'espace (' '). C'est un test rudimentaire. Si vous savez que votre
fichier ne contient pas de nom de colonne, l'argument noColName force csvSql a nommer les champs par:
'c1' pour la première colonne, 'c2' pour la seconde ...
Ainsi vous pourrez toujours untiliser les colonnes:
SELECT c1, c6 WHERE c3 = 'test' LIMIT 10
-sep separator
Par défaut, le separateur d'un fichier CSV est fixé à ';', mais il est possible de changer le séparateur,
il peut comporter plusiers caractères.
-enclosure char
Définit le caractère d'encapsulage des données. Par défaut '"'.
-sepOut separator
Définit le séparateur de sortie. Peut permettre par exemple de reformater un fichiers CSV,
avec des séparateurs différents.
-toXml nodeName
Il est possible ecrire les résultats directement en XML. Dans ce cas, la représentation des noms de
colonnes ne sera pas affichée pour éventuellement enregistrer les résultats dans un nouveau fichier
a l'aide d'un ' > newXml.xml' en fin de commande.
nodeName est le nom du noeud pour chaque élement (voir exemple)
exemple :
php csvSql.php -gzfile 10000_Client.csv.gz -toXml CLIENT -sql "SELECT CONCAT('prenom ','->', firstName)
AS PRENOM, strtoupper(lastName) AS NOM WHERE firstName ~~ 'F' LIMIT 10 "
<CLIENT>
<PRENOM>prenom ->Frederic</PRENOM>
<NOM>BONJOU</NOM>
</CLIENT>
<CLIENT>
<PRENOM>prenom ->Fabrice</PRENOM>
<NOM> WORM</NOM>
</CLIENT>
<CLIENT>
<PRENOM>prenom ->Fabienne</PRENOM>
<NOM>SIVAD</NOM>
</CLIENT>
......
-specialXmlCols "string"
Certaines valeurs de colonnes peuvent contenir plusieurs infos qui peuvent etre mises en sous element
d'un noeud.
ex, pour une colonne nommée 'villes', on peut avoir une valeur de type '92160::Antony#75000::Paris'.
Avec l'argument specialXmlCols et l'argument patternSpecialCol, csvSql est a meme d'afficher un element fils
de type :
<villes>
<ville>
<name>Antony</name>
92160
</ville>
<ville>
<name>Paris</name>
75000
</ville>
</villes>
Pour ce faire, l'argument specialXmlCols devra avoir comme valeur les tagnames désirés.
Pour notre exemple ce sera: -specialXmlCols 'villes|name;;code'
suivi de pattern qui permet d'extraire les données de cette valeur:
-patternSpecialCol '/(\d+)::([\w ]+)/'
la commande complete :
php csvSql.php -gzfile 10000_Client.csv.gz -toXml CLIENT -specialXmlCols 'villes|name;;code' -patternSpecialCol '/(\d+)::([\w ]+)/'
-sql "SELECT CONCAT('prenom ','->', firstName)
AS PRENOM, strtoupper(lastName) AS NOM,villes WHERE firstName ~~ 'F' LIMIT 10 "
-patternSpecialCol "string pattern"
Voir 'specialXmlCols'
-debug
Affichage du découpage de parse (object parse)
-help "string item"
L'argument affiche l'aide (trop fort), si une valeur d'item est donnée, l'aide affichera exclusivement
cet item.
ANNEXE :
Le SELECT peut se composer de plusieurs colonnes, ainsi que plusieurs Alias. Une colonne a qui
on applique une fonction doit impérativement contenir un alias.
SELECT REPLACE('fred','toto', firstName) AS prenom
Vous pouvez imbriquer plusieurs fonctions pour une colonne:
SELECT CONCAT(REPLACE('fred','toto', firstName) ,' : ', lastName ) AS nomComplet
Tous les mots clé SQL doivent etre majuscule.
Vous pouvez utiliser des fonctions php, ou bien les fonctions dédiées nommées csvSql_function dans
le fichier SqlParser.inc. Vous avez aussi la possibilité de brider l'utilisation de fonctions qu'a
ces seules fonctions en passant l'attribut '$functionLocalOnly' a true.
La clause WHERE :
La clause WHERE se compose de noms de colonnes ou d'alias. Tous comme les fonctions, vous
pouvez imbriquer les clauses OR / AND .
SELECT firstName, firstName as prenom WHERE firstName ~ 'F' OR ( firstName ~ 'fred' AND prenom = 'frederic')
Conclusion :
Sans doute certains trouverons la solution trop lourde pour parser un fichier CSV, mais si comme moi, vous manipulez souvent ce type de fichier, alors peut etre est ce une solution.
J'ai voulu par la suite, creer un Linq like, tel C#, j'ai vu que ca existait deja (phpLinq), inutile donc, de réinventer la roue. De plus, phpLinq offre des possibilités de 'sort' qui ne sont pas implémentées dans csvSql qui a vocation traiter de gros fichiers.
Si certains d'entre vous veulent un tuto sur ce bash ( comme le suggère Malalam ), n'hésitez pas a me le demander.
Codes Sources
A voir également
- Bash csvsql: parser un fichier csv avec des commandes sql
- Bash lexer ✓ - Forum C / C++ / C++.NET
- # /Bin/bash ✓ - Forum C
- Openclassroom bash ✓ - Forum C / C++ / C++.NET
- Ncurses bash ✓ - Forum C
- Regex bash - Forum Regex
Vous n'êtes pas encore membre ?
inscrivez-vous, c'est gratuit et ça prend moins d'une minute !
Les membres obtiennent plus de réponses que les utilisateurs anonymes.
Le fait d'être membre vous permet d'avoir un suivi détaillé de vos demandes et codes sources.
Le fait d'être membre vous permet d'avoir des options supplémentaires.