Blog de développement

Ici, j'explique comment intégrer mes référentiels (ex: SIRENE, Hexamal, BANO, France-Voie...) quand ils sont mis sous format .csv dans ma base de données MS-SQL.

BCP et SQLCMD : Outils Essentiels pour SQL Server

L'outil DTS de MS SQL est difficile à maîtriser et à maintenir. Pour importer de grosses bases CSV (ex: Chorus, BANO, SIRENE...), MS SQL Management Studio propose un outil d'importation de fichiers plats (Flat Files). Il semble utiliser la commande SQL BULKINSERT.
De façon similaire, BCP (Bulk Copy Program): outil en ligne de commande pour l'importation et l'exportation rapide de données, particulièrement utile pour transférer de grandes quantités de données entre SQL Server et les fichiers de données CSV.
A noter que QSV fournit des outils, mais limité aux bases Open Sources (SQLLite, etc)
Il permet d'exporter ou d'importer SQL Server <--> CSV ou TXT, avec des options de personnalisation pour les délimiteurs et les types de données.
Utilisation typique : Chargement rapide de données depuis ou vers un data warehouse, Archivage, migration...

SQLCMD (SQL Server Command Line Tool) : un outil puissant pour exécuter des scripts SQL, gérer des bases de données, effectuer des tâches administratives, depuis la ligne de commande, y compris à distance.
Utilisation typique : Exécution de scripts de maintenance et de mise à jour réguliers, interrogation rapide de bases de données pour des rapports ou des vérifications ad hoc.

En pratique, les deux vont de pair.

Installation et Prise en main

On peut installer les outils de ligne de commande de SQL Server, y compris BCP, sur un client sans avoir à installer l'intégralité de SQL Server. Microsoft fournit un package appelé "Microsoft Command Line Utilities for SQL Server" qui comprend BCP et sqlcmd. Lire et Télécharger →

L'utilisation d'une ligne de commande m'a toujours semblé un peu magique... R. Sheldon vous fait la lumière sur les détails pour le maîtriser : Lire en anglais → Lire en français →

Importation de données en français

L'importation de chaines de caractères simplifiées, comme celles au format de La Poste, qui sont limitées à du bon vieil ASCII, est simpliscime. C'est un peu plus compliqué quand on veut importer des data 'à la française'. Aujourd'hui le standard de référence est UTF-8, mais on peut importer directement depuis un fichier encodé en Windows-1252

Pour ce faire respecter les points suivant:
- convertir vers Windows-1252 avec Iconv avec -f UTF-8 -t Windows-1252
- changer la fin de ligne avec Unix2Dox.exe
- ne mettre aucun argument de caractère tel que -c ou -w ou -C (Mais on peut mettre -C ACP)
- préciser éventuellement -F 2 dans la commande BCP pour ne pas importer les entêtes

Voici une liste des valeurs possibles pour -C avec leur description :
ACP : Code Page Windows ANSI (1252 pour la plupart des systèmes Windows).
OEM : Code Page Windows OEM (850 pour la plupart des systèmes Windows en Amérique du Nord).
RAW : Aucun encodage, chaque byte est considéré comme un caractère.
UTF-7 : Encodage UTF-7.
UTF-8 : Encodage UTF-8.
UNICODE : Encodage UTF-16 Little Endian.
BigEndianUnicode : Encodage UTF-16 Big Endian.

Importation de données depuis Excel (XLSX)

Dans le cas où ldes données ne sont pas sous CSV (Ex: Chorus qui est sous XLSX, avec 2 feuilles), il faut d'abord extraire ce qui peut amener à plusieurs problèmes:

• Sur le serveur, on ne dispose pas d'Excel et on évite de charger LibreOffice
• La plupart des outilss ne traitent que la première feuille: les extracteurs multi-feuilles sont rares!
• L'encodage peut être corrompu et doit être vérifié

Un fichier XLSX est en fait un ZIP d'un dossier contenant les feuilles au format XML. Il est facile de deziper, puis, on peut envisager d'importer chaque fichier XML selon la procédure décrite par Microsoft Lire → C'est très spécifique au problème posé par des sources de données au format XLSX vers MS SQL. L'alternative est de passer de XLSX à CSV et utiliser les mêmes outils que pour SIRENE, BANO, etc.

