[sims] CSV Report - Date Format

[Cache]

Probably a stupid question, I've got a few reports recently created which export dates or dates of birth striaght into CSV format. All works well apart from the date is in the wrong format, I need to get it into the 22/10/2013 format instead of the standard 22 November 2013 format.

 

Anybody know anyway of forcing a csv export to change the date format from SIMS or is it just not possible?

 

Thanks

[CAM]

SIMS uses a macro when exporting reports to change the date format to a text string. It's commented in the code, so you need to create a new Excel template and comment out the line of VBA code performing the date fix.

[Cache]

Choosing the report type to text and then csv gives me the result I'm after apart from the date format, but doesn't open excel so can't see how to get around that bit.

[LosOjos]

I've been having some trouble with CSV myself and I'm afraid it's going to have to be a manual fix. You could write a script to open the file in Excel, change the format of the column and re-save it as CSV. I'm doing something similar at the moment (CSV is inconsistent as regards to using quotes around strings, causing another system issues reading it) so I can share that script with you (when I've written it) as a starting point if you want?

[Cache]

That would be great if you could @LosOjos, I'll try and look at it if I get chance myself.

 

Logged a change request before posting here, because some reports I created to enable staff to just upload straight to other websites too so I didn't have to get involved - 1310-1223128 if anyone is interested in supporting.

[LosOjos]

Ok, here you go:

 

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()

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

' INSERT FORMATTING ROUTINES HERE

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

 

I've labelled up where you need to do your formatting. As a for instance, if column D was the one that would contain the date you needed reformatting, you could do something like:

 

mySheet.Columns("D").NumberFormat = "dd/mm/yyyy"

 

I haven't tested that but it should work, give it a go and let me know how you get on.

 

Note also that it will overwrite the original file - I always tend to keep a back up while I'm testing to save me having to run the report from SIMS again.

 

EDIT: As for getting staff to run this without your involvement, you still could but you'd need to enable them to run a small BAT script. From that you could take their username and password for SIMS, run the report via CommandReporter and feed it straight in to your script. I'm doing something similar: the reports are run from CR, "cleaned" by my script then uploaded to a SQL DB for a system I have running on our intranet.

 

EDIT2: Note the usage comment, "CScript CleanCSV Path/To/CSV" - CScript is built in to Windows and runs the script from command line (important for unattended runs, any errors will be directed to CLI so code execution can continue, unlike the default behaviour of a dialog box that freezes execution until someone presses "OK") and the "CleanCSV" part is the name of the script; I have it saved as CleanCSV.vbs

Edited October 24, 2013 by LosOjos