Issues With Time Stamp Format Upon Export to .CSV

[Mason_Stevens]

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!

[Hayden_Watson]

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

[Tim_Carlton]

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.

[Mason_Stevens]

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.

[Hayden_Watson]

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

[Mason_Stevens]

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!

[JordanCClark]

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.

Copying a Excel file to a tables Dataset

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…

[PGriffith]

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

[Mason_Stevens]

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!

[pturmel]

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

[Andrew_Soltwedel]

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.

[JordanCClark]

Did you follow the link I provided?

[Mason_Stevens]

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.

[Andrew_Soltwedel]

Ah, okay. That's awesome I'm glad it worked for you.

Mine isn't getting me where I want it so I will have to follow @JordanCClark 's method. Which is fine I'd like to tweak some other things in how it transfers into excel anyway.