• Vérifier que le fichier (.CSV) est bien en UTF-8)
• Déterminer le mode de fin de ligne (ex. LF ou CRLF)
• Créer une table dans SQL qui corresponde à votre besoin
• La fin de ligne (LF ou Cr+Lf) est plus ou moins bien conservée selon les solutions
• Créer puis modifier le fichier Schema
• Créer une commande BCP et l'exécuter

Vérifier qu'on est bien en UTF-8

NotePad++ est gratuit est probablement le plus efficace à bien déterminer l'encodage ainsi que la terminaison de ligne. Dans un script, je procède ainsi : je demande à iconv de convertir de UTF-8 à UTF-8, sans le flag /IGNORE ni /TRANSLIT. A la moindre erreur, je la capte dans un fichier, que je vérifie éventuellement.

command = """" & sIconvPath & """ -f UTF-8 -t UTF-8 """  & sFilePath & """ 1> NUL 2> """ & sErrorFilePath_UTF_8 & """ "

Ici, 1> n'est pas capturé donc il n'est pas nécessaire de gérer le codepage

Déterminer/Corriger la fin de ligne (LF ou CRLF)

Windows travaille par défaut avec une terminaison de ligne, dite DOS : CR+LF. Les gros fichiers que je télécharge le plus: stock SIRENE, BANO... ont été générés dans un environnement Linux et sont en terminaison de line LF

A l'inverse le fichier XLSX de Chorus est généré dans un environnement Windows, il est UTF-8 MAIS avec CR+LF en fin de ligne.

Dos2Unix / Unix2Dos est un petit utilitaire Lire → bien pratique. Il propose aussi des conversions.. Télécharger →.
Les commandes sont expliquées par l'auteur Lire → dans ce manuel : Lire →
Ici, le flag -i ne nous donne que les informations. Il est paramétrable et les résultats s'interprètent avec le manuel ...

 "C:\...\dos2unix\unix2dos.exe" -i "E:\...\Data\Chorus_Services_Nom_Sans_Headers.csv"

A noter cette alternative avec Powershell, que j'ai écarté, peut-être un peu trop vite, après quelques essais infructueux Lire →

Il n'est pas indispensable de corriger systématiquement la fin de ligne. BCP peut parfaitement gérer LF, au prix d'une définition moins triviale dans BCP... Pour corriger :

"C:\...\unix2dos.exe" -f -n "E:\...\Input.csv" "E:\output.csv" 

On notera la présence de flag -n (new, crée un nouveau fichier) et aussi -f (force) que j'ai dû ajouter parfois, suite à la présence d'un octet signalant que c'est binaire (??)

Extraire les 2 feuilles du fichier XLSX de Chorus

Il est nécessaire d'extraire les 2 feuilles sous forme de fichiers CSV pour manipuler et importer. xlsxio_xlsx2csv, quand il fonctionnait encore sur ce fichier (jusque de vers fin 2022), conserve cette fin de ligne CR+LF.
Avec QSV ou CSVKT, qui sont plus puissants et plus riches, on peut aussi extraire les 2 feuilles (fonctionalité rare !) mais on perd CR+LF

Cependant, si on ne dispose que d'outil limité à l'extraction de la première feuille (ex: extracteurs en ligne), on doit pouvoir extraire la première feuille, dezipper le XLSX, échanger feuille 1 et 2, rezipper et recommencer comme décrit ici. Lire → Je n'ai pas essayé...

Pour les très gros fichiers, travailler en Stream ne nécessite pas beaucoup de mémoire. Pour l'extraction de XLSX, QSV charge en mémoire et peut rater l'opération si la mémoire est insuffisante : il génère alors un résultat vide, sans lever d'erreur. Je l'utilise volontiers QSV, toutefois, je vérifie le résultat et s'il est vide, je refais, mais avec CSVK cette fois.

Alternativement, on peut utiliser ce script python (avec ou sans wrapper): xlsx2csv.py Lire →

Avec BCP, Préparer le fichier de Schéma

Avec, BCP, pour des importations triviales, on peut utiliser le flag -c pour le forcer à prendre les données en mode texte, sinon, il interprètera les chiffres, par exemple. Quand on importe les SIRET (14 chiffres) il risque de les convertir en Float, ce qui n'est pas le but. Alternativement, on peut utiliser un schéma, avec le flag -F (il faut alors omettre le -c qui entre en conflit). Le Schéma et sa manipulation éventuelle permet entre autres :

