I am dealing with several different non-opc sensor systems that create a CSV text file output rather than logging to a database. I want this data to integrate with an FPMI based plant floor system. I’m wondering if I can use FSQL to connect to the text file, extract the fields I need and write a row to MySQL? If not FSQL what other method can you suggest?
Tricky question. You may be able to accomplish the task with FSQL action items. Here are some considerations:
- Are you free to make a new table to hold the data? Without a matching table schema this problem becomes significantly more challenging.
- Is the file name and path static or reproducible with simple expressions?
- Can you delete the .CSV after writing to the database? You’ll have to figure out how to prevent entering the same data multiple times.
MySQL can import the data with a query (if the schema matches) with the LOAD DATA keyword.
dev.mysql.com/doc/refman/5.1/en/load-data.html
Alternatively, you could write a script in FactoryPMI that reads your CSV and writes to MySQL. This provides more flexibility, but is a less ideal approach. It may be complicated to write and you would need a FPMI client to run the script.
Isn’t there a CSV ODBC driver that would let you bring in the CSV data into FactorySQL as if it were a database table?
In the Data Source Administrator there seems to be an odbc text driver that can be setup. Do you suggest trying to connect to the csv with odbc and then setup a data connection to that DSN in FSQL?
Yeah, thats exactly what I was thinking.
Is this different than the CSVs that we wrote the plugin for a while ago? The one that would watch a folder, grab the most recently changed .HWD file, and read the most recent values?
The ODBC CSV driver can work with FactorySQL at least for writing, though off the top of my head I’m not sure about reading. There are a variety of options, but if that plugin would work (or a modified version of it) that might be the way to go.
Regards,
The plugin works perfectly but I need to be able to modify it for a differently formated csv. Can you make the plugin code editable for me? is it Python?
The FactorySQL plugins are in .Net, that one’s in C#. You could certainly modify it if you wanted, and had visual studios. Otherwise we’d have to tweak it, or find a different way.
Does your current situation resemble the other one in terms of how the files work? That is, would you still be looking to read the most current row of the most recent file?
If you’re using MySQL, the “LOAD DATA INFILE” command is fairly flexible, maybe you could do something with that. But if the overall situation is close to what the plugin is doing, it may make sense to just make that a bit more flexible.
Regards,
After reading more about Load Data Infile I think it may be worth trying to do it that way. If you have any tips on how to get only the most recent data from the text file that would certainly be helpful.
Here’s what the text file looks like:
System Readings Taken 15:00 12/22/2008
“Zone”,“Temperature Average (°F)”,“O2 (%)”,“CO2 (%)”
“CA1”,36.8,20.7,0.2,
“CA2”,34.1,0.9,0.8,
“CA3”,34.0,0.8,0.7,
“CA4”,34.0,1.2,0.4,
“CA5”,35.1,2.5,0.6,
“CA6”,34.0,2.6,0.7,
“CA7”,35.4,2.5,0.8,
“CA8”,33.9,0.8,1.0,
“CA9”,35.0,0.8,1.0,
“CA10”,33.9,0.8,1.0,
“CA11”,33.9,1.2,1.3,
“CA12”,34.0,1.0,1.2,
“CA13”,34.0,6.1,0.8,
“CA14”,33.8,2.4,0.9,
So, where should I try to run the MySql load query, in FSQL somewhere or in MySql?
First off - is this one file that gets overwritten, or does the sensor create new files? Is data appended periodically, or is it all at once (more precisely, will you always load a full file at a time, or do you need to be able to seek)?
You could run the query in a number of places, so I’d choose the location that gives you the most flexibility in terms of triggering/selecting the file, etc.
As it is, it looks like you could do something like:
LOAD DATA INFILE 'file.txt' INTO TABLE tablename IGNORE 2 LINES (zone, temp_avg, o2_pct, co2_pct) SET load_time=CURRENT_TIMESTAMPThis would load the data into a table with the given column names, skipping the first 2 lines of the file (which are intro info) and assigning the current time to a “load_time” column, in order to record the time in which the data was loaded.
If it’s one file that gets overwritten, I’d do it in FactorySQL. If there are multiple files and you need to pick the correct one, I’d do it somewhere else- though we could probably whip up a FSQL plugin to return you the correct file name, if you really wanted to do it there.
Regards,
Colby’s got it nailed!
Just make sure the the path to the file (‘file.txt’ in his example) is as seen by the SQL database machine. It can be a mapped drive or probably even a network share.
It probably makes the most sense as an SQL query action item in FactorySQL. Get the query working properly first. Here’s how I’d get started:
- Create table with matching column names
- Copy .CSV file to a simple local path
- Run the query in MySQL Query Browser
- After that works move the .CSV to the appropriate network path
- After all works put it in an Action Item
I thought I about had it but ran into errors with my query. can you diagnose from this? The file is a mapped drive on the MySQL machine and it opens up fine if I browse it:
Executing Query. Wait …
MySQL ERROR:
File ‘X:\M3Stor_CurrentStatus.csv’ not found (Errcode: 2)
QUERY:
LOAD DATA INFILE ‘X:\M3Stor_CurrentStatus.csv’ INTO TABLE gas_m3storage
IGNORE 2 LINES (ZONE, temp_avg, o2_pct, co2_pct) SET load_time=CURRENT_TIMESTAMP
Hmm… well, one thing that comes to mind is that MySQL is running as a service, probably under the System account- which may not have access to that mapping or drive. I would recommend first trying the full address, like ‘\ipaddress\folder\M3Stor_CurrentStatus.csv’ (properly escaped, of course). If that doesn’t work off the bat, it will most likely be a security issue, so try running the mysql service as a different account which you know has access (like the one you’re logged into).
Hope that helps,
I had to use fields Terminated By comma to get each field out. It worked when getting the file on the local machine but not over the network. I guess I’ll try changing the account that MySQL runs under. What’s the best way to do that?
LOAD DATA INFILE ‘C:/M3Stor_CurrentStatus.csv’ INTO TABLE gas_m3storage
FIELDS TERMINATED BY ‘,’
IGNORE 2 LINES (ZONE, temp_avg, o2_pct, co2_pct) SET load_time=CURRENT_TIMESTAMP
15 2008-12-23 10:05:19 “CA1” 37.00000 20.80000 0.20000
16 2008-12-23 10:05:19 “CA2” 34.10000 0.90000 0.90000
17 2008-12-23 10:05:19 “CA3” 34.10000 0.60000 0.80000
18 2008-12-23 10:05:19 “CA4” 34.00000 1.20000 0.40000
19 2008-12-23 10:05:19 “CA5” 35.00000 2.60000 0.60000
20 2008-12-23 10:05:19 “CA6” 34.00000 2.50000 0.90000
21 2008-12-23 10:05:19 “CA7” 33.90000 2.50000 0.90000
22 2008-12-23 10:05:19 “CA8” 33.50000 0.70000 1.10000
23 2008-12-23 10:05:19 “CA9” 33.70000 0.80000 0.90000
24 2008-12-23 10:05:19 “CA10” 33.50000 0.70000 1.10000
25 2008-12-23 10:05:19 “CA11” 34.20000 1.20000 1.20000
26 2008-12-23 10:05:19 “CA12” 33.90000 0.90000 1.20000
27 2008-12-23 10:05:19 “CA13” 34.10000 9.60000 1.20000
28 2008-12-23 10:05:19 “CA14” 34.10000 2.40000 0.90000
Auto
When using the fully qualified path, did you still get a “file not found” error, or access denied? You shouldn’t get “not found”… so if that was the case I’d double check the path. It will ultimately be something like “\\192.168.1.1\C$\data\file.csv”.
At any rate, to change the user, go to Services under Control Panel->Administrative Tools. Find MySQL, right-click, and go to “Properties”. On the “Log On” tab you can specify the account to run under. You’ll need to restart the service for it to take affect.
But like I said, try to make sure the full path is correct first.
Regards,
can’t seem to get it to work with:
MySQL ERROR:
File ‘\5.132.105.116\SystemStatus(CSV)\M3Stor_CurrentStatus.csv’ not found (Errcode: 13)
QUERY:
LOAD DATA INFILE ‘\\5.132.105.116\SystemStatus(CSV)\M3Stor_CurrentStatus.csv’ INTO TABLE gas_m3storage
FIELDS TERMINATED BY ‘,’
IGNORE 2 LINES (ZONE, temp_avg, o2_pct, co2_pct) SET load_time=CURRENT_TIMESTAMP
Yep! I changed the account that MySQL service runs under from the service account to the local administrator account and the query worked great. I think this is probably the best way to deal with text file data logging. So, now I’m getting data from a text file on a machine accross the state and bringing it into my FPMI/MySQL system running in a datacenter. Pretty cool. Thanks for your help guys! -Tony
LOAD DATA INFILE ‘\\5.132.105.116\SystemStatus(CSV)\M3Stor_CurrentStatus.csv’ INTO TABLE gas_m3storage
FIELDS TERMINATED BY ‘,’
IGNORE 2 LINES (ZONE, temp_avg, o2_pct, co2_pct) SET load_time=CURRENT_TIMESTAMP