Regular Data Import Strategy

Ignition
1

Hello-

We have an ERP software that does not have a great way to automatically export or communicate data. I would like to be able to be able to access ERP data from Ignition as simply as possible. I’m wondering if anyone would be willing to suggest a strategy. We have MSSQL, Ignition 7.8. The ERP can manually export a csv file- as far as I can tell, this is our only way to get data out.

My current thought is to:

  1. Manually export csv file from ERP.
  2. Save in a fixed location with a fixed file name (overwrite CSV each data dump)
  3. then with a button or on a timer from ignition, use python to reformat CSV,
  4. use ignition’s system.dataset.fromCSV() to grab this file, and import into MSSQL table.
  5. Then access data from MSSQL to use however

Does that seem like a reasonable thing to do? Or would anyone suggest a different way? Please assume there is no better way to get data out of ERP- I’ve already exhausted all options :frowning:

2

It’s pretty common to read files into Ignition via CSV, so that doesn’t appear as an unreasonable plan (although I’m all ears if someone has better suggestions)

As for #3 and #4, maybe instead of manually manipulating the data to fit fromCSV()'s format, you could use Python’s csv library, which is pretty handy and less restricted by the format of the file:

#Note: I'm assuming you're doing this from a button with the following
import csv

#file the file. Manually in this case, but you could type in the static path below
filepath = system.file.openFile("csv")

#Checking to make sure the user selected a file. Again, you could skip this if you manually type the path 
if filepath != None:
	#Open the file. Replace 'filepath' with the static directory if you don't want to do this on a button
	file = open(filepath)
	#Read the data inside the CSV
	data = csv.reader(file)
	
	#If there is a header, you can skip it with a call to next()
	#No header in the file = comment out the following line
	data.next()
	
	#Iterate through your data
	for row in data:
		#Printing now, but do something more useful here, like insert into your DB
		print row
	
	#Close the file. Technically don't have to do this, but it's a good practice. 
	file.close()
3

If you can only export to CSV, then CSV is your only option I guess. Importing it directly into Ignition is a pretty reasonable strategy. It avoids maintenance of external tools.

However, you must watch out for CSV files, as they’re not very standardised (unlike JSON and XML). F.e. there’s no standardisation on what datatypes can to be put in CSV files, when they need to be quoted, how to escape special characters, … Certainly if the CSV contains user-inputted fields, this can cause severe issues.

There’s no library that can handle all these cases, simply because there’s no standard.