CODEWITHSUNDEEP
powershellexport-to-csv
vik440
vik440

Reputation: 21

powershell export-csv format cells

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?

Upvotes: 1

Views: 299

Answers (1)

Theo
Theo

Reputation: 61068

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"}
)

Upvotes: 3

Related Questions