Question Comment puis-je empêcher Excel de manger mes délicieux fichiers CSV et d'excréter des données inutiles?


J'ai une base de données qui suit les ventes de widgets par numéro de série. Les utilisateurs saisissent les données et la quantité des acheteurs et numérisent chaque widget dans un programme client personnalisé. Ils finalisent ensuite la commande. Tout fonctionne parfaitement.

Certains clients veulent un tableur compatible Excel des widgets qu'ils ont achetés. Nous générons cela avec un script PHP qui interroge la base de données et affiche le résultat sous forme de fichier CSV avec le nom du magasin et les données associées. Cela fonctionne très bien aussi.

Lorsqu'il est ouvert dans un éditeur de texte tel que Bloc-notes ou vi, le fichier ressemble à ceci:

"Account Number","Store Name","S1","S2","S3","Widget Type","Date"
"4173","SpeedyCorp","268435459705526269","","268435459705526269","848 Model Widget","2011-01-17"

Comme vous pouvez le voir, les numéros de série sont présents (dans ce cas deux fois, toutes les séries secondaires ne sont pas identiques) et sont de longues chaînes de chiffres. Lorsque ce fichier est ouvert dans Excel, le résultat devient:

Account Number  Store Name  S1  S2  S3  Widget Type Date 
4173    SpeedyCorp  2.68435E+17     2.68435E+17 848 Model Widget    2011-01-17

Comme vous avez pu le constater, les numéros de série sont encadrés par des guillemets doubles. Excel ne semble pas respecter les qualificateurs de texte dans les fichiers .csv. Lors de l'importation de ces fichiers dans Access, nous n'avons aucune difficulté. En les ouvrant comme texte, pas de problème du tout. Mais Excel, sans faute, convertit ces fichiers en fichiers inutiles. Essayer d'informer les utilisateurs finaux sur l'art d'ouvrir un fichier CSV avec une application autre que celle par défaut devient, dirons-nous, fastidieux. Y a-t-il de l'espoir? Y a-t-il un paramètre que je n'ai pas pu trouver? Cela semble être le cas avec Excel 2003, 2007 et 2010.


121
2018-01-19 01:08


origine


puis-je donner un +1 juste pour le nom? - tombull89
Excel does not seem to respect text qualifiers in .csv files - les guillemets sont ne pas qualificatifs de texte, ils autorisent simplement les virgules dans vos données, si vous n'utilisez pas de virgules dans vos données, elles sont sans signification. Toutes les données dans un fichier CSV ne sont pas typées, donc Excel ne peut que deviner que votre grand numéro de série est un nombre, et que lorsque vous exécutez dans Excel précision maximale de 15 chiffres, ce qui est ce qui tronque vos chiffres. - DMA57361
Excel ne semble pas respecter toutes les virgules entre guillemets. Considérez "12 Août 2012" Excel transforme cela en ordures aussi. - zundarz
Je veux mentionner ceci Question SU. Il explique quelles options vous avez lorsque vous traitez avec CSV dans Excel. - nixda
@nixda Merci! Ce sont des suggestions utiles, en particulier pour les utilisateurs plus expérimentés. Mon problème est presque plus un problème humain, dans la mesure où Excel s’associe aux fichiers .csv et que les gens voient l’icône, double-cliquez (parce que c’est ainsi que vous ouvrez les choses), puis appuyez sur Enregistrer (parce que nous disons toujours eux pour sauver!), et tout est perdu. Mais je vais certainement utiliser vos méthodes si possible. - atroon


Réponses:


Mais Excel, sans faute, convertit ces fichiers en fichiers inutiles.

Excel est une poubelle inutile.

Solution

Je serais un peu surpris si un client souhaitant que vos données soient dans un format Excel était incapable de modifier la mise en forme visible sur ces trois colonnes en "Nombre" avec zéro décimale ou en "texte". Mais supposons qu'un document court est hors de question.

Vos options sont les suivantes:

  1. Lancez un caractère non numérique et non blanc dans vos numéros de série.
  2. Écrivez un fichier xls ou un fichier xlsx avec un formatage par défaut.
  3. Trichez et sortez ces chiffres comme des formules ="268435459705526269","",="268435459705526269" (vous pouvez aussi faire ="268435459705526269",,="268435459705526269" en vous sauvant 2 caractères). Cela a l'avantage de s'afficher correctement et est probablement utile, mais subtilement rompu (car ce sont des formules).

