powershell export-csv format cells

Question

I'm working on a script which is using an object array and sending some of its data to a CSV file.

The array is something like

$data = @(
   [pscustomobject]@{FirstName='Joshua';LastName='Oliver';id='111111111111111111'}
   [pscustomobject]@{FirstName='Henry'; LastName='Wing';id='222222222222222222'}
)


$data | Select-Object -Property FirstName, id | Export-Csv "C:\Users\user\Documents\doc.csv" -NoTypeInformation

It works. However, the 'id' field is way too large, so when I open the generated csv file, excel rounds the number to something like 111111111111111000 (because it uses the scientific notation). The thing is: I can't lose any digit of this id field.

In a normal excel file I could format the cell as text and paste the id, it would work. But I need to find a way to do that on the script.

Any idea?

Theo · Accepted Answer · 2022-03-15 10:48:58Z

3

In order for Excel to import numeric fields as text, you need to preceed the values with a TAB character:

$data = @(
   # or do: [char]9 + '111111111111111111' or '{0}111111111111111111' -f "`t" if you prefer
   [pscustomobject]@{FirstName='Joshua';LastName='Oliver';id="`t111111111111111111"}
   [pscustomobject]@{FirstName='Henry'; LastName='Wing';id="`t222222222222222222"}
)

answered Mar 15, 2022 at 10:48

Theo

62k8 gold badges28 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

powershell export-csv format cells

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related