How to avoid "System.ArgumentException: Not a valid expression" when using a colum name with square brackets in Spotfire SDK?

Question

I've got a working IronPython script for loading an Excel file and adding its contents to an existing data source. When loading the Excel file, I'm using a DataFlowBuilder with an ExpressionTransformation to perform some data conversions, e.g. converting columns from Int to Real.

This works unless the column name contains square brackets - whenever I try to use a name containing square brackets, I get a "System.ArgumentException: Not a valid expression: Cast([Process Time [h]] as Real)" exception (where "Process Time [h]" (without the quotes) is the name of the Excel column).

CODE

transformation = ExpressionTransformation()

transformation.ColumnReplacements.Add(
         "Process Time [h]",
         "Cast([Process Time [h]] as Real)",
         ColumnSelection([DataColumnSignature("Process Time [h]", DataType.Integer)])
)  

Things I've tried

• quoting the column name with double quotes "Cast([\"Process Time [h]\"] as Real)"
• quoting the column name with single quotes "Cast(['Process Time [h]'] as Real)"
• escaping the square brackets with \ "Cast([Process Time \[h\]] as Real)"
• escaping the square brackets with \\ "Cast([Process Time \\[h\\]] as Real)"

Any ideas? Or should I contact Tibco Spotfire support regarding this?

Answer 1

Cast([Process Time [h]]] as Real) should work. Note the unbalanced number of [ and ].