PowerShell - Import Excel then Export CSV without using Excel or COM

Question

I am developing a PowerShell script to import an Excel file and output the data to a flat file. The code that I have below works fine except that it fails to preserve leading zeros; when the CSV file is opened in a text editor, the leading zeros are not present. (Leading zeros are necessary for certain ID numbers, and the ID numbers are stored in Excel using a custom format.) Does anyone have any thoughts on how to get the ImportExcel module to preserve the leading zeros, or, perhaps another way of getting to the same goal? I would like to do this without using the COM object and without having to install Excel on the server; that's why I've been trying to make the ImportExcel module work.

$dataIn = filename.xlsx ; $dataOut = filename.csv
Import-Excel -Path $dataIn | Export-Csv -Path $dataOut

Steven Steven · Accepted Answer · 2019-12-02T18:40:40

I presume you're using the ImportExcel module?

I just did this and it worked. I created a spreadsheet like:

Name         ID1          ID2
Steven  00012345     00012346

I gave them a custom number format of 00000000 then ran:

Import-Excel .\Book1.xlsx | Export-Csv .\book1.csv

When looking at the csv file I have both ID numbers as quoted strings:

"Name","ID1","ID2"
"Steven","00012345","00012346"

Is there anything else I need to do to reproduce this? Can you give the specifics of the custom number format?

Also withstanding your answer to above. You can modify the properties of each incoming object by converting them to strings. Assuming there's a fixed number of digits you can use the string format with the .ToString() method like:

(12345).ToString( "00000000" )

This will return "00012345"...

So redoing my test with regular numbers (no custom format):

$Input = @(Import-Excel \\nynastech1\adm_only\ExAdm\Temp\Book1.xlsx)
$Input | 
ForEach{
    $_.ID1 = $_.ID1.ToString( "00000000" )
    $_.ID2 = $_.ID2.ToString( "00000000" )
}

This will convert ID1 & ID2 into "00012345" & "00012345" respectively.

You can also use Select-Object, but you might need to rename the properties. If you are interested I can demo that approach.

Note: the @() wrapping in my example is because I only have the 1 object, and is partly force of habit.

Let me know how it goes.

PowerShell - Import Excel then Export CSV without using Excel or COM

1 Answers