none
Reading CSV's with Excel in Powershell - Formatting challenges

    Question

  • Folks,

         I am struggling to read a CSV file with an Excel COM object properly.  I went this route because i had pre-existing snippets from other scripts that didn't have quite this challenge, but am considering switching to using import-csv directly and calling it a day.

         That said though, hoping there is a quick fix.   I have a CSV file with 27 columns of data.  5 or so are date values in a format that needs manipulation.   When opened in Excel desktop, the values are obfuscated for the most part by default Excel formatting.   The date columns that are too wide for the default view are displayed as ###### and a fiel used for and OrderID is converted to scientific notation.   I didn't expect this to happen, but when i read the value for one of these cells in Excel - it reads the ####### not the real value of the column/row position. 

          My current focus is on the date fields.  I'm trying to use the worksheet.cells.entirecolumn.autofit action() - since this double-click action on the desktop application expands them to appropriate views.  however this isn't working in Powershell, I still get the ###### value.

         Assuming no quick fix, I'm thinking I just need to shift to import-csv, and then iterate the populated array to manipulate the data where necessary.   Not earth shattering - I was just hoping to continue using this established process as I do more.

         Any thoughts?  

    Wednesday, December 5, 2018 9:08 PM

All replies

  • Use

    $xl = New-Object -ComObject Excel.Application
    $xl.Visible = $true
    $wb = $xl.Workbooks.Open('d:\scripts\stateCity.csv')
    $wb.Worksheets['StateCity'].UsedRange.Columns.AutoFit()

    The ###### is not data.  It is just showing you that the column is too narrow to correctly display the value.


    \_(ツ)_/


    • Edited by jrv Wednesday, December 5, 2018 10:25 PM
    Wednesday, December 5, 2018 10:24 PM