Export-csv is missing the format while exporting hardware ID details

[Sivaramsharmar]

Hi All,

Got below script from Solved: Finding VID, DID & SVID from PCI devices in ESXi - VMware Technology Network VMTN.

$vmhosts =Get-VMHost
$report =@()foreach($ESXHost in$vmhosts){$esxcli =Get-EsxCli -VMHost $ESXHost -V2
    $nicfirmware =$esxcli.network.nic.list.Invoke()$fcfirmware =$esxcli.storage.san.fc.list.Invoke()$driversoft =$esxcli.software.vib.list.Invoke()foreach($nicfirmwareselect in$nicfirmware){$NetworDescription =$nicfirmwareselect.Description
        $NetworDriver =$driversoft |where{$_.name -eq ($nicfirmwareselect.Driver)}$NetworkName =$nicfirmwareselect.Name
        $NetworkFirmware =$esxcli.network.nic.get.Invoke(@{nicname=$nicfirmwareselect.Name}).DriverInfo.FirmwareVersion
        $ids =$esxcli.hardware.pci.list.Invoke()|where{$_.VMKernelName -eq $NetworkName}$report +=""|select@{N ="Hostname";E ={$ESXHost.Name }},@{N ="Hardware-Model";E ={$ESXHost.Model }},@{N ="device";E ={$NetworkName }},@{N ="driver";E ={$NetworDriver.Version }},@{N ="firmware";E ={$NetworkFirmware }},@{N ="description";E ={$NetworDescription }},@{N='VendorID';E={"{0:x}"-f [int]$ids.VendorID}},@{N='DeviceID';E={"{0:x}"-f [int]$ids.DeviceID}},@{N='SubVendorID';E={"{0:x}"-f [int]$ids.SubVendorID}},@{N='SubDeviceId';E={"{0:x}"-f [int]$ids.SubDeviceID}}}foreach($fcfirmwareselect in$fcfirmware){$fcDescription =$fcfirmwareselect.ModelDescription
        $fcDriver =$driversoft |where{$_.name -eq ($fcfirmwareselect.DriverName)}$fcName =$fcfirmwareselect.Adapter
        $fcFirmware =$fcfirmwareselect.FirmwareVersion
        $ids =$esxcli.hardware.pci.list.Invoke()|where{$_.VMKernelName -eq $fcName}$report +=""|select@{N ="Hostname";E ={$ESXHost.Name }},@{N ="Hardware-Model";E ={$ESXHost.Model }},@{N ="device";E ={$fcName }},@{N ="driver";E ={$fcDriver.Version }},@{N ="firmware";E ={$fcFirmware }},@{N ="description";E ={$fcDescription }},@{N='VendorID';E={"{0:x}"-f [int]$ids.VendorID}},@{N='DeviceID';E={"{0:x}"-f [int]$ids.DeviceID}},@{N='SubVendorID';E={"{0:x}"-f [int]$ids.SubVendorID}},@{N='SubDeviceId';E={"{0:x}"-f [int]$ids.SubDeviceID}}}}$report |Export-Csv -Path 'C:\ESXI HBA & NIC info.csv'

$report is showing the correct details in Powercli, but while exporting it to CSV, format is getting changed and it's showing as below.

It's not only on Vendor ID, it's changing for other servers for DID, SID , SDID.

Please let me know any way get exact output.

[LucD · Accepted solution]

That is the default behaviour from Excel, but why do you convert these values to [int] in the 1st place?

[Sivaramsharmar]

Hi Lucd,

I think I copied the edited script, I tried with [int] and it didn't worked, forgot to remove it before posting.

so we can't get the exact report in csv?

[LucD]

Again, this is an Excel "feature".
By formatting those columns to Text you will see the full value.

An alternative is to use the ImportExcel module and use the Set-CellStyle cmdlet

.

[Sivaramsharmar]

Hi Lucd,

when I formatted with text, value is changing below.