0

I am wondering if you can help, I want to be able to format column B to a Number column on export instead of running send keys once the csv has been created. I have looked at a few examples on Stack as well as other forums but unfortunately not very clear. any help would be awesome.

please see below my script, the top half is extracting the data from an sql db and then creates a csv with the result

$command             = $connection.CreateCommand()
$command.CommandText = "SELECT   T_CUSTOMERPRICELIST.C_DESCRIPTION AS ROLE , T_CUSTOMERPRICELISTBASESTANDARDRULE_PRICEDEFINITION.C_NETPRICE AS PRICE,T_CUSTOMERPRICELIST.C_REFERENCE AS MIN_QTY, T_CUSTOMERPRICELIST.C_REFERENCE AS FLAT_OR_DISCOUNT_PRICE, T_PRODUCT.C_D_PRODUCTID AS PRODUCT_ID


FROM T_CUSTOMERPRICELISTPRODUCTSTANDARDRULE 
INNER JOIN T_CUSTOMERPRICELIST ON T_CUSTOMERPRICELISTPRODUCTSTANDARDRULE.C__OWNER_ = T_CUSTOMERPRICELIST.C_ID
INNER JOIN T_PRODUCT ON T_CUSTOMERPRICELISTPRODUCTSTANDARDRULE.C_PRODUCT = T_PRODUCT.C_ID 
INNER JOIN T_CUSTOMERPRICELISTBASESTANDARDRULE_PRICEDEFINITION ON T_CUSTOMERPRICELISTPRODUCTSTANDARDRULE.C_ID = T_CUSTOMERPRICELISTBASESTANDARDRULE_PRICEDEFINITION.C__OWNER_
WHERE T_CUSTOMERPRICELIST.C_REFERENCE = '1' AND T_PRODUCT.C_D_PRODUCTID IS NOT NULL;"

$dataSet = New-Object System.Data.DataSet
$adapter = New-Object System.Data.SqlClient.SqlDataAdapter $command
$adapter.Fill($dataSet) | Out-Null

$DataSet.Tables[0] | export-csv -Path "C:\Program Files\Redec_ScanIQ\RI Scan IQ\IQCustomerPrice\CustPrice.csv" -NoTypeInformation



Start-Process "C:\Program Files\Redec_ScanIQ\RI Scan IQ\IQCustomerPrice\CustPrice.csv"

Start-sleep -Seconds 2

add-type -AssemblyName microsoft.VisualBasic
add-type -AssemblyName System.Windows.Forms
#[Microsoft.VisualBasic.Interaction]::AppActivate(“Microsoft Excel (32 Bit)”)
[System.Windows.Forms.SendKeys]::SendWait(“{Right 1}”)
Start-Sleep -Milliseconds 500
[System.Windows.Forms.SendKeys]::SendWait(“{Down 1}”)
Start-Sleep -Milliseconds 500
[System.Windows.Forms.SendKeys]::SendWait(“^+{Down}”)
Start-Sleep -Milliseconds 500
[System.Windows.Forms.SendKeys]::SendWait(“%{h}”)
Start-Sleep -Milliseconds 500
[System.Windows.Forms.SendKeys]::SendWait(“{o}”)
Start-Sleep -Milliseconds 500
[System.Windows.Forms.SendKeys]::SendWait(“{e}”)
Start-Sleep -Milliseconds 500
[System.Windows.Forms.SendKeys]::SendWait(“{tab}”)
Start-Sleep -Milliseconds 500
[System.Windows.Forms.SendKeys]::SendWait(“{down 1}”)
Start-Sleep -Milliseconds 500
[System.Windows.Forms.SendKeys]::SendWait(“{Enter}”)
Start-Sleep -Seconds 1
[System.Windows.Forms.SendKeys]::SendWait(“^{s}”)
Start-Sleep -Seconds 1
[System.Windows.Forms.SendKeys]::SendWait(“{enter}”)
Start-Sleep -Milliseconds 500
Stop-Process -Name EXCEL -force

This image is Before the Send Keys enter image description here

This image is After the Send Keys enter image description here

this is the csv which is produced, and the column named PRICE needs to be formatted to number

$csv1 = Import-Csv "C:\Program Files\Redec_ScanIQ\RI Scan IQ\IQCustomerPrice\CustPrice.csv" 
$csv2 = Import-Csv "C:\Program Files\Redec_ScanIQ\RI Scan IQ\customerpriceorig\OldFile.csv"
$differences = Compare-Object $csv1 $csv2 -Property PRICE -SyncWindow 0 -PassThru |Where-Object SideIndicator -eq '<=' |Select * -Exclude SideIndicator
$differences |Export-Csv "C:\Program Files\Redec_ScanIQ\RI Scan IQ\customerpriceorig\import\csvFinal.csv" -NoTypeInformation
RISL2023
  • 117
  • 8
  • Does this answer your question? [Excel CSV - Number cell format](https://stackoverflow.com/questions/137359/excel-csv-number-cell-format) – nimizen May 24 '22 at 11:03
  • unfortunately this doesn't as I want to use powershell possibly while exporting to csv – RISL2023 May 24 '22 at 11:09
  • 1
    Please show us what your current code produces, so we can see the column name and the actual content – Theo May 24 '22 at 11:57
  • 1
    Is that the CSV we're looking at of the Excel after all your SendKeys code? Column B looks like numbers to me... What number format do you want then? – Theo May 24 '22 at 12:04
  • sorry @Theo, i should have stated that, yes that is after all the send keys. i will add the before image now – RISL2023 May 24 '22 at 12:08
  • 1
    So, basically what you want is that the numbers are to be rounded to two decimals? – Theo May 24 '22 at 12:19
  • If rounding is all you need, do `foreach ($item in $DataSet.Tables[0].Rows) { $item['PRICE'] = [math]::Round([decimal]$item['PRICE'], 2) }` and after that `$DataSet.Tables[0] | Export-Csv ... ` – Theo May 24 '22 at 12:42
  • i need it formatted to a number column so i can use `Compare-Object` to spot any differences. i have added the Code for that, for some reason without the column being formatted it wont compare – RISL2023 May 24 '22 at 12:43

0 Answers0