• Bien définir le format des colonnes
• Travailler avec un CSV qui a plus de colonnes que la table cible
• Importer dans une table cible qui a plus de colonnes que le CSV
• Forcer BCP à importer en UTF-8
• Forcer BCP à accepter la fin de ligne (LF au lieu de Cr+Lf)
• Créer une commande BCP et l'exécuter

L'idée est donc de générer le fichier schéma, puis de le manipuler. BCP fait cela très bien :

bcp MaTable format nul -c -t, -f "E:\...\BCP_Schema.txt" -S MonServeur -d MaBase -U sa -P MonMotdePasse

Ici, BCP lit le schéma de votre table. Idéalement, votre CSV doit être structuré à l'identique. Voir aide MS Lire → Toutefois, il est possible de modifier ce schéma et demander à BCP d'utiliser le schéma modifié. Cela forcera BCP à faire selon vos instructions personnalisées : accepter UTF-8, accepter LF, ne pas importer certaines colonnes...

UTF-8 : BCP va accepter une commande spécifiant d'importer du UTF-8, mais le schéma de votre table peut s'y opposer, il faut donc adapter votre schéma pour qu'il l'accepte lui aussi. Cela consiste à retirer le descriptif linguistique. Dans le cas du français, c'est typiquement French_CI_AS. Remplacer par "" (soit 2 fois le guillement double). L'astuce vous est donnée dans un commentaire sur StackOverflow. Lire → Voir aussi l'aide de MicroSoft Lire →, et Lire →

' // dans le fichier Schéma, le laisser libre d'insérer selon ce qui sera dans la commenade BCP, typiquement UTF-8
sContent = replace (sContent,"French_CI_AS","""""") 
' // s'assurer que la table contient la/les colonnes au format N de NVARCHAR
sSQL = "IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = '" & sTableName & "' AND type = 'U')" & vbCrLf
sSQL = sSQL & "CREATE TABLE " & sTableName & " ("[Identifiant] [nvarchar](50) NOT NULL DEFAULT '',")"
' // executer avec une connection ADODB, il conviendra de mettre une gestion d'erreurs...
conn.Execute sSQL

Dans la commande BCP, il faut s'assurer de mettre le flag -f, ne pas mettre le flag -c et ajouter -C 65001 ou -C UTF-8 (c'est pareil)
S'assurer que la table est définie en NCHAR ou NVARCHAR (au lieu de CHAR...)

Lui faire accepter LF : BCP peut accepter LF au lieu de CR+LF, la valeur par défaut de Windows : Lire →
L'aide de Microsoft explique le fichier Schéma et comment le modifier : Lire →
Il faut penser à modifier la commande BCP ET le schéma !

