Date field values do not appear correctly in MS Excel after being exported to text from SM.

  • KM01371375
  • 27-Jan-2015
  • 27-Jan-2015

Summary

When export to text is used from a Service Manager webtier client and is then imported into MS excel some of the values for date fields are incorrectly displayed.

Question

How to correctly export date values and data from Service Manager webtier client using the "Export to text file" option.

Sometimes the date fields and their values appear as follows when viewed from Excel:

12/20/11 10:52:32
12/20/11 16:16:45
12/24/11 09:42:36
12/26/11 11:16:07
12/26/11 11:27:44
12/26/11 12:50:19
01-04-12 9:49
01-07-12 12:13
01-07-12 13:37

Answer

Example:

  1. Login to the webtier client as falcon
  2. Select the Incident Queue from the drop down.
  3. Goto More->Export to Text File
  4. Select the "Semicolon Seperated CSV" and click OK
  5. When Prompted to save the file choose "Save as"
  6. Save the file as "<filename>.txt". 
  7. Do not let excel bring the file up automatically.  You must open excel first after saving to a .txt file.
  8. Open MS Excel
  9. Goto File-Open and select your previously saved .txt file.
  10. You should be prompted with a Text Import Wizard
  11. For "Choose the file type the best describes your data:" --> Delimited
  12. Click Next
  13. Choose the semicolon delimiter and verify the data preview looks fine.  Click Next.
  14. In Step 3 of 3 highlight the incorrectly displayed date fields and select the Date value for Column data format
  15. Click Finish