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

Question

Eu tenho um banco de dados que controla 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ê pode 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.

    

Answer 1

But Excel, without fail, converts these files into useless garbage.

O Excel é um lixo inútil.

Solução

Eu ficaria um pouco surpreso se qualquer cliente que queira seus dados em um formato do Excel não puder alterar a formatação visível nessas três colunas para "Número" com casas decimais zero 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. Cheat e produza esses números como fórmulas ="268435459705526269","",="268435459705526269" (você também pode fazer ="268435459705526269",,="268435459705526269" salvar 2 caracteres). Isto tem a vantagem de exibir corretamente e, provavelmente, ser geralmente útil, mas sutilmente quebrado (como são fórmulas).

Tenha cuidado com a opção 3, pois alguns programas (incluindo o Excel e o Open Office Calc) não tratarão mais as vírgulas dentro dos campos ="" como escapadas. Isso significa que ="abc,xyz" abrangerá duas colunas e quebrará a importação.

O uso do formato "=""abc,xy""" soluciona esse problema, mas esse método ainda limita você a 255 caracteres devido ao limite de tamanho da fórmula do Excel.

    

Answer 2

Tivemos um problema semelhante no qual tínhamos arquivos CSV com colunas que continham intervalos como 3-5, e o Excel sempre os convertia em datas. 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 o abrimos no Excel, isso acionaria o assistente de importação de texto
3. Na Etapa 3 de 3, no assistente , informamos que as colunas em questão eram texto e elas foram importadas corretamente.

Você poderia fazer o mesmo aqui, eu acho.

Felicidades

    

Answer 3

Melhor solução é gerar o XML Workbook. Assim:

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

    

Answer 4

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.

    

Answer 5

Eu tinha números longos de contas sendo distorcidos.

Foi assim que consertei:

Abra o arquivo file.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á 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 no formato Excel e a coluna ainda deve estar ok!

    

Answer 6

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