Soyez prudent avec l'option 3, car certains programmes (dont Excel et Open Office Calc) ne traiteront plus les virgules à l'intérieur. ="" champs comme échappé. Cela signifie ="abc,xyz" étendra deux colonnes et interrompra l'importation.

En utilisant le format de "=""abc,xy""" résout ce problème, mais cette méthode vous limite toujours à 255 caractères en raison de la limite de longueur de la formule Excel.


57
2018-01-19 01:57



En fait, ce n'est pas dur. Copiez et collez l'un des numéros ci-dessus dans Excel, puis modifiez le format numérique comme suggéré. Excel modifie la valeur, ce qui entraîne des erreurs. - Joe Internet
@Joe, j'étais trop superficiel sur mon aperçu initial. Excel produit effectivement des déchets et est lui-même une poubelle. J'ai mis à jour ma réponse pour refléter cela. Une option pourrait avoir un "csv Excel" et avoir aussi un "csv utilisable et valable" - Tyler
@Tyler - Je ne pense pas qu'Excel est une poubelle, il suffit de dire que OP avait raison de dire qu'il produisait des déchets dans ce cas. C'est en fait une très bonne question, sans solution apparemment élégante. - Joe Internet
L'option Format Cells ... a été suggérée et j'ai essayé de l'utiliser. Dans ce cas, dès que vous ouvrez le fichier, Excel semble convertir les publications en série en notation scientifique (ce qui n'est pas surprenant) et lance la précision. Lorsque vous les modifiez en nombre ou en texte, la chaîne ne revient pas. Cette est vraiment l'essence du problème. Produire en tant que formules peut le faire, mais je n'y ai pas pensé. - atroon
@ DMA57361 Le comportement n'est pas attendu, il est déterminable. La précision numérique est bien documentée, mais pas Excel. L'absence d'avertissement et l'élimination silencieuse des données sont absurdes. Le fait que vous ne puissiez même pas dire à Excel comment importer les données est tout aussi absurde. Est la négativité nécessaire? Non, mais l'honnêteté est la meilleure politique et c'est ce que je ressens. - Tyler


Nous avons eu un problème similaire où nous avions des fichiers CSV avec des colonnes contenant des plages telles que 3-5 et Excel les convertissait toujours en dates, par ex. 3-5 serait le 3 mars, après quoi le retour à numérique nous a donné un nombre entier de dates inutile. Nous l'avons contourné par

  1. Renommer l'extension CSV en TXT
  2. Ensuite, lorsque nous l'avons ouvert dans Excel, cela déclencherait l'assistant d'importation de texte.
  3. A l'étape 3 de 3 de l'assistant nous lui avons dit que les colonnes en question étaient du texte et ils ont importé correctement.

Vous pourriez faire la même chose ici, je pense.

text import wizard

À votre santé


41
2018-01-31 18:30



+1 pour être la bonne façon de le faire. (edit: désolé a dû éditer un peu pour clarifier la solution) - Jay
Vous n'avez pas à renommer votre fichier. Utilisez simplement l'assistant d'importation Maj-sélectionnez toutes les colonnes et choisissez le texte. - nixda
L'assistant d'importation de texte est LA réponse. Toutes les autres solutions sont inutiles, car elles ne permettent pas de comprendre comment utiliser Excel pour afficher et éditer les fichiers CSV. - Excellll
@Excellll, en faisant un fichier à la fois, bien sûr. Lors de l'automatisation de ce processus, le «piratage inutile» permet souvent de gagner du temps. - Parrish Husband
Ceci est totalement inutile lorsque Excel est utilisé par les utilisateurs standard pour afficher des fichiers CSV. Avant d'essayer d'expliquer l'utilisation de l'assistant de saisie de texte à ~ 15 utilisateurs de bureau débutants, je préfère taper le code source du document Excel par moi-même. - northkildonan


Une meilleure solution consiste à générer un classeur XML. Comme ça:

