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 位的列的数字末尾添加制表符。

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

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

3.2 代码#

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 逻辑#

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

3.4 性能问题#

单元格数量：243789

处理时长：2.19s

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

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

参考资料：

导出csv文件数字变科学计数法的解决方法 ↗

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