Data upload from excel to Database

does anybody know hoe to Import data from excel to DB in Ignition 7.9.16.
Basically I am trying to bulk upload the recipes.


Which DB?

Some like MySQL, in WorkBench, you can just click a button and import. Think it’s CSV though not XLSX.

In 7.9 you will have to do some heavy scripting or use the module from Kymera systems… office module.

1 Like

Yes, I want to upload the data in MySQL or MSSQL .

Well, choose one and let us know. The method will be different.

MySQL: Save XLSX as CSV. Go into WorkBench and import to table. Don’t even need Ignition. Is this for initial setup only or for regular adding new recipes? If the latter, script it. Parse the CSV with Jython.

1 Like

Well mostly it will be MySQL,
Uploading would be mostly one time and obviously doing it from work bench is simpler but in future operator may need to upload the second set of bulk recipes and just click on button on ignition, then we must fetch the file from location ( location can be hard coded) and upload the data ignoring the duplicate ones, if added by mistake.

OK. So script it. Same script can insert to MySql or MS SQL with slight syntax change.

This is where i need help, do you know any example of such scripting ?

1 Like
1 Like

Thank you, I will give it a try :slight_smile:

You can do this in any DB I’ve used fwiw. Open csv in Excel, copy rows, paste into DB table

1 Like

I need to design this function from operator point of view where he can upload new set of data (Recipes)in bulk into database, ignoring duplicates ones.

Pseudo code:

csvData = for line in file read line
dbData = select X from DB where ABC

#insert statement
if csvData != dbData:
   system.db. insert data
1 Like

I didn’t mean copy and paste, there is a specific function in WorkBench (or maybe it’s MS SQL MS, can’t remember, even though the function is there I always script it :slight_smile: ) where you can ignore headers, and apply other rules to formatting etc