[00:00] In this lesson, we'll discuss the use case of how to import CSV data into a database using Ignition scripting. Our approach will be, define the problem we're trying to solve, demonstrate a solution, then in the following lessons, show step-by-step how that solution is implemented. These lessons compromise a short four-part series. This lesson will define the use case and demonstrate a solution. In the next two lessons, we'll demonstrate the step-by-step creation of two utility scripting functions, then in the final lesson we'll demonstrate how to integrate those functions into a solution and tie all the pieces together. This diagram shows the overall intent of this use case. We have some application whose data can be exported in CSV format, and we'd like to import it into some database. Here we'll use Excel as a general stand-in for some CSV producing application.

[01:04] We will assume that a database and a table already exists. However, there's one problem. In a production setting, it may not be possible to directly access that database due to permissions or other security concerns. However, Ignition offers the solution. Since it is already connected to the database using a named database connection, now Ignition provides a pathway to transfer the CSV data into the database. Now we've achieved our ultimate intent, which is to use that database data from some Ignition application using some named database connection. So, the process begins with exporting application data to a CSV file. Then using Ignition, we will insert data into the database via a short script invoked by pressing a button component. The script only needs to know the table name and database connection name. Both are assumed to already exist. The actions of that short script will draw upon resources found in a project library. Two specific functions are present in the project library.

[02:09] One to prompt for the CSV file name and read its data into a PyDataset. Then a second one to consume that PyDataset data and insert it into the database. The development of both these scripts will be the subject of the next two lessons in this series. Then the last lesson will show how to tie all the pieces together into a full solution. Then once the data has been inserted into the database, Ignition can interact with it for HMI display, reporting and so forth. At this point, all Ignition needs to know is the pertinent database connection name. The solution which follows assumes the following database prerequisites. First, we need to have a database installed and configured. That database could be MSSQL, MySQL, or any other type of database Ignition supports. For this course, we'll use MSSQL. For more details on database installation, please refer to the videos found in the Inductive University course databases and ignition. The database name we'll use is test, however yours will likely differ. Next, within that database, there should be a database table with its data structure already defined.

[03:21] The database table we'll use is products. Again, yours will likely differ. Finally, we'll need to have a database connection already created in Ignition to that database. For more details on creating a database connection, once again, please refer to the videos found in the Inductive University course Databases in Ignition. We will assume a database connection name of db_csv_data, but yours will likely vary. Let's demonstrate what the solution will look like in advance of diving into the details. We have an Excel worksheet with one record and five data fields, and a simple Ignition window with an empty table. We'll start by exporting this worksheet to a CSV file by selecting File, Export, Change File Type, CSV, Save as, We'll save it over an existing file called products.csv, confirm the overwrite, and say, okay, only save the active sheet.

[04:25] Then we'll return to the Vision window and make sure that the bidirectional mode is set in the toolbar, switch over to Preview mode, and when we click on the Update DB button and select the created CSV file and open it, some underlying scripting is carried out, which reads the exported CSV data, and inserts it into the database so that it can be displayed in the Ignition table component, which is bound to that database table. Let's repeat that process with one more data record. We will save our changes with a Ctrl S. Then once more, do File, Export, Change File Type, CSV, Save As, save over the existing CSV file, acknowledge the overwrite, acknowledge the active sheet only, and once again, click the Update DB button, select the exported CSV file again.

[05:36] The Ignition table component is updated to match our most current CSV file export. So in this lesson, we've defined the use case of importing CSV data into a database using scripting, described an approach to be taken, and demonstrated a working solution for this use case. In the coming lessons, we'll take a closer look at the scripting and Ignition elements needed to implement the details of this solution.