0

I'm trying to convert an XLS file into CSV using Excel as a ComObject, yet maintaining the precision of the data concerned. I've tried changing the style to "@" or "Text" or similar for the formatting of each cell, but this still results in limited precision and doesn't seem to apply to the whole file.

For example: cell D4 in my source xls document has a value of 0.5124839309949 but is viewed as 0.51 due to Excel's Number formatting. Manually changing this to Text formatting resolves this but I need Powershell to run this for me, and then export (in CSV format) that same 0.5124839309949 rather than 0*.*51.

At the same time, I'm concerned about how this will treat date cells. As it stands, each row in my array has a date which Excel reads as DD/MM/YYYY except when put through to CSV this becomes the Excel serial date number (useless for my ultimate goal of bulk loading this into SQL Server.)

I'm aware that a File-SaveAs exercise in Excel produces a CSV but I need this to be run through Powershell due to business demands, and also, running this 'conversion' exercise manually also results in precision limits, so I am looking for a way around this please.

If you're able to amend the below Powershell script I'd be grateful! Thanks for your help.

#
# Set the current folder path to temp
Set-Location \\xyz01\data\shared\data_warehouse\ntrs\temp
#Backup files
Copy-Item -path *.* -recurse \\xyz01\data\Shared\data_warehouse\test\performance
# Set variables
$file = "ABC Trust*"
$infile = "\\xyz01\data\shared\data_warehouse\ntrs\temp\"+$file 
$outfile = "\\xyz01\data\shared\data_warehouse\test\temp\test_performance.csv"
# Set com object to Excel
$Excel = New-Object -ComObject Excel.Application
# Open the excel file and save as CSV 
$workbook=$Excel.Workbooks.Open($infile)
$worksheet=$workbook.Worksheets.Item(1)
$worksheet.range.("D") = $worksheet.range.("D").Text
$workbook.SaveAs($outfile,6) # 6 is the code for .CSV 
$workbook.Close($false) 
$Excel.Quit()

The below may help? I have tried to replicate all suggested in here, to no avail.

Community
  • 1
  • 1
Alex
  • 1
  • 2
  • Have you tried the Import-XLS script from the MS script gallery? Find it at: http://gallery.technet.microsoft.com/scriptcenter/17bcabe7-322a-43d3-9a27-f3f96618c74b – TheMadTechnician Aug 05 '14 at 16:07

1 Answers1

0

Try changing this line...

$worksheet.range.("D") = $worksheet.range.("D").Text

to this...

$worksheet.range.("D") = $worksheet.range.("D").Value
TechSpud
  • 3,418
  • 1
  • 27
  • 35
  • No luck unfortunately, though it would seem that the issue is the way in which the range is specified. Errors returned within Powershell read as follows: *Property 'D' cannot be found on this object; make sure it exists and is settable* I've also tried lowercase d, D:D, identifying columns by numbers etc. all of which have not worked. – Alex Aug 06 '14 at 08:23
  • Try changing to a cell reference (e.g. "D4"), rather than a column ("D") – TechSpud Aug 07 '14 at 01:15