MIS Systems Thread, CSV Report - Date Format in Technical; Probably a stupid question, I've got a few reports recently created which export dates or dates of birth striaght into ...
22nd October 2013, 07:06 PM #1
CSV Report - Date Format
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?
IDG Tech News
23rd October 2013, 09:20 AM #2
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.
23rd October 2013, 10:57 AM #3
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.
23rd October 2013, 11:41 AM #4
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?
Thanks to LosOjos from:
Cache (23rd October 2013)
23rd October 2013, 06:09 PM #5
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.
24th October 2013, 11:29 AM #6
Ok, here you go:
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:
If WScript.Arguments.Length <> 1 Then
WScript.Echo "Usage: CScript CleanCSV Path/To/CSV"
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"))
.TextFileCommaDelimiter = True
' INSERT FORMATTING ROUTINES HERE
myWorkbook.SaveAs WScript.Arguments(0), 6
I haven't tested that but it should work, give it a go and let me know how you get on.
mySheet.Columns("D").NumberFormat = "dd/mm/yyyy"
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
Last edited by LosOjos; 24th October 2013 at 11:35 AM.
Thanks to LosOjos from:
Cache (24th October 2013)
By Chunks_ in forum MIS Systems
Last Post: 25th August 2012, 01:39 PM
By localzuk in forum MIS Systems
Last Post: 17th August 2010, 04:06 PM
By nwblue in forum MIS Systems
Last Post: 10th December 2009, 12:59 PM
By pod in forum MIS Systems
Last Post: 2nd November 2009, 01:37 PM
Last Post: 4th June 2007, 10:10 PM
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)