Writing information to a CSV file

I recently started working with the writeFile routine. I call up data out of the employee database based on a set of criteria. Now I can see my dataset just fine but am having difficulty having the system write it to the file. It either writes the name DATA into the file or it comes up with the error message about not being able to coerce into a string. The dataset is part of my root container as a customizer.

Here is the code I have tried so far

data = event.source.parent.dataset
filename = fpmi.file.saveFile(“test.csv”)
fpmi.file.writeFile(filename,data)

I have also tried adding str( ) arount the event source, then around the word dataset and finally around the word data in the fpmi code. I have also tried single and double quotes around data. But just cannot seem to get the right sequence of commands.

Thanks and have a great day.

You’re calling fpmi.file.writeFile with the arguments (filename,data). If you look in the manual, you’ll see that writeFile wants two arguments: the filname, and the data as a String or a byte array. This is where you are having difficulties - in your script, “data” is a DataSet, which is neither a String nor a byte array.

I suggest looking at the function fpmi.db.exportCSV, which you could use like this:

data = event.source.parent.dataset fpmi.db.exportCSV("test.csv", 1 , data)

You’d only use the fpmi.file.writeFile function if you had formulated the CSV yourself - that is, looped through the dataset, creating a String that was the dataset’s values in comma-separated format. But, why bother since fpmi.db.exportCSV exists?

Hope this helps,

Carl, Thanks for the info. It works quite well to make the .CSV file. There is one problem however and I am not quite sure what is causing it.

In the PLC that we are using there is a function to read a .csv file. I origionally had created the file using Excel. The file I created worked well. The file that I created using this script would not load. I am unsure why. I opened both files, the one created with Excel and the one created with FPMI and the data looked identical, however there was one small difference and I dont know if that is what is causing this. The difference is a unique character at the end of the data. The file created by excel has this and the file created by PMI does not. Both files load in Excel but only the one with the unique character loads in the PLC. Any ideas on how to tell what this character is and how to add it to the PMI file?

[quote=“Carl.Gould”]You’re calling fpmi.file.writeFile with the arguments (filename,data). If you look in the manual, you’ll see that writeFile wants two arguments: the filname, and the data as a String or a byte array. This is where you are having difficulties - in your script, “data” is a DataSet, which is neither a String nor a byte array.

I suggest looking at the function fpmi.db.exportCSV, which you could use like this:

data = event.source.parent.dataset fpmi.db.exportCSV("test.csv", 1 , data)

You’d only use the fpmi.file.writeFile function if you had formulated the CSV yourself - that is, looped through the dataset, creating a String that was the dataset’s values in comma-separated format. But, why bother since fpmi.db.exportCSV exists?

Hope this helps,[/quote]

After looking and looking I finally found out what I think the problem is. I dont know why I missed it the other day but when the exportCSV is called and it writes the file, it is putting quotes around all the data.

I have been on the Python web page and it states that the default to save in a CSV format is “,” so is there some other command that needs to be issued in PMI to make sure that the default is used and that quotes are not used?

Thanks for the info.

I don’t know what you mean. Yes, FactoryPMI puts quotes around each field of data in the CSV file. If you look at the so-called “spec” for CSV (en.wikipedia.org/wiki/Comma-separated_values), you’ll see that quoting all columns is legal. So, the problem lies in your PLC’s import routine.

If you want to write a custom CSV export routine, you can do this manually in Jython. See this post for an example:
inductiveautomation.com/foru … hlight=csv

Hope this helps,

1st off I did not say anything was wrong with using quotes. 2nd I do agree that there is a vague idea of what defines a CSV file. Some site say simply that it is called a CSV file because of the name, [color=red]Comma Seperated Variables[/color] Others say quotes is fine. And of course the list goes on and it breaks down even further with single and double quotes, etc…

However my question was, according the web site that I was on concerning Pyton, it clearly stated that the default delimiter on a .CSV file is indeed a comma. That by changing some of the attributes you can tell the system when writing the file to use any of the above mentioned delimiters.

So short of having to write a script as exampled does your PMI software have any atributes that can be set to define what character to use as the delimiter?

[quote=“Carl.Gould”]I don’t know what you mean. Yes, FactoryPMI puts quotes around each field of data in the CSV file. If you look at the so-called “spec” for CSV (en.wikipedia.org/wiki/Comma-separated_values), you’ll see that quoting all columns is legal. So, the problem lies in your PLC’s import routine.

If you want to write a custom CSV export routine, you can do this manually in Jython. See this post for an example:
inductiveautomation.com/foru … hlight=csv

Hope this helps,[/quote]

Nope, sorry. Our fpmi.db.exportCSV routine has been implemented independent of the information about CSV that you were reading on the Python website.

mrtweaver,

I’ve worked with a lot of PLC/HMI programs that export/import CSV files. Each application has its own twist on the format. Some handle quotes well and some don’t.

In most cases, you have to reverse engineer the format. Often the programs can import files created by it’s own export routine. So if you create a few sample tags (or other data) within the application and then export them, you can use the export file as an example. Once you know the structure of the file, you can modify the script Carl referenced earlier to construct a similarly structured file.

MickeyBob