# Data upload from excel to Database

**URL:** <https://forum.inductiveautomation.com/t/data-upload-from-excel-to-database/41968>\
**Category:** Ignition\
**Tags:** ignition79\
**Created:** [January 5, 2021, 9:31am UTC](https://forum.inductiveautomation.com/t/data-upload-from-excel-to-database/41968 "2021-01-05T09:31:34Z")\
**Posts on this page:** 14\
**Page:** 1

<div class="post-metadata">

**Author:** ![nnaik](https://avatars.discourse-cdn.com/v4/letter/n/53a042/32.png) [@nnaik](https://forum.inductiveautomation.com/u/nnaik)\
**Post date:** [January 5, 2021, 9:31am UTC](https://forum.inductiveautomation.com/t/data-upload-from-excel-to-database/41968/1 "2021-01-05T09:31:34Z")

</div>

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

Regards,  
Narayan

---

<div class="post-metadata">

**Author:** ![Matrix\_Engineering](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matrix_engineering/32/19781_2.png) [@Matrix\_Engineering](https://forum.inductiveautomation.com/u/Matrix_Engineering)\
**Post date:** [January 5, 2021, 9:46am UTC](https://forum.inductiveautomation.com/t/data-upload-from-excel-to-database/41968/2 "2021-01-05T09:46:47Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![nnaik](https://avatars.discourse-cdn.com/v4/letter/n/53a042/32.png) [@nnaik](https://forum.inductiveautomation.com/u/nnaik)\
**Post date:** [January 5, 2021, 9:49am UTC](https://forum.inductiveautomation.com/t/data-upload-from-excel-to-database/41968/3 "2021-01-05T09:49:34Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Matrix\_Engineering](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matrix_engineering/32/19781_2.png) [@Matrix\_Engineering](https://forum.inductiveautomation.com/u/Matrix_Engineering)\
**Post date:** [January 5, 2021, 9:54am UTC](https://forum.inductiveautomation.com/t/data-upload-from-excel-to-database/41968/4 "2021-01-05T09:54:04Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![nnaik](https://avatars.discourse-cdn.com/v4/letter/n/53a042/32.png) [@nnaik](https://forum.inductiveautomation.com/u/nnaik)\
**Post date:** [January 5, 2021, 9:59am UTC](https://forum.inductiveautomation.com/t/data-upload-from-excel-to-database/41968/5 "2021-01-05T09:59:05Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Matrix\_Engineering](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matrix_engineering/32/19781_2.png) [@Matrix\_Engineering](https://forum.inductiveautomation.com/u/Matrix_Engineering)\
**Post date:** [January 5, 2021, 10:10am UTC](https://forum.inductiveautomation.com/t/data-upload-from-excel-to-database/41968/6 "2021-01-05T10:10:34Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![nnaik](https://avatars.discourse-cdn.com/v4/letter/n/53a042/32.png) [@nnaik](https://forum.inductiveautomation.com/u/nnaik)\
**Post date:** [January 5, 2021, 10:20am UTC](https://forum.inductiveautomation.com/t/data-upload-from-excel-to-database/41968/7 "2021-01-05T10:20:53Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Matrix\_Engineering](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matrix_engineering/32/19781_2.png) [@Matrix\_Engineering](https://forum.inductiveautomation.com/u/Matrix_Engineering)\
**Post date:** [January 5, 2021, 10:41am UTC](https://forum.inductiveautomation.com/t/data-upload-from-excel-to-database/41968/8 "2021-01-05T10:41:07Z")

</div>

> [@Python script optomisation](http://forum.inductiveautomation.com/t/python-script-optomisation/11274/18):
>
> So seems to be that this line; multiRowPrepUpdate is the cause. Running it in the script console, or on a gateway event, won’t write anything to the db. It will on an action performed button in a client for some reason. I could change this to system.db.runPrepUpdate, as my script in the 1st post does execute and write to the db, but then I’d be back to my original issue once I exceed 30k entries, and also the filepath error. So I tried the store and forward suggestion, and also combining the …

---

<div class="post-metadata">

**Author:** ![Matrix\_Engineering](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matrix_engineering/32/19781_2.png) [@Matrix\_Engineering](https://forum.inductiveautomation.com/u/Matrix_Engineering)\
**Post date:** [January 5, 2021, 10:47am UTC](https://forum.inductiveautomation.com/t/data-upload-from-excel-to-database/41968/9 "2021-01-05T10:47:10Z")

</div>

> [@Importing a CSV file into a dataset, filter it by date, then into a postgresql database](http://forum.inductiveautomation.com/t/importing-a-csv-file-into-a-dataset-filter-it-by-date-then-into-a-postgresql-database/17643/5):
>
> Actually this was intriguing so I poked around and will share my results. I created a csv file with the following data: date,pressure,temp 2018-01-01 12:00:00,10,70 2018-01-08 12:00:00,12,82 2018-01-15 12:00:00,8,75 2018-01-22 12:00:00,15,80.66666667 2018-01-29 12:00:00,45,83.16666667 2018-02-03 12:00:00,16,85.66666667 2018-02-10 12:00:00,34,88.16666667 2018-02-14 12:00:00,36.71428571,90.66666667 2018-02-20 12:00:00,40.89285714,93.16666667 2018-03-01 12:00:00,45.07142857,95.66666667 2018-03-14 …

---

<div class="post-metadata">

**Author:** ![nnaik](https://avatars.discourse-cdn.com/v4/letter/n/53a042/32.png) [@nnaik](https://forum.inductiveautomation.com/u/nnaik)\
**Post date:** [January 5, 2021, 10:54am UTC](https://forum.inductiveautomation.com/t/data-upload-from-excel-to-database/41968/10 "2021-01-05T10:54:41Z")

</div>

Thank you, I will give it a try 🙂

---

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [January 5, 2021, 11:18am UTC](https://forum.inductiveautomation.com/t/data-upload-from-excel-to-database/41968/11 "2021-01-05T11:18:02Z")

</div>

> [@Matrix\_Engineering](#):
>
> Go into WorkBench and import to table

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

---

<div class="post-metadata">

**Author:** ![nnaik](https://avatars.discourse-cdn.com/v4/letter/n/53a042/32.png) [@nnaik](https://forum.inductiveautomation.com/u/nnaik)\
**Post date:** [January 6, 2021, 3:59am UTC](https://forum.inductiveautomation.com/t/data-upload-from-excel-to-database/41968/12 "2021-01-06T03:59:13Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Matrix\_Engineering](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matrix_engineering/32/19781_2.png) [@Matrix\_Engineering](https://forum.inductiveautomation.com/u/Matrix_Engineering)\
**Post date:** [January 6, 2021, 4:34am UTC](https://forum.inductiveautomation.com/t/data-upload-from-excel-to-database/41968/13 "2021-01-06T04:34:13Z")

</div>

Pseudo code:

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

#insert statement
if csvData != dbData:
   system.db. insert data
else:
   pass

```

---

<div class="post-metadata">

**Author:** ![Matrix\_Engineering](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matrix_engineering/32/19781_2.png) [@Matrix\_Engineering](https://forum.inductiveautomation.com/u/Matrix_Engineering)\
**Post date:** [January 6, 2021, 4:39am UTC](https://forum.inductiveautomation.com/t/data-upload-from-excel-to-database/41968/14 "2021-01-06T04:39:17Z")

</div>

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 🙂 ) where you can ignore headers, and apply other rules to formatting etc