jeudi 22 septembre 2011

Excel et son incapacité à exporter des données

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:



9 commentaires:

Anonyme a dit…

Bonjour,

merci pour ce code c'est très utile

cependant j'ai toujours des CR et LF quand j'ouvre le fichier obtenu...

comment faire pour avoir des tabulations comme séparateurs à la place des virgules ?

vFelix a dit…

Pour le délimiteur de colonne, j'ai mis un ;. Il suffit de le remplacer par le caractère de votre choix à la fin de la ligne
ligne = ligne & Replace(Replace(Replace(.Cells(l, c), "\r", " "), "\n", " "),";", ".") & ";"

La fonction CHR(9) fournira un caractère tabulation. 9 est une valeur décimale et hexadécimale.

Unknown a dit…

il est possible d'enregistrer un fichier CSV excel et de l'encoder en UTF-8. politique MS ou non...

Enregistrer sous-> (dans le menu) Outils -> Option web -> Onglet Codage -> Type de fichier Unicode UTF-8 -> valider -> selectionner le format CSV et enregistrer

Cordialement.

Unknown a dit…

http://www.surveygizmo.com/survey-software-support/tutorials/getting-started/how-to-encode-an-excel-file-to-utf-8-or-utf-16/

Stef a dit…

Je tiens aussi à vous remercier pour ce code.

Il m'a été d'une très grande utilité!

Unknown a dit…

Merci pour ce code! Si vous souhaitez importer un CSV avec du texte contenant des virgules, point virgule etc il faut ajouter des guillemets pour chaque texte. J'ai donc modifié la ligne comme suit:
ligne = ligne & Replace(Replace(Replace("""" & .Cells(l, c) & """", "\r", " "), "\n", " "), ";", ".") & ","

Philippe a dit…

Merci pour le code ... mais il génère des caractères "chinois" avec Excel 2016 en tout cas. Une solution ?

Chien4hk a dit…
Ce commentaire a été supprimé par l'auteur.
Unknown a dit…

Hey, I'm from China. Don't know your language. Just want to thanks for your code, it works! Really solved my problem.