Custom date format in SIMS Report

[pod]

Is it possible to have a custom date format in a SIMS report? I am exporting a SIMS csv file to a mysql database and want to use the standard mysql format yyyy-mm-dd as opposed to the SIMS dd-mm-yyyy.

Cheers,

Chris

[timzim]

Open your csv report in Excel. Highlight the column containg dates. Right click and choose Format Cells. Choose "Date" (should already be if dates in column). Change Type to 2001-03-14. Save as CSV. Hey presto!

[pod]

Thanks, but I am trying to automate the process as much as possible, familiar with excel, I'd like SIMS to do the work.

Chris

[timzim]

KB67226 would seem to suggest this isn't possible. You could temporarily change your computer's date format in Regional Settings but this is hardly automation.

[matt40k]

Assuming the file does magically appear in MySQL, can't you just have the import script convert it? What is it? A php file?

[superfletch]

I wanted to do exactly this and found it not to be possible I had to change the date format manually. This may have changed. Doing as timzim said might help but I'm pretty sure that SIMS insists on having the date set in a particular format.

I also needed the format you describe which was a non standard format for excel as well as SIMS, I needed to tell it the date format in the custom area of format cells.

I also found that if I reopened the spreadsheet after editting my changes were lost, so once you have the date format set then save the report and upload it before reopening it.


You could create a Macro in the document the report goes into?
OR
Using the SIMSReportExporter (command line util for SIMS Reports) it may be possible to pull the data out with the date setup the way timzim said.


I have to admit I'm rubbish with Macros and just used format cells.

EDIT

Matt 40k posted after I started writing this but that sounds like a good idea.

[vikpaw]

rtf reports allow you to modify date formats, but you wont want an rtf output, unless you bung all data in a table and then access that in a spreadsheet somehow. probably just going to be as much manual manipulation as loading the csv in excel.