Stop Numeric converting to text in Excel when using powershell

Question

I am using powershell to copy data from CSV to an excel worksheet (In a existing spreadsheet). I have a pivot table in another worksheet that references the data that I am copying from the CSV. The only problem is, the numeric values in CSV files are getting converted to text in Excel which is throwing the calculated fields in the pivot table haywire.

The fields in the spreadsheet are defined as numeric (I've made sure of that)

The values in the CSV file are being exported from a SQL Query and are enclosed with quotes. I removed the quotes before loading it into the excel sheet and it makes no difference. What started out as a one time task ended up being used multiple times and I want to automate this.

Any suggestions here would be very helpful

The flow here is

Powershell script kicks off a SQL Script and copies output to a CSV file which contains 4 columns with numeric data
Data in the CSV file is copied to a worksheet in an existing spreadsheet with 2 worksheets
The 2nd worksheet contains a pivot table which references data in the first worksheet.

I'm not sure due to my unfamiliarity with some of your flow, but quote enclosed data in Excel is interpreted as text — Ron Rosenfeld
One suggestion: don't base your PivotTable on the raw data. Instead base it on a calculated column that parses the input. — gvee
how do you import the data into excel? I once made a powershell script that utilized excel to create xlsx files. there I just set the numberformat for the cells. — restless1987

Jean-Claude DeMars Jean-Claude DeMars · Accepted Answer · 2017-05-12T15:13:30

This isn't an issue with Powershell as much as it is with the way Excel imports the CSV.

Have you considered doing a Macro that imports (and formats) the data into the excel sheet instead of doing a CSV? Perhaps have the Powershell code write the data to specific format (or even CSV) file, then have the Macro in the Excel sheet read that file, and convert the cells from text to numeric as it places the data in each cell.

Another better option would be to have Powershell actually export the data into the excel spreadsheet directly, bypassing the entire CSV step; having Powershell set the actual cell to the proper type (numeric) as it exports the data using a COM object. Excel must be installed on the computer your running the script from for this to work.

A code snip it to get you started might look something like:

$xl=New-Object -ComObject Excel.Application
$wb=$xl.WorkBooks.Open('C:\Temp\MyData.xls')
$ws=$wb.WorkSheets.item(1)
$xl.Visible=$false # Don't make excel pop up before the person running the script.

$ws.Cells.Item(1,1)=1

$wb.SaveAs('c:\temp\MyData.xls')
$xl.Quit()
Remove-Variable xl

A walk thru of this code:

Create the connection object
Open the actual workbook
Specify what sheet you need in the workbook
Make sure it's visible (personal choice)
Set the value of the cell in question, in this case row 1, column 1
Save it back out
Close the connection to the work book (Close the ComObject)

If you want even more fancy: [int32]$a = 255

$xl=New-Object -ComObject Excel.Application
$wb=$xl.WorkBooks.Open('C:\Temp\Servers.xls')
$ws=$wb.WorkSheets.item(1)
$xl.Visible=$false  # don't show excel poped up to the user.

# set the cell to be the value we desire
$ws.Cells.Item(2,3)=$a

# is it a string?
if ($a -is [string])  
{
  # it's a string
  $ws.Cells.Item(2,3).NumberFormat = "@"
} else {
  # yes, numeric..-- set to a number
  $ws.Cells.Item(2,3).numberformat = "0" # or could be "0.00", etc..
}

# save and close the sheet..
$wb.SaveAs('c:\temp\Servers1.xls')
$xl.Quit()

If this answered your question, please mark "As Answered".

Stop Numeric converting to text in Excel when using powershell

1 Answers