Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
Reading CSV's with Excel in Powershell - Formatting challenges RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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?  

    • Moved by Bill_Stewart Friday, March 15, 2019 12:47 AM Off-topic
    Wednesday, December 5, 2018 9:08 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    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