Questão Como posso impedir o Excel de comer meus deliciosos arquivos CSV e excretar dados inúteis?


Eu tenho um banco de dados que rastreia as vendas de widgets por número de série. Os usuários inserem dados e quantidade do comprador e examinam cada widget em um programa cliente personalizado. Eles então finalizam o pedido. Tudo isso funciona perfeitamente.

Alguns clientes querem uma planilha compatível com o Excel dos widgets que compraram. Nós geramos isso com um script PHP que consulta o banco de dados e gera o resultado como um CSV com o nome da loja e os dados associados. Isso funciona perfeitamente também.

Quando aberto em um editor de texto, como o Bloco de notas ou vi, o arquivo se parece com isto:

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

Como você pode ver, os números de série estão presentes (neste caso, duas vezes, nem todas as séries secundárias são as mesmas) e são longas seqüências de números. Quando este arquivo é aberto no Excel, o resultado se torna:

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

Como você deve ter observado, os números de série são colocados entre aspas duplas. O Excel não parece respeitar os qualificadores de texto em arquivos .csv. Ao importar esses arquivos para o Access, não temos dificuldade. Ao abri-los como texto, não há problema algum. Mas o Excel, sem falhar, converte esses arquivos em lixo inútil. Tentar instruir os usuários finais sobre a arte de abrir um arquivo CSV com um aplicativo não padrão está se tornando, digamos, cansativo. Existe esperança? Existe uma configuração que não consegui encontrar? Este parece ser o caso com o Excel 2003, 2007 e 2010.


121


origem


posso dar +1 para o nome? - tombull89
Excel does not seem to respect text qualifiers in .csv files - as aspas duplas são não qualificadores de texto, eles simplesmente permitem vírgulas em seus dados, se você não usar vírgulas em seus dados, então eles são sem significado. Todos os dados em um arquivo CSV não são digitados, portanto, o Excel só pode adivinhar que seu grande número de série é um número, e é aí que você corre para o Excel precisão máxima de 15 dígitos, que é o que está truncando seus números. - DMA57361
O Excel não parece respeitar todas as vírgulas entre aspas duplas. Considere "12 de agosto de 2012" O Excel também transforma isso em lixo. - zundarz
Eu quero mencionar isso Pergunta SU. Ele explica quais opções você tem ao lidar com o CSV no Excel. - nixda
@nixda Obrigado! Essas são sugestões úteis, especialmente para os usuários mais experientes. Meu problema é quase um problema humano, em que o Excel se associa a arquivos .csv, e as pessoas vêem o ícone, e clicam duas vezes (porque é assim que você abre coisas), e geralmente clicam em Salvar (porque estamos sempre dizendo para salvar!), e tudo está perdido. Mas eu definitivamente vou usar seus métodos quando possível. - atroon


Respostas:


Mas o Excel, sem falhar, converte esses arquivos em lixo inútil.

Excel é lixo inútil.

Solução

Eu ficaria um pouco surpreso se qualquer cliente que deseja seus dados em um formato do Excel não pôde alterar a formatação visível nessas três colunas para "Número" com zero casas decimais ou para "texto". Mas vamos supor que um breve documento de instruções esteja fora de questão.

Suas opções são:

  1. Jogue um caractere não numérico e não em espaço em branco nos números de série.
  2. Escreva um arquivo xls ou xlsx com alguma formatação padrão.
  3. Fraude e produza esses números como fórmulas ="268435459705526269","",="268435459705526269" (você também pode fazer ="268435459705526269",,="268435459705526269" salvando você mesmo 2 caracteres). Isto tem a vantagem de exibir corretamente, e provavelmente sendo geralmente útil, mas sutilmente quebrado (como são fórmulas).

Tenha cuidado com a opção 3, porque alguns programas (incluindo o Excel e o Open Office Calc) não tratarão mais vírgulas ="" campos como escapou. Que significa ="abc,xyz" irá abranger duas colunas e interromper a importação.

Usando o formato de "=""abc,xy""" resolve esse problema, mas esse método ainda limita você a 255 caracteres devido ao limite de comprimento de fórmula do Excel.


57



