鸿 网 互 联 www.68idc.cn

当前位置 : 服务器租用 > cms安装教程 > 新云cms > >

别动我的奶酪:CSV文件数据丢零现象及对策

来源:互联网 作者:佚名 时间:2015-08-11 09:11
CSV文件在读入EXCEL时,对于前面有零的数据项,比如电话号码,会自作聪明地丢掉那个零。 比如,我有一个北京客户,其号码为01059178888,如果这是通过CSV文件来的数据,在EXCEL中打开时,就成了1059178888,甚至会成为1E9。 当然,微软官方帮助文件说,你在

CSV文件在读入EXCEL时,对于前面有零的数据项,比如电话号码,会自作聪明地丢掉那个零。

比如,我有一个北京客户,其号码为01059178888,如果这是通过CSV文件来的数据,在EXCEL中打开时,就成了1059178888,甚至会成为1E+9。

当然,微软官方帮助文件说,你在打开此类文件时,手动指定列的类型就可以避免丢零。如果我有几个这样的列,倒还可以,如果有几十个这样的列呢?累死人啊。还有,有些用户就希望彻底自动化,对此,微软还真没有给出什么解决方案。全世界成千上万的用户在解决丢零问题上不知浪费了多少时间。

对微软来说,解决这个问题可以说是徒手之劳,比如,允许用户设置成,在读入CSV文件时,所有列都缺省为字符串,而不是让EXCEL自作聪明去解释数据。

请问,谁给了EXCEL改变我数据的权利?

 

当然,这种现象除了微软的傲慢外,CSV格式的先天不足是另外一个原因。CSV只包含数据,而对数据并无任何描述。这就使得微软对用户数据动手动脚提供了借口。

近年来,随着XML的进化与普及,随着微软软件的进一步公开化,这个问题的解决已显露端倪。

 

这里假设我要从数据库中读出一批数据,将其保存为文本文件,让用户在EXCEL上进一步处理。

我不希望丢零,又希望用户不费太多手脚。

(本文使用的验证环境为 EXCEL 2010)

 

 数据样品如下:

ID Name Phone Address
0001 李开复 01059178888 北京市朝阳区望京街8号利星行广场微软大厦 12 层
0002 张复开 01059179999 北京市朝阳区望京街9号利星行广场巨硬大厦 21 层

 

1. 输出CSV格式,数据如下:

0001,李开复,01059178888,北京市朝阳区望京街8号利星行广场微软大厦 12 层
0002,张复开,01059179999,北京市朝阳区望京街9号利星行广场巨硬大厦 21 层

 

如果在EXCEL中打开以上CSV格式的文件,将会出现:

ID Name Phone Address
1 李开复 1E+9 北京市朝阳区望京街8号利星行广场微软大厦 12 层
2 张复开 1E+9 北京市朝阳区望京街9号利星行广场巨硬大厦 21 层

ID前的0丢掉了不说,电话号码变成了科学计数法的数字。

 

2. 输出简单XML格式

因为是简单的XML格式,并不牵扯定义DAD等文件,在程序上容易实现。

例如:

<?xml version='1.0'?>
<?xml-stylesheet type="text/xsl" href="simple.xsl" ?>
<customer>
  <row>
 <ID>0001</ID>
    <Name>李开复</Name>
    <Phone>01059178888</Phone>
    <Address>北京市朝阳区望京街8号利星行广场微软大厦 12 层</Address>
  </row>
    <row>
 <ID>0002</ID>
    <Name>张复开</Name>
    <Phone>01059179999</Phone>
    <Address>北京市朝阳区望京街9号利星行广场巨硬大厦 21 层</Address>
  </row>
  </customer>

在EXCEL打开时,出现几个对话框,但只要按OK键,还是可以打开。但是,ID/Phone还是丢零。

ID Name Phone Address

1

李开复 1059178888 北京市朝阳区望京街8号利星行广场微软大厦 12 层
2 张复开 1059179999 北京市朝阳区望京街9号利星行广场巨硬大厦 21 层

别急,我们可以告诉EXCEL,这个ID列是文字。

下例中,通过给第一行数据置入文字串的做法,让EXCEL把所有的列都认作文字,而不是数字,从而避免丢零。

<?xml version='1.0'?>
<?xml-stylesheet type="text/xsl" href="simple.xsl" ?>
<DATA>
  <row>
 <A>ID</A>
    <B>Name</B>
    <C>Phone</C>
    <D>Address</D>

  </row>
  <row>
 <A>0001</A>
    <B>李开复</B>
    <C>01059178888</C>
    <D>北京市朝阳区望京街8号利星行广场微软大厦 12 层</D>
  </row>
    <row>
 <A>0002</A>
    <B>张复开</B>
    <C>01059179999</C>
    <D>北京市朝阳区望京街9号利星行广场巨硬大厦 21 层</D>
  </row>
  </DATA>

 

