☰

我怎样才能阻止 Excel 吞噬我宝贵的 CSV 文件并排出无用的数据？

tag-icon • tag-icon microsoft-excel csv
我怎样才能阻止 Excel 吞噬我宝贵的 CSV 文件并排出无用的数据？

我有一个数据库，它通过序列号跟踪小部件的销售情况。用户输入购买者数据和数量，并将每个小部件扫描到自定义客户端程序中。然后他们完成订单。这一切都完美无缺。

有些客户想要一个他们购买的小部件的 Excel 兼容电子表格。我们使用 PHP 脚本生成此电子表格，该脚本查询数据库并将结果输出为 CSV，其中包含商店名称和相关数据。这也很有效。

在记事本或 vi 等文本编辑器中打开时，该文件如下所示：

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

如您所见，序列号存在（在这种情况下是两次，并非所有次要序列号都相同）并且是长串数字。在 Excel 中打开此文件时，结果变为：

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

您可能已经注意到，序列号用双引号括起来。Excel 似乎不尊重 .csv 文件中的文本限定符。将这些文件导入 Access 时，我们没有任何困难。将它们作为文本打开时，完全没有麻烦。但 Excel 总是将这些文件转换成无用的垃圾。尝试指导最终用户使用非默认应用程序打开 CSV 文件的技巧变得令人厌烦。还有希望吗？是否有我无法找到的设置？Excel 2003、2007 和 2010 似乎就是这种情况。

答案1

但 Excel 却毫无例外地将这些文件转换成无用的垃圾。

Excel 是无用的垃圾。

解决方案

如果任何客户想要以 Excel 格式获取您的数据，却无法将这三列的可见格式更改为小数位为零的“数字”或“文本”，我会感到有些惊讶。但让我们假设，简短的操作方法文档是不可能的。

您的选择是：

  1. 在序列号中加入一个非数字而非空格的字符。
  2. 使用一些默认格式写出 xls 文件或 xlsx 文件。
  3. 作弊并将这些数字输出为公式="268435459705526269","",="268435459705526269"（您也可以这样做="268435459705526269",,="268435459705526269"以节省 2 个字符）。这样做的优点是可以正确显示，并且可能普遍有用，但存在微妙的缺陷（因为它们是公式）。

使用选项 3 时要小心，因为某些程序（包括 Excel 和 Open Office Calc）不再将=""字段内的逗号视为转义。这意味着="abc,xyz"将跨越两列并中断导入。

使用格式"=""abc,xy"""可以解决这个问题，但是由于Excel的公式长度限制，这种方法仍然将您限制为255个字符。

答案2

我们遇到过类似的问题，我们的 CSV 文件中的列包含 3-5 等范围，Excel 总是将它们转换为日期，例如 3-5 将是 3 月 3 日，之后切换回数字会给我们一个无用的日期整数。我们通过以下方法解决了这个问题

  1. 将 CSV 重命名为 TXT 扩展名
  2. 然后当我们在 Excel 中打开它时，这将启动文本导入向导
  3. 在向导中的第 3 步（共 3 步）中我们告诉它有问题的列是文本并且它们导入正确。

我想您可以在这里做同样的事情。

文本导入向导

干杯

答案3

更好的解决方案是生成 XML 工作簿。像这样：

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

该文件必须具有 .xml 扩展名。Excel 和 OpenOffice 可以正确打开它。

答案4

对于临时用户和一次性情况来说，导入向导是最佳解决方案。如果您需要编程解决方案，则可以使用 QueryTables.Add 方法（这是导入向导在后台使用的方法）。

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

相关内容