Ignition v7.9: Exporting dataset to CSV file, large numbers being made into sig fig

Question

I have a data set where product numbers are quite large, when being exported they're being made into sig fig (E+...).

I can resolve this by converting the columns affected to a text data type prior to exporting but I cannot find a way ignition v7.9 supports.

Here is what I have currently:

# Create a variable that references the data set
component = event.source.parent.getComponent('table')

data = component.data



# Use system.dataset.toCSV to turn the formatted dataset into a CSV string.
csv = system.dataset.toCSV(data)
 
# Use system.file.saveFile to have the user find a directory to write to.
filePath = system.file.saveFile(fileName, "csv", "Comma Separated Values")
 
# Check the value of filePath to make sure the user picked a path before
# attempting to write.
if filePath:
    system.file.writeFile(filePath, csv)

note that the format cannot be fixed in excel, cannot convert to a string, the string remains as E+...

Product numbers are usually better stored as strings anyway. You're not going to do any arithmetic with them, are you? Can you extract the data row by row and write it yourself? — Tim Roberts, May 09 '23 at 03:03
No arithmetic with them, correct. I am sure I can do it manually by making the CSV string myself, though I was hopeful for a more concise method. — Sam, May 09 '23 at 03:28
I don't know "ignition". If there is a way to change the data type of the column, that's what you need. If it thinks everything is a number, then you'll have to do it by hand. — Tim Roberts, May 09 '23 at 04:21

score 0 · Answer 1 · answered May 09 '23 at 03:33

I don't have any experience on Ignition, since it is Python scripting embedded in Ignition and I cannot find out how to re-test my thought, but I can give you some advices.

CSV is not Excel: To review the CSV, you should open it as plain text, such as notepad to get the pure format, I believe that CSV file from toCSV function remain the number as well, whereas excel would make a bit different due to large number

If you want to overview it via Excel, it could achieve by several ways:

Try toExcel 8.0 onward or exportExcel 7.9 backward: https://docs.inductiveautomation.com/display/DOC79/system.dataset.exportExcel https://docs.inductiveautomation.com/display/DOC80/system.dataset.toExcel
Append special character for that number, typically is ': '123456789123456789
Find alternative ways of CSV export with custom format supported, it would be a double quote surrounded "123456", "my string here"

My recommendation is your code is fine, no need to change, just to view it as notepad, notepad++

score 0 · Answer 2 · answered May 21 '23 at 01:00

I know ignition quite well, and unfortunately, you will have to do the string conversion yourself, but even so, it can be done concisely:

data = system.dataset.toDataSet(system.dataset.getColumnHeaders(component.data), 
    [[str(component.data.getValueAt(row, column)) 
        for column in range(component.data.getColumnCount())] 
        for row in range(component.data.getRowCount())])

Since ignition uses an older version of jython, it is possible that you will want to use unicode( instead of str( if you are in a country that uses special characters and there is a chance that those characters could be in your dataset.

Ignition v7.9: Exporting dataset to CSV file, large numbers being made into sig fig

2 Answers2