Importation d'un fichier CSV avec des sauts de ligne dans Excel 2007

Question

Je travaille sur une fonctionnalité permettant d'exporter les résultats de recherche vers un fichier CSV à ouvrir dans Excel. L'un des champs est un champ de texte libre, qui peut contenir des sauts de ligne, des virgules, des citations, etc. Pour éviter cela, j'ai placé le champ entre guillemets (").

Toutefois, lorsque j'importe les données dans Excel 2007, que je définis le délimiteur approprié et que je fixe le qualificateur de texte à un guillemet double, les sauts de ligne créent toujours de nouveaux enregistrements à ces endroits, alors que je m'attendrais à voir l'intégralité du champ de texte dans une seule cellule.

J'ai également essayé de remplacer CR/LF ( \r\n ) avec seulement CR ( \r ), et encore une fois avec seulement LF ( \n ), mais sans succès.

Quelqu'un d'autre a-t-il rencontré ce comportement et, si oui, comment l'a-t-il résolu ?

TIA,
-J

EDIT :
Voici un fichier rapide que j'ai écrit à la main pour reproduire le problème.

ID, nom, description
"12345", "Smith, Joe", "Hey.
Je m'appelle Joe."

Lorsque j'importe ce fichier dans Excel 2007, je me retrouve avec une ligne d'en-tête et deux enregistrements. Notez que la virgule dans "Smith, Joe" est traitée correctement. Ce sont les sauts de ligne qui posent problème.

Answer 1

Réponse courte

Supprimer les caractères de saut de ligne ( \n avec Notepad++). Excel reconnaîtra toujours le caractère retour chariot ( \r ) dans des enregistrements distincts.

Réponse longue

Comme nous l'avons mentionné, les caractères de retour à la ligne sont pris en charge dans les champs CSV, mais Excel ne les gère pas toujours de manière élégante. J'ai été confronté à un problème similaire avec un CSV tiers qui présentait peut-être des problèmes d'encodage, mais qui ne s'est pas amélioré avec les changements d'encodage.

Ce qui a fonctionné pour moi, c'est la suppression de tous les caractères de retour à la ligne ( \n ). Cela a pour effet de réduire les champs à un seul enregistrement, en supposant que vos enregistrements soient séparés par la combinaison d'un retour chariot et d'une nouvelle ligne (CR/LF). Excel importera alors correctement le fichier et reconnaîtra les nouveaux enregistrements par le retour chariot.

Il est évident qu'une solution plus propre consiste à remplacer d'abord les vraies lignes de séparation ( \r\n ) avec une combinaison de caractères temporaire, en remplaçant les nouvelles lignes ( \n ) par le caractère de séparation de votre choix (par exemple, une virgule dans un fichier avec point-virgule), puis en remplaçant les caractères temporaires par de véritables nouvelles lignes.

Answer 2

Si le champ contient un espace en tête, Excel ignore le guillemet double en tant que qualificatif de texte. La solution consiste à éliminer les espaces entre la virgule (séparateur de champ) et le guillemet double. En voici un exemple :

Cassé :
Nom, titre, description
"John", "Mr.", "Ma description détaillée"

Travailler :
Nom, titre, description
"John", "Monsieur", "Ma description détaillée"

Answer 3

+1 sur le commentaire de J Ashley. J'ai également rencontré ce problème. Il s'avère qu'Excel nécessite :

• Un caractère de retour à la ligne (" \n ") dans la chaîne de caractères citée

• Un retour chariot et une nouvelle ligne entre chaque ligne.

Par exemple

"Test", "Multiline item\n
multiline item"\r\n
"Test2", "Multiline item\n
multiline item"\r\n

J'ai utilisé notepad ++ pour délimiter correctement chaque ligne et pour n'utiliser que des nouvelles lignes dans la chaîne. J'ai découvert cela en créant des entrées multilignes dans un document Excel vierge et en ouvrant le csv dans notepad ++.

Answer 4

Si quelqu'un tombe sur ce fil et cherche une réponse définitive, voici ce qu'il en est (merci à la personne qui a mentionné LibreOffice) :

1) Installer LibreOffice 2) Ouvrir Calc et importer le fichier 3) Mon fichier txt avait les champs séparés par , et les champs de caractères entourés de " 4) enregistrer comme fichier ODS 5) Ouvrir le fichier ODS dans Excel 6) Enregistrer sous .xls(x) 7) Terminé. 8) Cela a parfaitement fonctionné pour moi et m'a fait économiser BEAUCOUP d'argent !

Answer 5

Vue d'ensemble

Près de 10 ans après le post original, Excel n'a pas amélioré l'importation des fichiers CSV. Cependant, j'ai découvert qu'il est bien meilleur pour importer des tableaux HTML. On peut donc utiliser Python pour convertir un fichier CSV en HTML, puis importer le HTML résultant dans Excel.

Les avantages de cette approche sont les suivants (a) elle fonctionne de manière fiable, (b) vous n'avez pas besoin d'envoyer vos données à un service tiers (par exemple Google sheets), (c) aucune installation supplémentaire "lourde" n'est nécessaire (LibreOffice, Numbers etc.) pour la plupart des utilisateurs, (d) le niveau est plus élevé que la manipulation des caractères CR/LF et des marqueurs de nomenclature, (e) il n'est pas nécessaire de manipuler les paramètres régionaux.

Les étapes

Les étapes suivantes peuvent être exécutées dans n'importe quel shell de type bash, à condition que Python 3 soit installé. Bien que Python puisse être utilisé pour lire directement des fichiers CSV, csvkit est utilisé pour effectuer une conversion intermédiaire en JSON. Cela nous permet d'éviter d'avoir à gérer les subtilités du CSV dans notre code Python.

Tout d'abord, enregistrez le script suivant en tant que json2html.py . Le script lit un fichier JSON à partir de stdin et le restitue sous forme de tableau HTML :

#!/usr/bin/env python3
import sys, json, html

if __name__ == '__main__':
    header_emitted = False
    make_th = lambda s: "<th>%s</th>" % (html.escape(s if s else ""))
    make_td = lambda s: "<td>%s</td>" % (html.escape(s if s else ""))
    make_tr = lambda l, make_cell: "<tr>%s</tr>" % ( "".join([make_cell(v) for v in l]) )
    print("<html><body>\n<table>")
    for line in json.load(sys.stdin):
        lk, lv = zip(*line.items())
        if not header_emitted:
            print(make_tr(lk, make_th))
            header_emitted = True
        print(make_tr(lv, make_td))
    print("</table\n</body></html>")

Ensuite, installez csvkit dans un environnement virtuel et utilisez csvjson pour envoyer le fichier d'entrée à notre script. C'est une bonne idée de désactiver la devinette du type de cellule avec l'option -I argument :

$ virtualenv -p python3 pyenv
$ . ./pyenv/bin/activate
$ pip install csvkit
$ csvjson -I input.csv | python3 json2html.py > output.html

Maintenant output.html peuvent être importées dans Excel. Les sauts de ligne dans les cellules auront été conservés.

En option, vous pouvez nettoyer votre environnement virtuel Python :

$ deactivate
$ rm -rf pyenv