CODEWITHSUNDEEP
excelwindowspowershellcsvautomation
Vibhav MS
Vibhav MS

Reputation: 163

Stop Numeric converting to text in Excel when using powershell

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

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

Upvotes: 1

Views: 2928

Answers (2)

asinteger
asinteger

Reputation: 1

You should set column data types from TextFileColumnDataTypes property. Use integer array for this like.

$formatsArray = @(1,2,1,2,2,2,2,2)
$query.TextFileColumnDataTypes = $formatsArray

Information about values which use in array

If you want set all range of data types to general, here is the example

$excel = New-Object -ComObject excel.application 
$workbook = $excel.Workbooks.Add(1)
$worksheet = $workbook.worksheets.Item(1)
$TxtConnector = ("TEXT;" + "./myCSV")
$Connector = $worksheet.QueryTables.add($TxtConnector,$worksheet.Range("A1"))
$query = $worksheet.QueryTables.item($Connector.name)
### set all columns type general(1)
$query.TextFileColumnDataTypes = ,1 * $worksheet.Cells.Columns.Count

Upvotes: 0

Jean-Claude DuBois
Jean-Claude DuBois

Reputation: 376

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:

  1. Create the connection object
  2. Open the actual workbook
  3. Specify what sheet you need in the workbook
  4. Make sure it's visible (personal choice)
  5. Set the value of the cell in question, in this case row 1, column 1
  6. Save it back out
  7. 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".

Upvotes: 1

Related Questions