Générer un fichier EXCEL à partir des résultats d'une requête SQL avec SYSTOOLS.GENERATE_SPREADSHEET
Objectifs :
La fonction scalaire SQL SYSTOOLS.GENERATE_SPREADSHEET permet de convertir un fichier DB2 aux formats :
- EXCEL : xlsx
- EXCEL : xls
- CSV (COMMA SEPARATED VALUE) : csv
- TEXTE : txt
- ODS (Feuille de calcul OPENDOCUMENT) : ods
Le fichier converti sera placé dans l’IFS.
La fonction SYSTOOLS.GENERATE_SPREADSHEET peut convertir :
- Un fichier DB2 ou une table SQL déjà existante.
Dans ce cas l’intégralité des enregistrements et l’intégralité des champs de la table seront pris en compte et donc convertis. - Le résultat d’une requête SQL passée en paramètre, ce qui permet de convertir uniquement les enregistrements et les champs sélectionnés par la requête.
La fonction SYSTOOLS.GENERATE_SPREADSHEET retourne :
- 1 lorsque la conversion à été faite.
- -1 lorsque la conversion n’a pas pu être réalisée.
ATTENTION :
- L’appel de SYSTOOLS.GENERATE_SPREADSHEET peut prendre un certain temps.
La génération du fichier converti dans l’IFS n’est pas instantanée. - Les fichiers de QTEMP, *LIBL, *CURLIB ne peuvent pas être directement pris en charge par SYSTOOLS.GENERATE_SPREADSHEET
Syntaxe :
La fonction scalaire SQL SYSTOOLS.GENERATE_SPREADSHEET dispose de plusieurs paramètres à renseigner :
- PATH_NAME : c’est le chemin d’accès au fichier à générer dans l’IFS.
- Les paramètres d’accès aux données à convertir
- Soit SPREADSHEET_QUERY si les données à convertir sont extraites par une requête SQL.
Dans ce cas le paramètre SPEADSHEET_QUERY contiendra la requête SQL permettant d’extraire les données à convertir. - Soit LIBRARY_NAME et FILE_NAME si l’intégralité d’un fichier DB2 doit être converti.
Dans ce cas LIBRARY_NAME contiendra le nom de la bibliothèque contenant le fichier DB2 à convertir et FILE_NAME contiendra le nom du fichier DB2 à convertir.
- Soit SPREADSHEET_QUERY si les données à convertir sont extraites par une requête SQL.
- SPREADSHEET_TYPE : c’est le format de conversion attendu.
Ce paramètre peut prendre les valeurs suivantes :- xlsx si le fichier est à convertir au format EXCEL
- xls si le fichier est à convertir au format EXCEL (ancien format EXCEL)
- csv si le fichier est à convertir au format CSV (COMMA SEPARATED VALUE)
- txt si le fichier est à convertir au format Texte
- ods si le fichier est à convertir au format ODS (Feuille de calcul OPENDOCUMENT)
Ce paramètre est optionnel.
S’il n’est pas spécifié, par défaut, le fichier sera converti au format csv.
- COLUMN_HEADING : paramètre qui gère les entêtes de colonnes.
- COLUMN si les noms de colonnes doivent être utilisés pour les entêtes de colonnes du fichier converti.
- LABEL si les libellés de colonnes doivent être utilisés pour les entêtes de colonnes du fichier converti.
- NONE si le fichier converti ne doit pas avoir d’entêtes de colonnes.
Exemples :
Conversion de l’intégralité d’un fichier DB2 :
SELECT CASE WHEN SYSTOOLS.GENERATE_SPREADSHEET(
PATH_NAME => ‘/HOME/mon_profil/mon_fichier_converti’,
LIBRARY_NAME => ‘bibliothèque_de_mon_fichier_à_convertir’,
FILE_NAME => ‘nom_du_fichier_à_convertir’,
SPREADSHEET_TYPE => ‘txt’,
COLUMN_HEADINGS => ‘COLUMN’
) = 1 THEN ‘CONVERSION TERMINEE’
ELSE ‘CONVERSION NON EFFECTUEE’
END AS RESULTAT_CONVERSION
FROM SYSIBM.SYSDUMMY1;
Le fichier converti :
- sera au format TXT
- aura pour entête de colonnes les noms des colonnes de la table à convertir.
- sera disponible dans l’IFS dans le repertoire /HOME/mon_profil/
- se nommera mon_fichier_converti.txt
Conversion à partir du résultat d’une requête SQL :
La requête SQL peut contenir des clause WHERE, ORDER BY ou autres.
Il faut utiliser des doubles quottes pour insérer des valeurs alphanumériques.
SELECT CASE WHEN SYSTOOLS.GENERATE_SPREADSHEET(
PATH_NAME => ‘/HOME/mon_profil/mon_fichier_converti’,
SPREADSHEET_QUERY => ‘SELECT COLONNE_1, COLONNE_5 FROM bibliothèque_de_mon_fichier_à_convertir.nom_du_fichier_à_convertir WHERE COLONNE_2 = »ma_valeur_de_selection » ,
SPREADSHEET_TYPE => ‘xlsx’,
COLUMN_HEADINGS => ‘NONE’
) = 1 THEN ‘CONVERSION TERMINEE’
ELSE ‘CONVERSION NON EFFECTUEE’
END AS RESULTAT_CONVERSION
FROM SYSIBM.SYSDUMMY1;
Le fichier converti :
- sera au format EXCEL xlsx
- contiendra les valeurs des colonnes COLONNE_1 et COLONNE_5 des enregistrements dont la valeur de la COLONNE_2 est égale à ‘ma_valeur_de_selection’
- n’aura pas d’entête de colonnes.
- sera disponible dans l’IFS dans le repertoire /HOME/mon_profil/
- se nommera mon_fichier_converti.txt
Conclusions :
La fonction SYSTOOLS.GENERATE_SPREADSHEET est très pratique, elle :
- Peut être encapsulée dans du code RPG (SQL EMBEDDED).
- Peut être couplée à la fonction SYSTOOLS.SEND_EMAIL pour envoyer le fichier converti par mail automatiquement.
- Ne fonctionne pas sur des données issues de QTEMP, *LIBL, *CURLIB.
- Met du temps à s’exécuter.
