SteadyLatch

Guides · Excel and CSV

Excel CSV Import Drops Leading Zeros: Keep IDs as Text

Published · Tested with Microsoft 365 Excel 16.0 (build 20430), PowerShell 7.6.6 and Windows PowerShell 5.1 (build 26100) on Windows 11 Pro

A report export has an order ID 00123 and a ZIP code 02134. Someone opens the CSV in Excel, fixes one cell and saves. The IDs are now 123 and 2134, nothing complains, and the lookup that joins this file to another system finds no match. This guide reproduces the loss with desktop Excel, shows that saving makes it permanent, and tests four ways to load the file. One of them, Workbooks.OpenText with a text setting for the column, does nothing at all on a .csv file. Two scripts at the end do the safe version: one checks a CSV for values Excel would change and exits with code 1, the other loads it as text into an .xlsx.

Before you start

What Excel does to the file

The call that matches “open the CSV”, Workbooks.Open, on the semicolon version of orders.csv. The file holds text. Excel reads each field and keeps what it recognises as a number or a date:

Values in the file and what Workbooks.Open stored (three runs, same result)
ColumnIn the fileIn the cellStored as
order_id00123123number
order_id123456789012345678901,23E+19number, digits past 15 lost
zip021342134number
zip070307030number
part1E51,00E+05number (100000)
part007E27,00E+02number (700)
part004242number
part12-34dic-34a date (December 1934)
ship_date2026-01-05######## in a narrow columna date (a number with a date format)

Four ways to load it

The loaders that let you say “this column is text” are Workbooks.OpenText, a QueryTable and Power Query. Results on the same data, three runs each:

Which methods kept the values as text
MethodResult
Workbooks.Open on the .csvNumbers and dates, the table above
Workbooks.OpenText on the .csv, every column set to textSame as Open. The setting is ignored and no error is raised
Workbooks.OpenText on a copy named .txt, every column set to textText, all values as in the file
QueryTables.Add on the .csv with TextFileColumnDataTypesText, all values as in the file
Power Query (Csv.Document and Table.TransformColumnTypes with type text)Text, all values as in the file

The Automatic Data Conversion option

Microsoft 365 and Excel 2024 have a switch for this. Under File, Options, Data, Automatic Data Conversion, Microsoft lists “Remove leading zeros and convert to number” and the other three conversions, and says they apply when opening a .csv or .txt file. It also says the options “don’t directly affect data imported by using Power Query” (Microsoft Support).

Check the file before Excel sees it

Every cell that Excel changes has a value in the file that looks like what it will change. The script reads the CSV as plain text, no Excel needed, and reports each column that has a value matching one of four patterns: digits with a leading zero, 16 or more digits, digits around an E, and two groups of digits joined by - or /. It exits with 1 when it finds any, so a scheduled job or a CI step stops. An empty file or a missing one is an error with code 2, not a pass. Save it as Test-CsvTextColumns.ps1:

# Test-CsvTextColumns.ps1: find CSV columns that Excel would change when it opens the file.
# Exit 0 = no risky values, 1 = at least one column at risk, 2 = file unreadable or empty.
# Windows PowerShell 5.1 and PowerShell 7.
param(
    [Parameter(Mandatory)] [string] $Path,
    [string] $Delimiter = ','
)
$ErrorActionPreference = 'Stop'

# Each rule: the name shown in the report and the pattern of a value Excel would change.
$rules = @(
    @{ Name = 'leading zeros dropped';       Pattern = '^0\d+$' },
    @{ Name = 'digits past 15 rounded';      Pattern = '^\d{16,}$' },
    @{ Name = 'read as scientific notation'; Pattern = '^\d+[eE][+-]?\d+$' },
    @{ Name = 'read as a date';              Pattern = '^\d{1,2}[-/]\d{1,4}$' }
)

try {
    if (-not (Test-Path -LiteralPath $Path -PathType Leaf)) { throw "File not found: $Path" }
    $rows = @(Import-Csv -LiteralPath $Path -Delimiter $Delimiter -Encoding UTF8)
    if ($rows.Count -eq 0) { throw 'No data rows. An empty file is not the same as a clean one.' }
} catch {
    [Console]::Error.WriteLine("ERROR: $($_.Exception.Message)")
    exit 2
}

$findings = foreach ($column in $rows[0].PSObject.Properties.Name) {
    foreach ($rule in $rules) {
        $hits = @($rows | Where-Object { "$($_.$column)".Trim() -match $rule.Pattern })
        if ($hits.Count -gt 0) {
            [pscustomobject]@{
                Column  = $column
                Risk    = $rule.Name
                Rows    = $hits.Count
                Example = "$($hits[0].$column)".Trim()
            }
        }
    }
}

if ($findings) {
    $findings | Format-Table -AutoSize | Out-String -Width 200 | Write-Host
    Write-Host "RISK: $(@($findings | Select-Object -ExpandProperty Column -Unique).Count) column(s) would change in Excel. Import them as text."
    exit 1
}
Write-Host "OK: $($rows.Count) rows, no value Excel would change."
exit 0

