This is part 2 of our mini-series about the awesome and free “ImportExcel” PowerShell module by Doug Finke. Make sure you install the module before you play with this tip:
PS> Install-Module -Name ImportExcel -Scope CurrentUser -Force
When you export data to Excel files, you may sometimes encounter data that is misinterpreted by Excel. For example, often phone numbers are misinterpreted as numeric values. Here is an example that reproduces this problem:
# any object-oriented data will do # we create some sample records via CSV # to mimick specific issues $rawData = @' Phone,Name +4915125262524, Tobias 0766256725672, Mary 00496253168722567, Tom '@ | ConvertFrom-Csv # create this Excel file $Path = "$env:temp\report.xlsx" # make sure the file is deleted so we have no # effects from previous data still present in the # file. This requires that the file is not still # open and locked in Excel $exists = Test-Path -Path $Path if ($exists) { Remove-Item -Path $Path} $rawData | Export-Excel -Path $path -ClearSheet -WorksheetName Processes -Show
As you’ll see, when Excel opens, the phone numbers are auto-converted to integers.
To prevent this auto-conversion, use -NoNumberConversion, and specify the column(s) that should be excluded from conversion:
# any object-oriented data will do # we create some sample records via CSV # to mimick specific issues $rawData = @' Phone,Name +4915125262524, Tobias 0766256725672, Mary 00496253168722567, Tom '@ | ConvertFrom-Csv # create this Excel file $Path = "$env:temp\report.xlsx" # make sure the file is deleted so we have no # effects from previous data still present in the # file. This requires that the file is not still # open and locked in Excel $exists = Test-Path -Path $Path if ($exists) { Remove-Item -Path $Path} $rawData | Export-Excel -Path $path -ClearSheet -WorksheetName Processes -Show -NoNumberConversion Phone
Now, the “Phone” column will no longer be treated as numbers, and the phone numbers show correctly.
