[00:00] In this lesson, we'll show how to implement CSV data import into a database using Ignition scripting. We'll use the two project library scripts developed in prior lessons. Along with two Vision components and some minimal backend configuration and scripting, we'll show how to initiate the import and display of CSV data. Let's review our starting configuration and overall objectives. We have an empty Excel spreadsheet in the products tab. Here Excel is the origin for the CSV data will be importing. No data is defined yet, but the column labels represent the structure of an existing database table. We're trying to move CSV data from here into the database. As for the database, we're starting with a database connection named db_csv_data in our gateway Tt encompasses a destination table named products, with the indicated database fields as defined.

[01:07] We've said nothing about the specific type of database used. We are using MSSQL, but it could be MySQL, Oracle, or any other database type Ignition supports. Use of a database connection abstracts away this detail, and makes the solution database independent. Up in the Tools menu, if we invoke the Database Query Browser and specify the db_csv_data database connection, we see the products table. When we double click, we see the database structure corresponding to the Excel worksheet. If we execute the Select All query, we see we're starting from an empty table. Our goal is to populate this table. We have an empty Vision window, where we'll want to display the table data. Finally, our project library script file, toolbox, contains two utility functions we'll use to read the CSV data and insert it into the database.

[02:11] We'll just need to create a button script to invoke them. First, let's create a couple records of made up data. Here the first column is the primary key and must be unique, but the other columns can be any random values. Notice that the dates got auto formatted into the expected SQL date format. Let's save this data with a Ctrl-S, then set this file aside. Next, we'll need some way to display our data. In our Component Palette, we'll scroll down a bit, and drag a table component onto our window. We'll reposition it, and resize it to take up most of the window.

[03:06] Then we need to bind this table's data to our database, even though it's empty for the moment. So in the Property Editor, we'll scroll down a bit and click on its data binding. Then select the SQL Query binding type. We'll replace this default with a very basic query, SELECT * FROM products. For a more general solution, we might read the table name from some other component, but note that for security purposes, we wouldn't want this to be arbitrary user input text, but instead some list of allowable options, such as a dropdown component. But here we'll keep things very simple. Then we'll specify our database connection, set the polling mode to absolute, and keep the polling rate at 5 seconds and click OK. We are now connected to the underlying database table, so we can at least see the table field names. Next, we need a way to initiate the data read and insert. Let's use a button.

[04:09] We'll scroll back up in the palette a bit, and drag a button onto the window, under our table. We'll give it a meaningful text label, make it a bit more readable, and resize and reposition it. Next, we need to define the actions taken when this button is pressed. This is where we'll use our two project library functions. So we'll right click on the button, select Scripting, action, actionPerformed, then the Script Editor tab on the right. Let's outline the couple lines of scripting required. We'll specify a couple parameters, read a CSV file, and write its data to the database. We'll specify three parameters. For a more general solution, we would prefer to read these from various window components, but for now we'll just specify them as follows: The database table name, the database connection name, and the maximum number of records that we'll insert at one time. Again, we could take the database table name from some component, but for security purposes, it should be something like a fixed options dropdown component, and not arbitrary user input text. Next, we'll use the first of our two project library functions to prompt for a file name and read its CSV data into a PyDataset, and the second one to write the data into the database.

[05:57] Notice how it was very easy to invoke these because of the way we defined our variables and method interfaces. That's it. We're done.

[06:07] So let's save our changes. Now we're ready to test this out. First, let's export the Excel data to a CSV file, and let's maximize this window for a moment to make that easier. Then we'll click File, Export, Change File Type, CSV, Save As. We'll save it as products.csv, Click OK, Save only the current sheet. Then restore the window size and minimize it again. Now let's import that CSV file. So we'll make sure that bidirectional mode is set. Select Preview mode. Click the Update button we created, select the CSV file we saved, products.csv, and click Open. And after a moment, we see the empty table update with the CSV data we just read in.

[07:07] Furthermore, if we execute the Select query in the Database Query Browser, we also confirm the database update. Finally, we'll restore our original CSV data source, to show the entire path our CSV data has taken. So in this lesson, we've shown how to import CSV data into a database using Ignition scripting. We needed to know the destination database table and its database connection name. The database specifics are encompassed within the database connection, so these steps will be database independent. We use two scripting functions in a project library. One to read a CSV file into a PyDataset, and a second one to import that PyDataset into a database. Then we integrated everything using some simple button scripting, and displayed the data to a table with a simple query binding.