In the power table, my time stamp data is shown as yyyy-MM-dd HH:mm:ss.xxx, as in 2023-01-01 12:34:56.789. However, when I save the file and it opens in Excel, the time stamps now read as mm:ss.x, as in 34:56.8. The events I'm logging happen over the course of many hours, so times of this format are near worthless. Is there a way around this alteration within the scripting? Or is this a limitation of .CSV and I should try exporting a .XML or .PDF?
you're both correct, the data looks fine in notepad++. Is there a way to override the automatic formatting? I imagine this would have to be done in Excel itself, meaning I'd need to do it on every client.
Okay, I'll dig around in Excel's settings to see if I can nuke the format changes permanently, but if I can't I can simply have operators format the row or import manually before printing. Thank y'all!
You can use system.dataset.formatDates to force the data into a specific string representation that Excel understands... much as I ideologically disagree with bending over backwards to support Excel, that's probably the most pragmatic solution.
You could also use exportExcel directly, which should export date-typed columns with appropriate markers for Excel to treat them as dates. The format it will use to render them is mostly up to Excel, though; we don't expose the ability to change it (unlike manually building the Excel document via POI, as Jordan's suggesting).
I'm having no luck finding a catch-all formatting setting within Excel, so I'll explore these options and find the path of least resistance. It'll certainly be better to handle this within ignition and remove the possibility of an operator forgetting to reformat, etc. I greatly appreciate it!
Hey Mason, did you find a good fix for this? I'm pulling data from a SQL database into my Power Table and exporting out into an Excel file instead of a CSV after reading this forum. This did change it from the mm:ss.x to the date, but not date and time like we want. I see Excel has an option for Date+hr:mm but not date+hr:mm:ss. I think if we could drop the seconds we could get around this by using the Excel file instead of the CSV.
The time I'm using is from the time stamp the transaction group stores, but I don't see how to change that so it doesn't record seconds.
In my case, swapping to exportExcel worked, not only printing the entire Date+HH:mm:ss.xxx but preserving my column widths as well. However, I did notice the number of decimals differed depending on the source of the query (if the information was inserted directly from a button press, or from a tag change script responding to action from the controller). Like @JordanCClark mentioned, the thread he linked has a lot of in-depth information on fabricating an XL file to your exact specifications if the exportExcel doesn't get you what you need. With Jordan's method, you can build the file essentially from scratch, allowing you to directly define the formatting you'd like to see all within the scripting.