Importing a CSV into Excel with formulas

[EBGreen]

Ok, so I'm actually doing this in Powershell, but asking the question here because I suspect that this is where I will find somjeone that knows the answer. It's all COM and Office automation anyway so language doesn't matter a lot in this.

Here is the situation, I have a CSV:

"ID","Business_Hours","Actual_Hours","TimeToAck","Dispatch_Stop","Open","Close","WasReopened","Impact","Status","Urgency","Problem_Type","Product_Type","IsBF","PM-01","PM-02"
"IM0521321","53.2316666666667","166.85",,"","4/26/2010 8:41:42 AM","5/10/2010 10:19:42 AM","True","4","closed","3","break / fix / defect - accuracy","network - lan","True","=if(E2="",TRUE,if(G2<E2,TRUE,FALSE))","=IF(N2 = FALSE, IF(K2=2, IF(C2<2, TRUE, FALSE), NOQUAL"")"
"IM0319185","385.296388888889","1486.29638888889","6.02E+23","","9/17/2009 9:46:33 AM","5/11/2010 9:09:54 AM","False","4","closed","3","break / fix / defect - accuracy","network - lan","True","=if(E2="",TRUE,if(G2<E2,TRUE,FALSE))","=IF(N2 = FALSE, IF(K2=2, IF(C2<2, TRUE, FALSE), NOQUAL"")"

I have code that creates a sheet in a workbook and imports the CSV:

	$xl = new-object -comobject excel.application
	$xl.Visible = $false	
	$xl.DisplayAlerts = $false
	$wb = $xl.Workbooks.Open("PATH\TO\FILE.XLS")
		$ws = $wb.WorkSheets.Add()
		$ws.Name = $group
		$import = $ws.QueryTables.Add("TEXT;D:\Projects\Ajilon\Metrics\BO_DAILY\$group.csv", $ws.Range("A1:A1"))
		$import.FieldNames = $true
		$import.RowNumbers = $false
		$import.FillAdjacentFormulas = $false
		$import.PreserveFormatting = $true
		$import.RefreshOnFileOpen = $false
		$import.RefreshStyle = 1
		$import.TextFileCommaDelimiter = $true
		$import.SavePassword = $false
		$import.Refresh() | Out-Null

Everything is working fine except that the last two columns are being imported as string literals instaead of evaluating as formulas. Usually my google-fu is pretty strong, but for some reason this issue is kicking my butt. Thanks for any help.


[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[EBGreen]

Got it. Just me forgetting about CSV double quote interpolation. Just needed to double up my double quotes.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[MasterRacker]

After the import, within Excel try calling Data > Text to Columns.

Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[MasterRacker]

Nearly simultaneous post. Glad you got it.

Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]