It is currently Tue Sep 02, 2014 8:35 pm




Post new topic Reply to topic  [ 9 posts ] 
 Parsing .csv and .txt files in FPMI 
Author Message
Trooper
Trooper

Joined: Sun Dec 13, 2009 8:49 am
Posts: 29
Post Parsing .csv and .txt files in FPMI
Hi, everyone. I would like to use FPMI to open a .csv (comma delimited) or a .txt (tab delimited) file and parse each element into a text box on a FPMI window.

Imagine the simplest case: A file called Numbers.csv has thefollowing text: 1,2,3
I want the user to open the file with the "fpmi.file.openFile()" command followed by the "fpmi.file.readFileAsString(path)" command. But then I want the 1 to go into one textbox on the window, the 2 to go into another, and the 3 to go into a third textbox.

It might get a little more complicated when we have multi-line CSV files with quotation marks, embedded commas, etc. (see attached files as examples)

I also want to be able to do the same with a tab delimited file.

How should this best be done? Assume I have more than enough discrete text boxes to hold all of the entries in the file. Perhaps there's a magic parsing command in Python?

Thanks in advance,
Cas


Attachments:
File comment: This file is tab delimited.
SimpleText (Tab delim).txt [94 Bytes]
Downloaded 153 times
File comment: This file is comma delimited.
SimpleText (comma delim).csv [94 Bytes]
Downloaded 148 times
Wed Jun 30, 2010 6:56 pm
Profile
Moderator
Moderator

Joined: Sun Apr 02, 2006 2:46 pm
Posts: 1982
Location: Sacramento, CA
Post Re: Parsing .csv and .txt files in FPMI
You can use straight Python file handling, here is an example for a csv file:
Code:
file = open('c:\\file.csv', 'r')

for line in file.readlines():
   data = line.split(",")
   num1 = data[0]
   num2 = data[1]
   num3 = data[2]
   print num1, num2, num3
That will read a multi-line csv file. If you want to read just one line remove the loop and just do line = file.readlines(). The line.split will split the line on some character. If you wanted a tab instead do "\t".

_________________
Travis Cox
Inductive Automation
Technical Support Rep.


Thu Jul 01, 2010 7:27 am
Profile WWW
Trooper
Trooper

Joined: Sun Dec 13, 2009 8:49 am
Posts: 29
Post Re: Parsing .csv and .txt files in FPMI
Travis,
Thank you, this is a great start. However, how can I have the code ignore commas that are in text surrounded by quotes (like in an address line, for instance)?

Right now, the line:
"One, two","Two, three","Three, four"
gets split into:
num1 = "One
num2 = two"
num3 = "Two

where I'd like it to be:
num1 = One, two
num2 = Two, three
num3 = Three, four

Any suggestions?
Cas


Thu Jul 01, 2010 11:14 am
Profile
General
General

Joined: Tue Feb 24, 2009 1:30 pm
Posts: 891
Location: Calgary
Post Re: Parsing .csv and .txt files in FPMI
There is the python csv module http://docs.python.org/library/csv.html that was created just for this.

Not sure if/how to include it into Ignition but it should give you some ideas.


Thu Jul 01, 2010 11:39 am
Profile WWW
Moderator
Moderator

Joined: Sun Apr 02, 2006 2:46 pm
Posts: 1982
Location: Sacramento, CA
Post Re: Parsing .csv and .txt files in FPMI
The csv module in python would be great, however we don't include that module in Ignition. The csv module depends on other libraries that we don't have as well. So, if you can find a lighter weight one you can use that.

_________________
Travis Cox
Inductive Automation
Technical Support Rep.


Thu Jul 01, 2010 12:47 pm
Profile WWW
General
General

Joined: Tue Feb 24, 2009 1:30 pm
Posts: 891
Location: Calgary
Post Re: Parsing .csv and .txt files in FPMI
As a follow on, how can we include external python modules?
Or can we?


Thu Jul 01, 2010 12:53 pm
Profile WWW
Moderator
Moderator

Joined: Sun Apr 02, 2006 2:46 pm
Posts: 1982
Location: Sacramento, CA
Post Re: Parsing .csv and .txt files in FPMI
You can't right now. I can add this as a feature request for Ignition.

_________________
Travis Cox
Inductive Automation
Technical Support Rep.


Thu Jul 01, 2010 12:59 pm
Profile WWW
General
General

Joined: Tue Mar 24, 2009 9:14 am
Posts: 880
Location: Hudson, MI
Post Re: Parsing .csv and .txt files in FPMI
Please do! In IA's tpyical timeliness, you've already answered my question! :prayer: :lol:

_________________
Jordan

Duct tape is like The Force. It has a light side, a dark side, and it holds the universe together.


Fri Jul 02, 2010 11:48 am
Profile
General
General

Joined: Tue Mar 24, 2009 9:14 am
Posts: 880
Location: Hudson, MI
Post Re: Parsing .csv and .txt files in FPMI
Until it comes along did anyone look up importing a .csv file into MySQL/SQL Server?

http://www.tech-recipes.com/rx/2345/import_csv_file_directly_into_mysql/

http://www.sqlservercentral.com/Forums/Topic494460-338-1.aspx

_________________
Jordan

Duct tape is like The Force. It has a light side, a dark side, and it holds the universe together.


Fri Jul 02, 2010 12:28 pm
Profile
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 9 posts ] 


Who is online

Users browsing this forum: Google [Bot] and 1 guest


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
cron