在EXCLE打开时: (第一行的abcd是新加的项目名称,红色部分才是原来的名称。)

A B C D
ID Name Phone Address

0001

李开复 01059178888 北京市朝阳区望京街8号利星行广场微软大厦 12 层
0002 张复开 01059179999 北京市朝阳区望京街9号利星行广场巨硬大厦 21 层

 这样的数据打开后,用户只要删除第一行也就可以了。

 

 3. 输出XML Spreadsheet 2003(XMLSS)格式

 XMLSS的格式就要复杂一些。但仍然是编程可以接受的范围。

但是,我们可以省略一些不必要的东西。下例就是一个不能再简化的XMLSS数据。

<?xml version="1.0"?>
 <?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
  xmlns:o="urn:schemas-microsoft-com:office:office"
  xmlns:x="urn:schemas-microsoft-com:office:excel"
  xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
  xmlns:html="http://www.w3.org/TR/REC-html40"
  xmlns:x2="http://schemas.microsoft.com/office/excel/2003/xml">
<Styles> 
<Style ss:ID="ce1"><NumberFormat ss:Format="Short Date"/></Style>
</Styles>
   <ss:Worksheet ss:Name="Sheet1">
   <Table ss:ExpandedColumnCount="5" ss:ExpandedRowCount="3" x:FullColumns="1"
    x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="13.5">
    <Column ss:Width="38.25"/>
    <Column ss:Width="52.5"/>
    <Column ss:Width="76.5"/>
    <Column ss:Width="281.25"/>
 <Column ss:Width="76.5"/>
    <Row>
     <Cell><Data ss:Type="String">ID</Data></Cell>
     <Cell><Data ss:Type="String">Name</Data></Cell>
     <Cell><Data ss:Type="String">Phone</Data></Cell>
     <Cell><Data ss:Type="String">Address</Data></Cell>
     <Cell><Data ss:Type="String">Created_Date</Data></Cell>
  </Row>
    <Row>
     <Cell><Data ss:Type="String">0001</Data></Cell>
     <Cell><Data ss:Type="String">李开复</Data></Cell>
     <Cell><Data ss:Type="String">01059178888</Data></Cell>
     <Cell><Data ss:Type="String">北京市朝阳区望京街8号利星行广场微软大厦 12 层</Data></Cell>
  <Cell ss:StyleID="ce1"><Data ss:Type="DateTime">2013-02-23T00:00:00.000</Data></Cell>
    </Row>
    <Row>
     <Cell><Data ss:Type="Number">0002</Data></Cell>
     <Cell><Data ss:Type="String">张复开</Data></Cell>
     <Cell><Data ss:Type="String">01059179999</Data></Cell>
     <Cell><Data ss:Type="String">北京市朝阳区望京街9号利星行广场巨硬大厦 21 层</Data></Cell>
  <Cell ss:StyleID="ce1"><Data ss:Type="DateTime">2013-02-23T00:00:00.000</Data></Cell>
    </Row>
   </Table>
  </Worksheet>
  </Workbook>

 

因为可以具体指定每个Cell的数据类型(Number/String/DateTime),可以更精确地显示各个数据的格式。

上例中故意把【0002】这个数据指定为Number,在读入Excel之后,这项数据前面的零将被丢掉。

ID Name Phone Address Created_Date

001

李开复 1059178888 北京市朝阳区望京街8号利星行广场微软大厦 12 层 2013/2/23
2 张复开 1059179999 北京市朝阳区望京街9号利星行广场巨硬大厦 21 层 2013/2/23

如何生成上述XMLSS格式的数据,这个太简单了,就不用我再说了吧。

 

 4. 直接生成XLSX文件

从Office2007之后,Excel文件后缀变成4位,xlsx。xslx文件实际上是一个zip文件。如果你改变xlsx后缀为zip,你就可以打开它。

你会发现,这是一大堆xml文件和其他数据文件的组合。如果你下点功夫,你会弄懂它的结构。

最近发现一位大牛为APEX写的一个插件,从ORACLE中能直接输出xlsx文件。

http://www.apex-plugin.com/oracle-apex-plugins/process-type-plugin/ir-report-to-excel-xlsx_74.html

有兴趣的话可以看看。我试着用过,修改了对列数目的限制,还是不错的。当然,还有一些其他问题,以后做别论。

网友评论
<