' // Ajouter le flag -r avec 0x0a (équivalent de \n), c'est à dire LF 
Command = Command & "bcp " & sTableName & " in """ & sFilePath & """ -f """ & sBCPSchemaFilePath & """ -e """ & sFilePath_Error_Data & """ " & sConnection_BCP & " -C UTF-8 -r 0x0a "
'// Dans le fichier Schéma s'assurer qu'il est défini pour LF et non CR+LF"
sContent = replace ( sContent,"\r\n","\n")

Changer/omettre des colonnes : C'est un autre sujet. L'aide Microsoft est ici: Lire → et aussi pour éviter d'importer des colonnes : Lire →

Avec BCP, entrer Chorus avec les accents

Pour bien faire les codes de services dans Chorus devraient être simple et court. La vaste majorité l'est... En OCR, l'existence de codes longs ou avec de accents est problématique. Avec MS SQL on cherche à posteriori les entrées problématiques Lire →. En Français cela ressort souvent avec à (A majuscule tilde), que ce soit é, è ou à, voire ï. Les déliquants sont identifiés par leur SIRET : 17350430900019 , 24590041000235 ,23130002100012, 23130002100012, 23130002100012,23130002100012, 23130002100012, 23130002100012, 13001453300017, 24590041000292. On construit une liste de délinquants, liste non exhaustive, mais facile à construire et representative, dans SQL (ou ADO) avec:

SELECT * FROM XXX_Chorus_Services where code like '%Ã%'

SELECT * FROM XXX_Chorus_Services where WHERE PATINDEX('%[Ã]%', code) > 0;

On utilise cette liste des affichages problématiques pour construire un CSV, consitué des services correspondant à ces SIRET. QSV dispose d'une requête SQL assez riche au point de pouvoir se substituer à un serveur de base de données ! On construit une requête avec des expressions régulières pour sélectionner ce que l'on veut tout en élaguant un peu...

c:\...\qsv.exe"  sqlp -Q "E:\...\xxx.csv" "select * from Chorus_Services_Identifiant_Code 
where regexp_like(Identifiant, '24590041000292|17350430900019|24590041000235|23130002100012|13001453300017') 
AND  NOT regexp_like(Code, '_')  AND  NOT regexp_like(Code, 'A')  AND  NOT regexp_like(Code, 'E')  AND  NOT regexp_like(Code, '0') "  
-o "E:\...\xxx_exemple.csv"  

Ce petit CSV sert à tester les stratégies d'entrée dans SQL. Il est dans le ZIP de ressources.

BCP et l'analyse des erreurs

Exemple d'erreur: #@ ligne 1, Colonne 2: Troncation à droite de la chaîne de données @# . Dans la cas présent, la fin de ligne n'était pas correctement configurés dans le fichier source. Ici, une fin en LF au lieu de CR+LF attendu. Un passage par Unix2dos a résolu le problème...

Modifier le Schéma de BCP ou modifier le CSV

Pour un CSV encodé en UTF-8, à entrer dans SQL on peut soit adapter la table SQL (en utilisant NVARCHAR au lieu de VARCHAR) et modifier le schéma de BCP pour lui faire accepter UTF-8 et la fin de ligne de type LF, soit convertir le CSV à Windows-1252 tout en conservant VARCHAR et le schéma. Les 2 marchent... Je privilégie de convertir à Windows-1252.

A noter : QSV modifie la fin de ligne: il faut alors utiliser Unix2dos.
La conversion par ICON peut apparaître satisfaisante à la console, ne pa générer d'erreur, et, cependant donner un résultat inattendu lorsque l'on capte et écrit avec 1> dans la ligne de commande. CMD écrit le fichier de sortie dans son Code Page par défaut (par exemple UTF-8) ce qui corrompt le résultat. Pour écrire le fichier de sortie dans un fichier nommé myfile_windows-1252.txt avec l'encodage Windows-1252, utilisez chcp 1252 > nul :

chcp 1252 > nul
iconv.exe -f UTF-8 -t Windows-1252 myfile.txt 1> myfile_windows-1252.txt

La commande chcp 1252 définit la page de codes de la console sur Windows-1252. L'opérateur > nul redirige la sortie de la commande chcp vers l'appareil null, de sorte qu'elle ne soit pas affichée dans la console.
L'opérateur 1> redirige la sortie standard vers le fichier myfile_windows-1252.txt, écrit en Windows-1252.
Notez que la commande chcp affecte la page de codes de la console CMD pour la session CMD actuelle uniquement. Pas sûr d'ailleurs que chcp 1252 > nul soit indispensable. A tester...

Vérification avant importation

' Séparateur utilisé dans le fichier
sSeparator = ","
Set objFile = objFSO.OpenTextFile(sFilePath, 1,False)
	' // Lire la ligne d'en-tête et déterminer le nombre de séparateurs
If Not objFile.AtEndOfStream Then
    sHeader = objFile.ReadLine
    nHeaderSeparatorCount = Len(sHeader) - Len(Replace(sHeader, sSeparator, ""))
End If
	' // Vérifier chaque ligne subséquente
bIsValid = True
nLineNumber = 1
nLineNumber_FirstError = 0
Do Until objFile.AtEndOfStream
    sLine = objFile.ReadLine
    nLineNumber = nLineNumber + 1
    nLineSeparatorCount = Len(sLine) - Len(Replace(sLine, sSeparator, ""))    
	' // Vérifier si le nombre de séparateurs correspond
    If nLineSeparatorCount <> nHeaderSeparatorCount AND nLineNumber_FirstError = 0 Then nLineNumber_FirstError = nLineNumber 
Loop

Vérification après importation

On vérifie que les fichiers d'erreurs sont vides et que le fichier de sortie comprend bien des mots magiques. On peut aussi en extraire le nombre de lignes importées et comparer au nombre d lignes attendues...

BCP et le rapprochement bancaire (SHINE Banque)

La gestion du rapprochement bancaire est détaillée dans ce billet Lire →.
Résumé des caractéristiques: a une BOM: non (utilisation de hexed.it →) ; Encodage : UTF-8 (utilisation de freeformatter.com →) ; headers : Oui; separateur ; fin de ligne : CR+LF (utilisation de Notepad++)