Force open CSV with Column Data Format = TEXT in Excel (Powershell)

Question

I have this CSV file I generate using Export-CSV. Everything is fine with it but it display like this when opening in Excel because the cells are not formatted as TEXT:

I want to force open the CSV with the cells all set to TEXT like you can do manually with the interface.

Is there a way to automate that with PowerShell, opening the CSV in Excel with cells formatted as text?

Not sure if it will do what you want, but certainly worth taking a look at the ImportExcel module as it's the most featured module for Excel. — henrycarteruk
– henrycarteruk, Commented Nov 19, 2018 at 14:15
you CANNOT format a CSV file [beyond the delimiter used]. that is part of the definition of a CSV file. [grin] to format the file, you will have to do that in the import step via COM automation, OR do as @JamesC. recommends and use one of the PoSh Excel modules that can generate an excel file instead of a CSV file. — Lee_Dailey
– Lee_Dailey, Commented Nov 19, 2018 at 16:03
@lee_daily, would it be possible to do it with com automation though ? Having the CSV opened with text formatting for cells ? — Rakha
– Rakha, Commented Nov 19, 2018 at 22:36

Mike Twc · Accepted Answer · 2018-11-19 22:33:39Z

1

There is a little trick you can use - convert your data to html, and save with "xls" extention. For example:

Get-Process | convertto-html | Out-File csv2.xls

You'll see a warning when opening it, just click OK.

You can suppress that warning message by adding extra key in registry:

open regedit

HKEY_CURRENT_USER\Software\Microsoft\Office\15.0\Excel\Security

Create a new DWORD with name ExtensionHardening and value 0

answered Nov 19, 2018 at 22:33

Mike Twc

2,3752 gold badges17 silver badges20 bronze badges

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

Comments

Rakha · Accepted Answer · 2018-11-20 14:54:50Z

1

Found a very good way to make it happen!

After generating your CSV file, here is how to automatically load it into Excel with AutoFit column width and TEXT format for cells :) :

$Fichier = "PATH_TO_CSV.csv"
$objExcel = New-Object -ComObject Excel.Application
$WorkBook = $objExcel.Workbooks.Open($Fichier)
$WorkSheet = $WorkBook.worksheets.item(1)

$objExcel.Visible = $true

$Range = $worksheet.UsedRange.Cells
$range.NumberFormat = "@"

$WorkSheet.Columns("A:B").AutoFit()

edited Nov 20, 2018 at 14:54

answered Nov 20, 2018 at 13:40

Rakha

2,1025 gold badges37 silver badges65 bronze badges

Collectives™ on Stack Overflow

Force open CSV with Column Data Format = TEXT in Excel (Powershell)

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related