# Populating a Table With a Text File **URL:** \ **Category:** Archived Topics\ **Created:** [March 2, 2007, 4:20pm UTC](https://forum.inductiveautomation.com/t/populating-a-table-with-a-text-file/229 "2007-03-02T16:20:26Z")\ **Posts on this page:** 13\ **Page:** 1
**Author:** ![Moody](https://avatars.discourse-cdn.com/v4/letter/m/3d9bf3/32.png) [@Moody](https://forum.inductiveautomation.com/u/Moody)\ **Post date:** [March 2, 2007, 4:20pm UTC](https://forum.inductiveautomation.com/t/populating-a-table-with-a-text-file/229/1 "2007-03-02T16:20:26Z")
I’m a student programmer, and most of my experience is limited to C++ and Perl. Although Factory PMI is a great designer, I’m having a little trouble getting used to it. In my project, I’d like to load a table with values from a text file when the user starts the program. This is what I have in my global startup script: [quote]filename = “V:\API Manuals\MPMS\Chapter 11 (Physical Properties Data)\API\_Correction\_60.txt”; if fileExists(filename): bytes = fpmi.file.readFileAsBytes(filename) [/quote] How do I load those bytes into the table? ---
**Author:** ![nathan](https://avatars.discourse-cdn.com/v4/letter/n/c5a1d2/32.png) [@nathan](https://forum.inductiveautomation.com/u/nathan)\ **Post date:** [March 2, 2007, 8:48pm UTC](https://forum.inductiveautomation.com/t/populating-a-table-with-a-text-file/229/2 "2007-03-02T20:48:31Z")
Could you provide a sample of the data? If it’s not comma (or something similar) separated/delimited, you may want to use another component such as a Document Viewer. ---
**Author:** ![Carl.Gould](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/carl.gould/32/7262_2.png) [@Carl.Gould](https://forum.inductiveautomation.com/u/Carl.Gould)\ **Post date:** [March 2, 2007, 9:36pm UTC](https://forum.inductiveautomation.com/t/populating-a-table-with-a-text-file/229/3 "2007-03-02T21:36:42Z")
First of all - it sounds very strange to load data from a file into a database when a client starts up. I just want to make sure that you understand our architecture, that is, the database is global and you typically have multiple clients started at once. That said, assuming you have a good reason to do this or are doing it just as an exercise, read on. I gather from your document name (ending in “.txt”) that this is a text file, so the fpmi.file.readFileAsBytes probably isn’t appropriate. You should use fpmi.file.readFileAsString. Once you read the file as a string, how you load it into the database is highly dependent on what the file looks like. For the sake of example, lets say it has some data as comma separated values. You might load it up like this: `contents = fpmi.file.readFileAsString(filename) values = contents.split(",") for value in values: fpmi.db.runUpdateQuery("INSERT INTO MyTable (MyColumn) VALUES ('%s')" % value) ` Hope this helps, ---
**Author:** ![Moody](https://avatars.discourse-cdn.com/v4/letter/m/3d9bf3/32.png) [@Moody](https://forum.inductiveautomation.com/u/Moody)\ **Post date:** [March 5, 2007, 3:30pm UTC](https://forum.inductiveautomation.com/t/populating-a-table-with-a-text-file/229/4 "2007-03-05T15:30:22Z")
Thanks for your help so far! I’m actually allowing the user to load a strapping table from a file. The file type probably won’t be a text file, but I’m just testing right now. test.txt: [quote]0-0-0 0.0 0-1-2 69.0 0-3-8 120.0 0-3-14 144.0[/quote] The file contains strap values and their corresponding volumes. I’ve changed the program to load this information into “Strap\_Table” when the user clicks a load button. [quote]filename = fpmi.file.openFile() contents = fpmi.file.readFileAsString(filename) strings = contents.split("\t") for string in strings: fpmi.db.runUpdateQuery(“INSERT INTO Strap\_Table (FT\_IN\_16) VALUES (’%s’)” % string) [/quote] I realize that the loaded information will not be correct at this point. However, after I load the text file, I get “Error 301: Invalid object name ‘Strap\_Table’.” I’m sure that I used the correct names for my table and column. Any ideas? Thanks again for your help. ---
**Author:** ![Carl.Gould](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/carl.gould/32/7262_2.png) [@Carl.Gould](https://forum.inductiveautomation.com/u/Carl.Gould)\ **Post date:** [March 5, 2007, 6:24pm UTC](https://forum.inductiveautomation.com/t/populating-a-table-with-a-text-file/229/5 "2007-03-05T18:24:12Z")
Well, that error message doesn’t leave much room for negotiation - the database doesn’t recognize the name “Strap\_Table”. Maybe you’re using a case sensitive database and it should be “strap\_table”? Maybe you have multiple datasource connections and are running the query through the wrong one? Hope this helps, ---
**Author:** ![Moody](https://avatars.discourse-cdn.com/v4/letter/m/3d9bf3/32.png) [@Moody](https://forum.inductiveautomation.com/u/Moody)\ **Post date:** [May 24, 2007, 2:27pm UTC](https://forum.inductiveautomation.com/t/populating-a-table-with-a-text-file/229/6 "2007-05-24T14:27:22Z")
Okay, now that I better undertand how Factory PMI works, I think I can approach this problem better. Is there a way to load Strap\_Table with an Excel file? Do I need to convert the file? Should I use fpmi.file.writeFile? ---
**Author:** ![Carl.Gould](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/carl.gould/32/7262_2.png) [@Carl.Gould](https://forum.inductiveautomation.com/u/Carl.Gould)\ **Post date:** [May 24, 2007, 2:47pm UTC](https://forum.inductiveautomation.com/t/populating-a-table-with-a-text-file/229/7 "2007-05-24T14:47:06Z")
Do you want to load the data from an excel file into a database table once, or repeatedly from a FactoryPMI window? fpmi.file.writeFile writes a file. It sounds to me like you need to read the excel file to put it into the database. I’d try to save the excel file as a CSV file, and then read and parse it with fpmi.file.readFileAsString. (FactoryPMI does not know how to read data from Excel files) If you give us a few more details (what does the excel file look like, what should the Strap\_Table look like, how/when do you want this load to occur), I should be able to have some more specific pointers. Hope this helps, ---
**Author:** ![nathan](https://avatars.discourse-cdn.com/v4/letter/n/c5a1d2/32.png) [@nathan](https://forum.inductiveautomation.com/u/nathan)\ **Post date:** [May 24, 2007, 3:01pm UTC](https://forum.inductiveautomation.com/t/populating-a-table-with-a-text-file/229/8 "2007-05-24T15:01:50Z")
Carl’s March 2nd response on how to load and store the data from your text file into the database makes the most sense to me. This can be easily adapted to run at startup for a file at a fixed location, or set up with a file browser and button. FactoryPMI can then deal with the data in the SQL database natively. One good way of importing data into the database would be with a .CSV (comma separated values) file, which is a format that Excel supports. [This post](http://www.inductiveautomation.com/forum/viewtopic.php?t=2480) shows how you can run a MySQL query to get the data from your spreadsheet into a MySQL table. fpmi.file.writeFile is for using FactoryPMI to actually write a file to disk. It’s going the other way than you’re expecting. If you truely want to read in the file and use it in FactoryPMI directly (without going through the SQL database) you can use fpmi.file.readFileAsString. You would then populate objects on the screen, or dataSets, or whatever else you’re trying to accomplish. ---
**Author:** ![Moody](https://avatars.discourse-cdn.com/v4/letter/m/3d9bf3/32.png) [@Moody](https://forum.inductiveautomation.com/u/Moody)\ **Post date:** [May 24, 2007, 3:49pm UTC](https://forum.inductiveautomation.com/t/populating-a-table-with-a-text-file/229/9 "2007-05-24T15:49:10Z")
Thanks! That March 2nd post was very helpful. The excel file looks something like this: 1 0-1-2 2.4 2 0-7-4 4.3 3 1-11-10 8.9 4 3-10-13 10.7 The name of the file is stored in a Tank\_Table database. The file itself needs to be loaded into Strap\_Table with columns Col 1, Col 2, and Col 3. Strap\_Table is not directly connected to the sql database. The file is loaded upon startup and/or when the user chooses to change the file. Here's the code for when the user tries to load a different file: `value = fpmi.file.openFile("csv") if value != None: fpmi.db.runQuery("LOAD DATA LOCAL INFILE '%s' INTO TABLE Strap_Table FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' ('Col 1', 'Col 2', 'Col 3')" %value) ` I keep getting this error: > [@](#): > > ValueError: SQL Error: Line 1: Incorrect syntax near 'DATA'. I get a similar error when I use runUpdateQuery instead. Any ideas? ---
**Author:** ![Carl.Gould](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/carl.gould/32/7262_2.png) [@Carl.Gould](https://forum.inductiveautomation.com/u/Carl.Gould)\ **Post date:** [May 24, 2007, 4:55pm UTC](https://forum.inductiveautomation.com/t/populating-a-table-with-a-text-file/229/10 "2007-05-24T16:55:53Z")
I suppose that could work, if your client is running on the same computer as the database. With FactoryPMI’s architecture, this is usually not the case. Try using fpmi.db.runUpdateQuery for the LOAD command. It is possible that the LOAD command doesn’t work through JDBC (you’d have to check the MySQL JDBC driver docs) - try it on a local MySQL client to double-check. ---
**Author:** ![Moody](https://avatars.discourse-cdn.com/v4/letter/m/3d9bf3/32.png) [@Moody](https://forum.inductiveautomation.com/u/Moody)\ **Post date:** [May 24, 2007, 6:36pm UTC](https://forum.inductiveautomation.com/t/populating-a-table-with-a-text-file/229/11 "2007-05-24T18:36:48Z")
Hmm, runUpdateQuery didn’t seem to work, so I’m trying to populate the strap table through a mouseClicked action script. Here’s what I have right now: ```auto path = fpmi.file.openFile("csv") table = event.source.parent.getComponent('StrapTable').data if path != None: text = fpmi.file.readFileAsString(path) i = 0 line = text.split("\n") for i in range(0, 4): word = line[i].split(",") fpmi.gui.messageBox("%s %d %s %s" % (line[i], word[0], word[1], word[2])) #debugging table[0,0] = word[0] ``` My problem is with the last line. How can I edit that table’s data? I’m sure this is something very basic, but nothing seems to work. Should I just cave in and use a separate SQL dataset for every tank? Egad, I sometimes wish C++ wasn’t my first language. ---
**Author:** ![Carl.Gould](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/carl.gould/32/7262_2.png) [@Carl.Gould](https://forum.inductiveautomation.com/u/Carl.Gould)\ **Post date:** [May 24, 2007, 6:52pm UTC](https://forum.inductiveautomation.com/t/populating-a-table-with-a-text-file/229/12 "2007-05-24T18:52:27Z")
Ooooh, you’re trying to populate an on-screen table component, not a database table. Ok, that changes things, lets see… To modify a table component’s data, you must set it to your own DataSet (you don’t get the data and modify it directly) To make a new DataSet, you use fpmi.db.toDataSet(), which takes a list of headers and a list of lists. See the documentation for more details. You’re on the right track by reading the file in as a string. Try something like this: [code]path = fpmi.file.openFile(“csv”) headers = [“Word1”, “Word2”, “Word3”] data=[] if path != None: text = fpmi.file.readFileAsString(path) line = text.split("\n") for i in range(len(line)): #This will get the words as a list of 3 strings word = line[i].split(",") # Open up the output console to see the prints print “%s %s %s” % (word[0], word[1], word[2]) # Add the list as a row of data for our table data.append(word) event.source.parent.getComponent(‘StrapTable’).data=fpmi.db.toDataSet(headers,data) [/code] I tried this out with the following csv file: `hello,there,moody 1,2,3` and it worked fine. Note that it isn’t very fault tolerant - it needs exactly 3 items in each row, but of course you can make it more fault tolerant if you need to. Also, it will read in everything as a string. If you need some columns an integers, you will need to use python’s int() casting. Hope this helps, ---
**Author:** ![Moody](https://avatars.discourse-cdn.com/v4/letter/m/3d9bf3/32.png) [@Moody](https://forum.inductiveautomation.com/u/Moody)\ **Post date:** [May 24, 2007, 7:46pm UTC](https://forum.inductiveautomation.com/t/populating-a-table-with-a-text-file/229/13 "2007-05-24T19:46:11Z")
Thank you so much! It’s working just fine now. 😃