[sims] SIMS Report and Command Line Report produce different files

[Grazza]

Hi all,

 

I have done a report within SIMS which exports out a file that is TAB delimited.

 

However if I use the command line reporter (which I need for my programme) it produces a CSV.

 

Ideas please?

[LosOjos]

I'm afraid you're stuck with it!

 

What I have done in the past is write a small script to open the resulting CSV in Excel and save it as whatever format I need - also helps whereby SIMS is inconsistent on whether or not it encloses strings in quotes, which causes SQL havoc:

 

'Check arguments
If WScript.Arguments.Length <> 1 Then
WScript.Echo "Usage: CScript CleanCSV Path/To/CSV"
WScript.Quit
End If 

Set myExcel = CreateObject("Excel.Application")
myExcel.Visible = False
myExcel.Application.DisplayAlerts = False

Set myWorkbook = myExcel.Workbooks.Add()
Set mySheet = myWorkbook.Sheets.Add()

Dim arr(99)
For x = 0 to 99
arr(x) = 2
Next

Set QT = mySheet.QueryTables.Add("TEXT;" + WSCript.Arguments(0), mySheet.Range("$A$1"))
With QT
.TextFileCommaDelimiter = True
.TextFileColumnDataTypes = arr
.Refresh
End With

myWorkbook.SaveAs WScript.Arguments(0), 6
myWorkbook.Close
myExcel.Application.Quit

 

The arr() part is a bit of a hack to make sure Excel doesn't reformat any dates etc. (some of our reg groups are a number followed by 'E' followed by another number which Excel interprets as scientific notation!)

[Grazza]

It's a pain I need it tab delimited as the text being exported may contains "," so throws my programme as I did decode the text looking for ","

Why does it export CSV when the report is setup as a Tab Delimited report !

Sort it our Capita !!!!

[PhilNeal]

Please log a case.