Comment transformer une table Excel en fichier CSV UTF-8
Ce genre de question peut rapidement devenir énervante. Excel fournie des moyens incroyables pour réaliser 80% des besoins de ses utilisateurs. On peut ainsi gagner un temps précieux. Malheureusement, on arrive très rapidement sur des cas d'usage en dehors de ces 80%, comme par exemple l'exportation d'une table Excel vers un simple fichier txt de type CSV. C'est quant on arrive sur ce genre d'obstacle que l'on paye une grande partie du temps économisé.
Source VBA
Voici donc un exemple de code VBA permettant d'exporter une feuille Excel (onglet "message", zone "a:f") en fichier CSV de type UTF-8 ("e:\tmp\excel\messages-utf8.csv"):
Sub exporter_messages()
'
' exporter_conversations Macro
' arguments:
' fichier_CSV
' Onglet source du tableau à exporter
' zone source à exporter dans l'onglet précédent
'
Dim fichier_out As String
fichier_out = "e:\tmp\excel\messages-utf8.csv"
fichier_out = Zone2CSV_UTF8(fichier_out, "messages", "a:f")
End Sub
'==================================================
Public Function Zone2CSV_UTF8(fichier As String, Onglet As String, Zone As String)
Dim nb_l, nb_c, c, l As Integer
Dim ligne As String
Dim objStream As ADODB.Stream
' init stream
Set objStream = New ADODB.Stream
objStream.Open
objStream.Charset = "utf-8"
objStream.Type = adTypeText
objStream.LineSeparator = adCRLF
objStream.Position = 0
nb_c = Worksheets(Onglet).UsedRange.Columns.Count
nb_l = Worksheets(Onglet).UsedRange.Rows.Count
With Worksheets(Onglet).Range(Zone)
For l = 2 To nb_l ' première ligne de titre à éviter
ligne = ""
For c = 1 To nb_c
ligne = ligne & Replace(Replace(Replace(.Cells(l, c), "\r", " "), "\n", " "),";", ".") & ";"
Next c
objStream.WriteText ligne, adWriteLine
Next l
End With
objStream.SaveToFile fichier, adSaveCreateOverWrite
' close up and return
objStream.Close
Set objStream = Nothing
Zone2CSV_UTF8 = RemoveBOM(fichier)
End Function
'====================================
Public Function RemoveBOM(filePath)
' Create a reader and a writer
Dim writer, reader, fileSize
Set writer = CreateObject("Adodb.Stream")
Set reader = CreateObject("Adodb.Stream")
' Load from the text file we just wrote
reader.Open
reader.LoadFromFile filePath
' Copy all data from reader to writer, except the BOM
writer.Mode = 3
writer.Type = 1
writer.Open
reader.Position = 5
reader.CopyTo writer, -1
' Overwrite file
writer.SaveToFile filePath, 2
' Return file name
RemoveBOM = filePath
' Kill objects
Set writer = Nothing
Set reader = Nothing
End Function
La procédure supprime également les caractères parasites ou dangereux dans les champs de la table: les caractères CR, LF, ;. Cela permet de sécuriser l'export des données en filtrant les caractères qui empêchent une importation sans faute vers une BdD. Ne pas oublier de cocher dans la liste des "références" de VB, l'ActiveX 2.5 (voir la copie d'écran ci-dessus).
Solution alternative sans VBA
La solution la plus simple sans VBA est de passer par le menu "enregistrer sous..." d'Excel, en choisissant le format text unicode comme format de sauvegarde. On utilise ensuite Notepad pour traduire le fichier ainsi créé de la même manière mais cette fois en UTF-8. Malheureusement cette procédure peut contenir des surprises: on peut se tromper (l'erreur est humaine), impossible de le faire en macro facilement (la fonction "enregistrer sous" ferme le classeur en cours), on ne peut pas maitriser le format CSV (CR, LF ou CRLF; délimiteur particulier, etc...). Trop d'imprévus, surtout si on doit répéter l'opération pour plusieurs tables et régulièrement: cela ne fonctionne qu'une table à la fois.
Pourquoi Excel ne fait pas d'exportation de table vers un autre format?
Je n'ai pas de réponse officielle à cette question, mais j'ai bien une idée: AMHA, MS refuse qu'Excel devienne un logiciel pivot pour la gestion des données d'une base de données. Je désigne par logiciel pivot, le fait de pouvoir utiliser Excel comme un moyen de modifier certaines données de référence d'une BdD. Excel est capable d'importer des données d'une BdD, mais interdit l'opération inverse, ce qui empêche l'utilisateur de modifier les données dans Excel pour le remettre dans la BdD. Excel est ainsi contraint de rester un simple logiciel de consultation. Le but inavoué serait de réserver la gestion des données d'une BdB aux autres logiciels de MS, Access ou SQL server. C'est pour cela que selon moi on bride Excel. Le marketting des produits d'un grand groupe tel MS accouche toujours de ce genre de bridage, même si cela va à l'inverse de la simplicité et de l'efficacité. Ainsi va le monde
Lien qui ont servie à la rédaction:
Cette affiche de film m'a interloqué. Voir Tom Hanks et Julia Roberts riants aux éclats sur fond bleu et cheveux au vent, avec ce titre "Il n'est jamais trop tard", m'a paru incongru. Je sais que le film part d'un bon sentiment, et qu'il surfe sur le thème de la recherche du bonheur (rappellez-vous le film insipide de la même actrice : "Mange, prie, aime"). Mais là, le summum du cynisme est atteint par le marketing du film. Cette affiche semble dire, si vous n'êtes pas heureux, c'est bien de votre faute, bande de clown triste. Le bonheur est un droit au US (inscrit dans la constitution), mais semble devenir depuis peu une obligation, une contrainte sociale passablement naïve.