On the invented file, in PowerShell 7.6.6 and in Windows PowerShell 5.1, in every run:

PS> .\Test-CsvTextColumns.ps1 -Path .\orders.csv

Column   Risk                        Rows Example
------   ----                        ---- -------
order_id leading zeros dropped          3 00123
order_id digits past 15 rounded         1 12345678901234567890
zip      leading zeros dropped          2 02134
part     leading zeros dropped          1 0042
part     read as scientific notation    2 1E5
part     read as a date                 1 12-34

RISK: 3 column(s) would change in Excel. Import them as text.
PS> $LASTEXITCODE
1

A file with values like A1 and Z9 printed OK: 2 rows, no value Excel would change. and exited with 0. A file with a header and no rows, and a path that does not exist, both exited with 2.

Load it as text from PowerShell

The second script uses the QueryTable from the table above. It counts the columns from the CSV, sets every one to text except the ones you list in -NumericColumns (numbered from 1), saves an .xlsx, and ends only the Excel process it started. Exit codes: 0 saved, 2 bad input, 3 Excel failed. Save it as Import-CsvAsText.ps1:

# Import-CsvAsText.ps1: load a CSV into a new .xlsx with every column as text,
# except the ones you list in -NumericColumns (1-based). Uses its own Excel process
# and ends only that process. Exit 0 = saved, 2 = bad input, 3 = Excel failed.
# Windows PowerShell 5.1 and PowerShell 7. Needs desktop Excel.
param(
    [Parameter(Mandatory)] [string] $Csv,
    [Parameter(Mandatory)] [string] $Xlsx,
    [string] $Delimiter = ',',
    [int[]] $NumericColumns = @()
)
$ErrorActionPreference = 'Stop'
Add-Type -Namespace Win32 -Name Native -MemberDefinition @'
[DllImport("user32.dll")] public static extern uint GetWindowThreadProcessId(IntPtr hWnd, out uint pid);
'@

try {
    $Csv  = (Resolve-Path -LiteralPath $Csv).ProviderPath
    $Xlsx = [IO.Path]::GetFullPath($Xlsx)
    $columnCount = @((Import-Csv -LiteralPath $Csv -Delimiter $Delimiter -Encoding UTF8) | Select-Object -First 1 |
        ForEach-Object { $_.PSObject.Properties }).Count
    if ($columnCount -eq 0) { throw 'No data rows or no header.' }
} catch {
    [Console]::Error.WriteLine("ERROR: $($_.Exception.Message)")
    exit 2
}

# xlGeneralFormat = 1, xlTextFormat = 2
$types = 1..$columnCount | ForEach-Object { if ($NumericColumns -contains $_) { 1 } else { 2 } }

$excel = $null; $excelPid = 0
try {
    $excel = New-Object -ComObject Excel.Application
    $excel.Visible = $false
    $excel.DisplayAlerts = $false
    [void][Win32.Native]::GetWindowThreadProcessId([IntPtr]$excel.Hwnd, [ref]$excelPid)

    $book  = $excel.Workbooks.Add()
    $sheet = $book.Worksheets.Item(1)
    $query = $sheet.QueryTables.Add("TEXT;$Csv", $sheet.Range('A1'))
    $query.TextFilePlatform         = 65001      # UTF-8
    $query.TextFileParseType        = 1          # xlDelimited
    $query.TextFileOtherDelimiter   = $Delimiter
    $query.TextFileColumnDataTypes  = [int[]]$types
    [void]$query.Refresh($false)
    $query.Delete()                              # keep the values, drop the live link
    $book.SaveAs($Xlsx, 51)                      # xlOpenXMLWorkbook
    $book.Close($false)
    Write-Host "Saved $Xlsx ($columnCount columns, text except: $(if ($NumericColumns) { $NumericColumns -join ',' } else { 'none' }))"
    $code = 0
} catch {
    [Console]::Error.WriteLine("ERROR: Excel failed: $($_.Exception.Message)")
    $code = 3
} finally {
    if ($excel) { try { $excel.Quit() } catch { } ; [void][Runtime.InteropServices.Marshal]::ReleaseComObject($excel) }
    if ($excelPid) { Start-Sleep -Milliseconds 500; Get-Process -Id $excelPid -ErrorAction SilentlyContinue | Stop-Process -Force }
}
exit $code

Run on orders.csv with column 5 numeric, three times in each PowerShell version:

PS> .\Import-CsvAsText.ps1 -Csv .\orders.csv -Xlsx .\orders.xlsx -NumericColumns 5
Saved C:\data\orders.xlsx (5 columns, text except: 5)
PS> $LASTEXITCODE
0

We read the saved .xlsx back without Excel, by unzipping it. The shared strings hold 00123, 02134, 1E5, 007E2, 0042, 12345678901234567890, 07030 and 12-34 exactly as in the CSV, and the qty cells are numbers (<c r="E2"><v>3</v></c>). No Excel process was left behind.

Limits

The code on this page was written for this guide and tested on the versions above. The data is invented.