Na verdade, isso não está sendo duro. Copie e cole um dos números acima no Excel e altere o formato numérico conforme sugerido. Excel altera o valor, resultando em lixo. - Joe Internet
@Joe, eu estava muito superficial na minha visão inicial inicial. O Excel realmente está produzindo lixo e é lixo. Eu atualizei minha resposta para refletir isso. Uma opção pode estar tendo um "csv do Excel" e também ter um "csv utilizável e útil" - Tyler
@Tyler - Eu não acho que o Excel é lixo, apenas dizendo que OP estava correto que estava produzindo lixo neste caso. Na verdade, é uma pergunta muito boa, sem solução aparentemente elegante. - Joe Internet
A opção Formatar células ... foi sugerida e tentei usá-la. Nesse caso, no momento em que você abre o arquivo, o Excel parece converter os seriais em notação científica (combinada, não inesperada) e lança a precisão. Quando você as altera para um número ou para um texto, a string não volta. que é realmente a essência do problema. Emitir como fórmulas pode fazê-lo embora ... Eu não pensei nisso. - atroon
@ DMA57361 O comportamento não é esperado, é determinável. A precisão numérica é bem documentada, como o Excel lê CSVs não é. A falta de aviso e o descarte silencioso de dados é um absurdo. O fato de você não poder mesmo dizer ao Excel como importar os dados é igualmente absurdo. É a negatividade necessário? Não, mas a honestidade é a melhor política e é assim que me sinto. - Tyler


Tivemos um problema semelhante em que tínhamos arquivos CSV com colunas contendo intervalos como 3-5 e o Excel sempre os convertia em datas, por exemplo, 3-5 seria 3 Mar, após o qual a mudança de volta para numérico nos deu um número inteiro de data inútil. Nós contornamos isso por

  1. Renomeando a extensão CSV para TXT
  2. Então, quando abrimos no Excel, isso acionaria o assistente de importação de texto
  3. Na etapa 3 de 3 no assistente dissemos que as colunas em questão eram texto e eles importaram corretamente.

Você poderia fazer o mesmo aqui eu pensaria.

text import wizard

Felicidades


41



1 por ser a maneira correta de fazer isso. (editar: desculpe tive que editar um pouco para esclarecer a solução) - Jay
Você não precisa renomear seu arquivo. Basta usar o assistente de importação Shift - selecione todas as colunas e escolha como texto. - nixda
Assistente de importação de texto é a resposta. Todas as outras soluções são desnecessárias hackery resultantes de não entender como usar o Excel para visualizar e editar CSVs. - Excellll
@Excellll, fazendo um arquivo de cada vez, com certeza. Ao automatizar este processo, a "hackie desnecessária" geralmente salva o dia. - Parrish Husband
isso é completamente inútil quando o Excel é usado por usuários padrão para exibir arquivos CSV. Antes de tentar explicar como usar o assistente de importação de texto para ~ 15 usuários de escritório com habilidades para iniciantes, eu prefiro digitar o código fonte do documento excel por mim mesmo. - northkildonan


Melhor solução é gerar o XML Workbook. Como isso:

<?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>

O arquivo deve ter extensão .xml. Excel e OpenOffice abrem corretamente.


9



Você quer dizer que o OP deve usar script PHP para converter o banco de dados em formato XML? - Prasanna
Muito mais limpo do que ter usuários que abrem o .csv no Excel ou bagunçam o seu CSV para que somente o Excel possa entender seu CSV. Não é mesmo esse complexo de um esquema. - binki
Onde esse padrão é documentado? Eu gostaria de saber mais sobre os tipos de dados disponíveis. - John Doherty


Minha solução: Eu tenho o mesmo problema com a importação de números de série. Eles não precisam ser tratados como números, isto é, nenhuma função matemática é executada, mas precisamos do número inteiro lá. A coisa mais simples que tenho é inserir um espaço no número de série. por exemplo, "12345678 90123456 1234". Quando o Excel o importa, ele será tratado como texto em vez de numérico.


1





Eu tinha números de conta longos sendo ilegíveis.

Foi assim que eu consertei:

Abra seu arquivo .csv no Libre Office / Open Office (talvez seja necessário especificar delimitadores, etc.) e salve o arquivo como um arquivo XML do Excel.

Em seguida, abra este arquivo no Excel e, em seguida, você verá que as colunas não são mais alteradas para o formato científico ou qualquer outra coisa. Para ficar seguro, clique com o botão direito do mouse na coluna e defina explicitamente o formato como Texto e salve como formato de arquivo do Excel.

Abra o arquivo de formato do Excel e a coluna ainda deve estar ok!


0



Enquanto isso seria trabalho, tentando explicar para alguém que fala apenas Inglês quebrado porque ele precisaria usar um conjunto de escritório diferente cria tantos problemas quanto ele resolve. O software alternativo para o M $ Office é bom na minha opinião, mas percebo que não posso converter todos. - atroon


O Assistente de Importação é a melhor solução para usuários casuais e situações únicas. Se você precisar de uma solução programática, poderá usar o método QueryTables.Add (que é o que o Assistente de Importação está usando nos bastidores).

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