+ Post New Thread
Results 1 to 6 of 6
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 ...
  1. #1
    Cache's Avatar
    Join Date
    Apr 2008
    Location
    Cumbria
    Posts
    1,257
    Thank Post
    471
    Thanked 181 Times in 178 Posts
    Blog Entries
    3
    Rep Power
    66

    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?

    Thanks

  2. #2
    CAM
    CAM is offline

    CAM's Avatar
    Join Date
    Mar 2008
    Location
    Burgh Heath, Surrey
    Posts
    4,290
    Thank Post
    869
    Thanked 395 Times in 305 Posts
    Blog Entries
    60
    Rep Power
    288
    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.

  3. #3
    Cache's Avatar
    Join Date
    Apr 2008
    Location
    Cumbria
    Posts
    1,257
    Thank Post
    471
    Thanked 181 Times in 178 Posts
    Blog Entries
    3
    Rep Power
    66
    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.

  4. #4

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,562
    Thank Post
    1,469
    Thanked 1,221 Times in 830 Posts
    Rep Power
    752
    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?

  5. Thanks to LosOjos from:

    Cache (23rd October 2013)

  6. #5
    Cache's Avatar
    Join Date
    Apr 2008
    Location
    Cumbria
    Posts
    1,257
    Thank Post
    471
    Thanked 181 Times in 178 Posts
    Blog Entries
    3
    Rep Power
    66
    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.

  7. #6

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,562
    Thank Post
    1,469
    Thanked 1,221 Times in 830 Posts
    Rep Power
    752
    Ok, here you go:

    Code:
    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:

    Code:
    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
    Last edited by LosOjos; 24th October 2013 at 10:35 AM.

  8. Thanks to LosOjos from:

    Cache (24th October 2013)

SHARE:
+ Post New Thread

Similar Threads

  1. [SIMS] Sims Report in Word/Excel changes date format
    By Chunks_ in forum MIS Systems
    Replies: 10
    Last Post: 25th August 2012, 12:39 PM
  2. [SIMS] Timetable report - data format, not printable
    By localzuk in forum MIS Systems
    Replies: 9
    Last Post: 17th August 2010, 03:06 PM
  3. Short Date format
    By nwblue in forum MIS Systems
    Replies: 6
    Last Post: 10th December 2009, 11:59 AM
  4. Custom date format in SIMS Report
    By pod in forum MIS Systems
    Replies: 6
    Last Post: 2nd November 2009, 12:37 PM
  5. Replies: 5
    Last Post: 4th June 2007, 09:10 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •