Attempting to use PowerShell to emulate the Convert to Number function in Excel

Question

I receive a file everyday that I need to insert data into then process and format it to get a useful report. I am very new to PowerShell, but I am trying to automate this to save some time, and learn as I go.

The issue I am running into now is Column A has numeric values stored as text. While manually formatting it, I would normally click the popup next to the cell, which then gives an option of "Convert to Number". How do I emulate this using PowerShell? I have attempted a few different methods that have allowed me to format the text number, but none of them have managed to convert it from text to number. Another way of manually doing it is by going to Data -> Text to Columns, but even manually this doesn't seem like the appropriate way of doing this.

I need this as a number and not text because later in the process it will be used in a vlookup. Also this column is in the initial report I receive, which is a .xlsx file, not part of the data I am pulling from our database.

$date = (Get-Date).tostring("yyyy.MM.dd.")

$dbserver = "server"
$dbdatabase = "database"
$dbuser = "user"
$dbpass = "password"
$conn = New-Object system.data.sqlclient.sqlconnection
$conn.connectionstring = "server=$dbserver;database=$dbdatabase;user id=$dbuser;password=$dbpass;"

$query = @"
SELECT DISTINCT
    ORS.Reference2 AS 'CDL OrderID',
    ORS.PKID AS 'Sonic Tracking',
    D.DisplayCode AS 'HUB',
    ZRZ.RouteID AS 'Default Route',
    ORS.ROUTEID AS 'Assigned Route',
    CAST(ORS.CREATEDWHEN AS DATE) AS 'Order Created Date',
    p2.createdwhen  AS 'RECEIVE Scan Date',
    CASE WHEN p2.createdby like 'driver 9[0-9][0-9][0-9]' then 'Y'
         ELSE ''    
         END AS 'Scanned?',
    ORS.POSTDATE AS 'Post Date',
    ors.pod AS 'POD',
    ORS.COMPLETEDTIME AS 'CompletedTime',
    (STUFF((SELECT ', ', EC.ExceptionDesc, ' ( Added: ',  OSC.createdWhen, ' By: ',OSC.createdBy, ') ' 
    FROM tblOrderRouteStops_StatusCodes OSC 
    LEFT JOIN tblexceptioncodes ec on osc.statuscode = ec.exceptionid
    WHERE OSC.ORSID = ORS.PKID 
    ORDER BY osc.createdwhen
    FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, ' ') )
    AS  'Status Code'     

FROM tblOrderRouteStops ORS
    LEFT JOIN tblParcel P ON ORS.PKID = P.ORDERID and p.reference like '128%AP1'
    Left Join tblParcel p2 on p.reference = p2.reference and p2.createdby like 'driver 9[0-9][0-9][0-9]' 
    LEFT JOIN tblZones Z ON ORS.Zip = Z.Zip
    LEFT JOIN tblDepots D ON Z.DepotID = D.DepotID
    LEFT JOIN tblZonesRouteZones ZRZ ON Z.ZoneID = ZRZ.ZoneID AND ZRZ.ROUTELOOKUPID = 1
    LEFT JOIN tblOrderRouteStops_StatusCodes OSC ON ORS.PKID = OSC.ORSID
    LEFT JOIN tblExceptionCodes EC ON OSC.statusCode = EC.ExceptionID

WHERE 1=1
      
    AND ORS.CUSTID = 3919
    AND ORS.CREATEDWHEN > '2021-06-10'
    AND ORS.REFERENCE2 <> ''
      
ORDER BY ORS.pkid DESC

"@

$sqlcmd = New-Object system.data.sqlclient.sqlcommand
$sqlcmd.CommandText = $query
$sqlcmd.Connection = $conn
$sqladpt = New-Object system.data.sqlclient.sqldataadapter
$sqladpt.selectcommand = $sqlcmd
$data = new-object system.data.dataset
$sqladpt.fill($data)
$dataset = $data.Tables[0] 


#$wb = 'C:\Users\jthompson\Desktop\CDL Daily\' + $date + 'ReviewOrders.xlsx'
$wb = 'C:\Users\jthompson\Desktop\CDL Daily\test.xlsx'
$excel = new-object -ComObject excel.application
$excel.visible = $false
$excel.DisplayAlerts = $false
$wb = $excel.workbooks.open($wb)
$ws1 = $wb.worksheets.item(1)
$ws2 = $wb.worksheets.add()

$dataset | convertto-csv -notype -delimiter `t | clip
[void]$ws2.cells.item(1).pastespecial()

$ws2.usedrange.entirecolumn.autofit()
$ws1.UsedRange.horizontalalignment = -4131
$ws2.UsedRange.horizontalalignment = -4131
$ws2.columns.item(6).numberformat = "MM/dd/yyy"
$ws2.columns.item(7).numberformat = "MM/dd/yyy"
$ws2.columns.item(9).numberformat = "MM/dd/yyy"
$ws2.columns.item(1).numberformat = "0.000001"

//this will format it with the correct decimals, but does not change it from text to number  
$ws1.columns.item(1).numberformat = 0.000001


$wb.Save()
$wb.close()
$excel.Quit()
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($excel)
Remove-Variable excel

---

Edit

Message stating number in cell is formatted as text

Format cell dialogue box

To clarify, after I use numberFormat, in the "Format Cells" dialogue box it states they are formatted as decimals with 6 places. However they still have the green triangle, and the popup with the message " The number in this cell is formatted as text or preceded by an apostrophe". Even when I manually format it the only way I have found to change it from text to numeric is by clicking "Convert to Number" or "Text to Column". Changing the format in "Format Cells" seems to have no effect on this.

No it is not a table and it is not linked to a data import. I receive this report from a customer, so it has no connections or dependencies.

Answer 1

UPDATE (February 7, 2022), see this question:

Depending on your version of Excel, you may need to use .Value2 or .Value(10) instead of .Value:

$ws1.Columns.Item(1).Value2 = $ws1.Columns.Item(1).Value2

---

ORIGINAL ANSWER:

Similar to this, you can use:

$ws1.Columns.Item(1).Value = $ws1.Columns.Item(1).Value

Note that a cell's format and its underlying value are two separate things. Changing the NumberFormat (manually or programmatically) doesn't change the underlying value.

Answer 2

NumberFormat is the correct property - try out:

$excel = new-object -ComObject excel.application
$excel.visible = $false
$excel.DisplayAlerts = $false
$wb = $excel.workbooks.open('c:\temp\test.xlsx')
$ws1 = $wb.worksheets.item(1)
$ws1.Columns.Item(1).NumberFormat = "#,##0.000000"

$wb.Save()
$wb.close()
$excel.Quit()

I tested this with a very simple .xlsx doc:

A B
- -
1 A
2 B
3 C

before changing the format, I verified the numbers got saved in General format using $ws1.Columns.Item(1).NumberFormat. It returns General before the change, and returned Number format after saving and re-opening the file.