Button that exports to a customized csv file

Archived Topics
1

I am trying to create a button that updates a dataset and exports to a customized csv file.

Desired results:

  1. updates the dynamic dataset “Room_Temps” on my button.
  2. output to .CSV using fpmi.file.writeFile() to the desired format (shown below)
  3. Append a units character “°F” to the data.
  4. open the .csv somehow with the default app (optional)
    note: the first column value is the database column name

I have a dynamic dataset on my button called “Room_Temps” that gets
properly populated by the following SQL binding:

"SELECT
t_stamp,
Room_163_Temp, 
Breakdown_Room_Temp, 
RTE_Freezer_EA5_Temp, 
RTE_Freezer_EA6_Temp, 
RTE_Cooler_EA8_Temp,
RTE_Cooler_EA7_Temp, 
Frozen_Packaging_Temp, 
Hog_Receiving_Temp, 
Finished_Products_Temp, 
Raw_Cooler_Temp, 
Receiving_Dock_Temp, 
Hog_Cooler_Temp, 
Raw_Freezer_Temp, 
Pork_Proc_Room_11_12_Temp, 
Chill_Bagging_Temp_1_2, 
Case_Ready_Room_Temp,
Produce_Cooler_Temp, 
Pork_Proc_Room_13_14_Temp, 
Chill_Bagging_Temp_3_4, 
Raw_Prep_Room_Temp, 
Meat_Cooler_Temp, 
Raw_Packaging_Temp, 
Shipping_Dock_Temp, 
Shipping_Receiving_Dock2_Temp, 
RTE_Cooler_2_Temp, 
Costo_Production_Room_Temp, 
Outside_Temperature,
FROM room_temperature_status limit 1"

Here’s how I want the data formatted - this seems to open OK in Excel if I give it a .CSV extension.

t_stamp,Tue Feb 13 12:07:48 PST 2007
Breakdown_Room_Temp,33.1 °F
RTE_Freezer_EA5_Temp,19.5 °F
Frozen_Packaging_Temp,40.2 °F
Hog_Receiving_Temp,39.2 °F
Finished_Products_Temp,37.6 °F
Raw_Cooler_Temp,31 °F
Receiving_Dock_Temp,38.7 °F
Hog_Cooler_Temp,32.8 °F
Raw_Freezer_Temp,27.8 °F
Pork_Proc_Room_11_12_Temp,41.8 °F
Chill_Bagging_Temp_1_2,45.9 °F
Case_Ready_Room_Temp,36.4 °F
RTE_Cooler_EA8_Temp,33.9 °F
Produce_Cooler_Temp,39.3 °F
Pork_Proc_Room_13_14_Temp,41.6 °F
Chill_Bagging_Temp_3_4,43.4 °F
RTE_Freezer_EA6_Temp,37.2 °F
Raw_Prep_Room_Temp,40.3 °F
Meat_Cooler_Temp,32 °F
Raw_Packaging_Temp,36.6 °F
Shipping_Dock_Temp,40.2 °F
RTE_Cooler_EA7_Temp,33.7 °F
Shipping_Receiving_Dock2_Temp,54.2 °F
RTE_Cooler_2_Temp,35.8 °F
Costo_Production_Room_Temp,41.3 °F
Outside_Temperature,63.19029 °F
Wet_Bulb_Temp,54.2019 °F
2

Ok, sounds good. What part are you having trouble with?

3

I just wanted to ask, just in case, you’re sure you want the data like this, right? That is, as opposed to how “exportToCSV” would place it, with the columns in one row, and the values in another?

I ask because that would be the easiest way, and maybe you haven’t seen that function. Still, this way is do-able, Carl says he might need to post an example of how to loop through a non-py dataset in order to get the column names.

Later,

4

Travis and Nathan got me to here via phone and gotomeeting:

[code]
newDS = event.source.getPropertyValue(“Room_Temps”)
output = “”
i = 0

for col in range(newDS.columnCount):
if i == 0:
output += “%s, %s \r\n” % (“Time / Date”,newDS.getValueAt(0,col))
else:
output += “%s, %s °F\r\n” % (newDS.getColumnName(col),newDS.getValueAt(0,col))
i += 1

fpmi.file.writeFile(“temp.csv”, output)
fpmi.net.openURL(“temp.csv”)[/code]

Many thanks!

5

No problem Jeff.

In response to Colby’s comment, we started with fpmi.db.exportCSV() like the help file shows, but it was too wide to print, which was the point of this request.

I made the code a little more legible, then complicated it a little to protect from the possible IOError, if the file is already open and the user clicks the button.

dataSetName = "Room_Temps"
fpmi.db.refresh(event.source, dataSetName)
newDS = event.source.getPropertyValue(dataSetName)
output = ""

for col in range(newDS.columnCount):
   header = newDS.getColumnName(col)
   data = newDS.getValueAt(0,col)
   if header == "t_stamp":	
      header = "Time / Date"
   output += "%s, %s °F\r\n" % (header, data)
   
try:
   fpmi.file.writeFile("temp.csv", output)
   fpmi.net.openURL("temp.csv")
except IOError:
   fpmi.gui.warningBox("Spreadsheet already open")
6

After a little help from Nathan this afternoon and playing with the information I want at the top of the page that opens I now have this:

Note: on the first line I substituted the long list of column header names with "[other column data] or this would look ridiculous.

[code]query = “SELECT t_stamp,[other column data] FROM room_temperature_status LIMIT 1”

newDS = fpmi.db.runQuery(query) #returns pyDataSet
newDS = fpmi.db.toDataSet(newDS) #Need regular DataSet to get column headers
output = “” #initialize output var as string

for col in range(newDS.columnCount):
header = newDS.getColumnName(col)
data = newDS.getValueAt(0,col)
if header == “t_stamp”:
header = “Customer Name Room Temperatures” #Place the facility name in the header string
header += “\r\nRoom Name,Room Temp” #Place the column headers in the header string
output += “%s\r\n%s \r\n” % (data, header) #This places the data at the top row and the two header lines in the next row (All of it in the “output” string.)
else:
output += “%s, %s °F\r\n” % (header, data) #After the title and headers are set up, put the room name and data into the “output” string.
try:
fpmi.file.writeFile(“temp.csv”, output)
fpmi.net.openURL(“temp.csv”)
except IOError: #checks to see if the file is already open
fpmi.gui.warningBox(“Spreadsheet already open”)[/code]

Again many thanks to the IA team for your help with this. I plan on using the reporting plug-in in the future but it isn’t in the budget for this phase of the project. This more of an interim measure I wanted to give to them as they are manually logging the temperatures right now.