<?xml version="1.0" encoding="UTF-8"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:c="urn:schemas-microsoft-com:office:component:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x2="http://schemas.microsoft.com/office/excel/2003/xml" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
  </OfficeDocumentSettings>

  <ss:Worksheet ss:Name="Sheet 1">
    <Table>
    <Column ss:Width="100"/>
    <Column ss:Width="100"/>
    <Column ss:Width="150"/>
    <Column ss:Width="150"/>
    <Column ss:Width="150"/>
    <Column ss:Width="150"/>
    <Column ss:Width="80"/>
    <Column/>

    <Row>
      <Cell><Data ss:Type="String">Account Number</Data></Cell>
      <Cell><Data ss:Type="String">Store Name</Data></Cell>
      <Cell><Data ss:Type="String">S1</Data></Cell>
      <Cell><Data ss:Type="String">S2</Data></Cell>
      <Cell><Data ss:Type="String">S3</Data></Cell>
      <Cell><Data ss:Type="String">Widget Type</Data></Cell>
      <Cell><Data ss:Type="String">Date</Data></Cell>
    </Row>

    <Row>
      <Cell><Data ss:Type="String">4173</Data></Cell>
      <Cell><Data ss:Type="String">SpeedyCorp</Data></Cell>
      <Cell><Data ss:Type="String">268435459705526269</Data></Cell>
      <Cell><Data ss:Type="String">x</Data></Cell>
      <Cell><Data ss:Type="String">268435459705526269</Data></Cell>
      <Cell><Data ss:Type="String">848 Model Widget</Data></Cell>
      <Cell><Data ss:Type="String">2011-01-17</Data></Cell>
    </Row>


    </Table>
    <x:WorksheetOptions/>
  </ss:Worksheet>
</Workbook>

Le fichier doit avoir l'extension .xml. Excel et OpenOffice l'ouvrent correctement.


9
2018-02-17 11:13



Voulez-vous dire que l'OP doit utiliser un script PHP pour convertir la base de données au format XML? - Prasanna
Bien plus propre que d’avoir des utilisateurs ouvrant .csv dans Excel ou de déconner votre CSV pour que seul Excel puisse comprendre votre CSV. Ce n'est même pas ce complexe d'un schéma. - binki
Où cette norme est-elle documentée? Je voudrais en savoir plus sur les types de données disponibles. - John Doherty


Ma solution: J'ai le même problème avec l'importation des numéros de série. Il n'est pas nécessaire de les traiter comme des nombres, c’est-à-dire qu’aucune fonction mathématique n’est exécutée, mais nous avons besoin de la totalité du nombre. La chose la plus simple que j'ai est d'insérer un espace dans le numéro de série. par exemple "12345678 90123456 1234". Lorsque Excel l'importe, il sera traité comme du texte au lieu d'un numérique.


1
2018-05-08 08:08





J'ai eu de longs numéros de compte brouillés.

Voici comment je l'ai corrigé:

Ouvrez votre fichier.csv dans Libre Office / Open Office (vous devrez peut-être spécifier des délimiteurs, etc.), puis enregistrez le fichier en tant que fichier XML Excel.

Ensuite, ouvrez ce fichier dans Excel et vous verrez que les colonnes ne sont plus modifiées au format scientifique ou autre. Pour être sûr, cliquez avec le bouton droit de la souris sur la colonne et définissez explicitement le format en tant que texte, puis enregistrez-le au format de fichier Excel.

Ouvrez le fichier au format Excel et la colonne doit toujours être correcte!


0
2018-05-17 14:53



Alors que ce aurait travailler, essayer d'expliquer à quelqu'un qui ne parle que l'anglais brisé, pourquoi il aurait besoin d'utiliser une suite bureautique différente crée autant de problèmes que cela résout. Le logiciel alternatif à M $ Office est tout à fait à mon avis, mais je me rends compte que je ne peux pas convertir tout le monde. - atroon


L'assistant d'importation est la meilleure solution pour les utilisateurs occasionnels et les situations uniques. Si vous avez besoin d'une solution de programmation, vous pouvez utiliser la méthode QueryTables.Add (utilisée par l'Assistant d'importation en arrière-plan).

Workbooks.Add
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & "C:\myfile.csv", Destination:=Range("$A$1"))
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 437
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = False
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 2, 2) 'Edit this line. Add a number for each column, 1 is general, 2 is text. Search the internet for other formats.
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
End With

0
2017-07-23 19:48