Populating a Table With a Text File

Archived Topics
1

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?

2

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.

3

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,

4

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.

5

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,

6

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?

7

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,

8

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 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.

9

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:

I get a similar error when I use runUpdateQuery instead. Any ideas?

10

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.

11

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:

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.

12

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,

13

Thank you so much! It’s working just fine now. :smiley: