解决Excel保存CSV时长数字丢失精度的问题

发表于 更新于 分类于 阅读次数 评论数

1. 问题

使用 Excel 保存 CSV 文件时，长度大于 11 位的数字会被强制以科学计数法形式储存，导致数据丢失精度，例如 Unix 时间戳1532793600000变为1.53279E+12，导致时间由2018-07-29 00:00:00变为2018-07-28 23:00:00。

2. 解决方法

此问题为 Excel 软件设计缺陷，将单元格格式改为文本亦无法解决，只能将数据内容本身改为文本格式来避免转换，如在前缀或后缀处增加占位符，下面是笔者编写的通过添加制表符尾缀实现此功能的程序。

3. 长数字转文本宏

3.1 使用

在 Excel 待处理 csv 文件内按下 Alt+F11 打开「Microsoft Visual Basic for Applications」，在左侧工程窗口空白处按下鼠标右键，选择「插入(N)」-「模块(M)」，随后在编辑区域内粘贴代码，按下 F5 运行即可给所有长度大于 11 位的列的数字末尾添加制表符。

使用数据时如何移除制表符：

  1. 如果应用于数据库，在导入时勾选类似「去除字符串两端的空白字符（如空格、制表符、换行符等）」的选项，如 SQLite 勾选 「Trim fields」，即可去除本程序增加的制表符还原为数字；
  2. 若应用于其他场景或数据库不支持自动去除制表符，可使用文本编辑器查找制表符替换为空白。

3.2 代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
Sub 长数字末尾加制表符转文本()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim lastColumn As Long
    Dim cell As Range
    Dim data As Variant
    Dim newData As Variant
    Dim i As Long
    Dim j As Long

    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    lastColumn = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

    data = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastColumn)).Value
    ReDim newData(1 To UBound(data, 1), 1 To UBound(data, 2))

    For Each cell In ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastColumn))
        i = cell.Row
        j = cell.Column
        If IsNumeric(data(i, j)) And Len(CStr(data(i, j))) > 11 Then
            newData(i, j) = data(i, j) & vbTab
        Else
            newData(i, j) = data(i, j)
        End If
    Next cell

    ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastColumn)).Value = newData
End Sub

3.3 逻辑

  1. 初始化变量：创建变量以存储工作表、最后一行和最后一列的位置、单元格值和数据数组；
  2. 获取活动工作表：设置操作对象 ws 为当前活动工作表；
  3. 确定数据范围：计算最后一行和最后一列的位置，以确保只处理包含数据的部分；
  4. 读取数据：将工作表中指定范围的数据读取到二维数组 data 中，以提高处理速度；
  5. 创建新数据数组：定义 newData 数组以存放处理后的数据，维度与 data 相同；
  6. 遍历单元格：使用两个嵌套循环遍历每个单元格，外层循环遍历行，内层循环遍历列；
  7. 检查数字长度：判断每个单元格的值是否为数字且长度是否大于 11；
  8. 添加制表符：如果条件满足，将该单元格的值末尾添加制表符 vbTab，否则保留原始值；
  9. 写回数据：将修改后的 newData 数组写回到工作表，更新所有单元格的值。

3.4 性能问题

单元格数量：243789
处理时长：2.19s

为避免遗漏，本程序采取逐个单元格遍历判断长度决定是否修改的方法，性能不佳。以下是改为通过读取每列最后一行的值来判断列数据长度的程序，处理时长缩短至 1.14s，性能提升近一倍，仅适用于列数据长度统一的情况。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
Sub 长数字末尾加制表符转文本()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim lastColumn As Long
    Dim j As Long
    Dim i As Long
    Dim cellValue As Variant
    Dim data As Variant

    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    lastColumn = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

    data = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastColumn)).Value

    For j = 1 To lastColumn
        cellValue = data(lastRow, j)
        If IsNumeric(cellValue) And Len(CStr(cellValue)) > 11 Then
            For i = 1 To lastRow
                data(i, j) = data(i, j) & vbTab
            Next i
        End If
    Next j

    ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastColumn)).Value = data
End Sub

参考资料：

  1. 导出csv文件数字变科学计数法的解决方法
相关推荐