Using Awesome Export-Excel Cmdlet (Part 2)

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:tempreport.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:tempreport.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.


Twitter This Tip! ReTweet this Tip!

GD Star Rating
loading...