Hello, I am having some difficulty getting my time stamps to retain the right format when exported from power table to .csv. I am using the following script on button press: exp = event.source.parent.getComponent('ExportLog').data sDate = system.date.now() fsDate = system.date.format(sDate,"yyyy_MM_dd") fName = "Wash%s_%s_%s"%(cID,rxr,fsDate) fPath = system.dataset.exportCSV(fName, 1, exp) if fPath != None: system.net.openURL("file:///"+fPath.replace('\\','/')) 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? Any suggestions are appreciated. Thanks!

You could change the column on excel, Format cells, and try either date or just text type Edit: Or use this on excel? You can then not detect data type [image]

Have you checked the raw CSV as a text file, I've had issues before with excel manipulating my data in the past

What does the data in the CSV look like in a text editor (notepad)? It is likely that Excel is "helping" by applying a default format to the data for display and your data is fine.

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!

Using the POI libraries, you can format the dates however you want. Don't let the thread title fool you. I have scripts in there for both directions. :wink: [image] Copying an Excel file to a table's dataset Ignition The Apache POI libraries are included with Ignition. At the time I was only needing to read files, so xlrd was all that was necessary. I'm just starting to dig into POI, myself, but here's a start: EDIT 2021-08-23: Updated to allow JsonDataSets. Changed to allow for a ByteArray output instead of writing to a file. EDIT 2023-01-20: Added option for date formats. Default is 'yyyy-MM-dd hh:mm:ss'. EDIT 2023-05-12: Added options for first row and column, if needed to offset from A1 def datasetT…

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). Lots of ways to skin this cat, as ever :slight_smile:

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. :slight_smile: I greatly appreciate it!

I highly recommend exporting in Excel format if your purpose is to open it in Excel. CSV is a very "lossy" file format.

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.

Did you follow the link I provided?

Inductive Automation Forum

Issues With Time Stamp Format Upon Export to .CSV

Ignition

ignition80, vision, scripting

show post in topic

Home
Categories
Guidelines
Terms of Service
Privacy Policy

Powered by Discourse, best viewed with JavaScript enabled