Importing CSV data to AppGini 5.90+ apps

We've added a completely rewritten CSV import tool to AppGini 5.90. Any user can now use it to import CSV data to tables (as long as they are granted import permission, and have insert permission to the table).

00:01 Hello and welcome everyone. In this screencast, we  are going to see the new import CSV data feature  

00:08 added in AppGini 5.90. Importing CSV data has  been possible since very long ago in AppGini.

00:15 The change in this release is that it's now  available to all users not just to the admin user.

00:20 And beside this, we have made a tremendous amount  of improvement in this release. So I've prepared  

00:27 a demo of the northwind application. And this one  has no records for orders, but has records for all  

00:34 other tables: customers, employees, suppliers, ... etc  all have data. But the orders table has no data.  

00:41 So we are going to import a CSV file of orders  data into that table and see how things go.

00:47 I'm not going to import it as admin.  I'm going to import it as another user. 

00:51 But before I'm able to import data as  a non-admin user i have to enable this  

00:56 feature for non-admin users first. I can do this from the admin area.

01:02 I can go to Groups and I'll  select the salespeople group.

01:10 And i have to check this option "Allow importing  CSV files" otherwise if i don't check this option  

01:16 that group will not be able to import CSV data into the database so i have to check it.

01:22 And before saving changes, let's have a look on the  permissions of this group: sales group can insert  

01:29 data into customers, but not to employees and it  can insert data to orders and order items but not  

01:36 to other tables. So this group can insert data only  to three tables: customers orders and order items  

01:44 but not to other tables. I'll save the changes  i made which are allowing importing CSV files.  

01:53 And then I'll sign out and sign in as a user of  the sales people group. Sign in. When I'm signed in  

02:02 as john doe, who is a member of the salespeople  group, I can see the import CSV data button.

02:08 If i didn't enable this feature for this group,  this button will not be visible to that user.

02:14 The next step is to import data into the orders  table. I'll click the button import CSV data. It's  

02:19 accessible from any page not just the home page.  And here we have a page where we can upload our  

02:26 CSV file so I'll click on browse and then i  select the orders CSV file and then uploading  

02:35 should start automatically in a few seconds. But  I can just start uploading right now if i want to  

02:41 skip the wait. Now the reason for this wait is in  case I chose the wrong file I can just cancel and  

02:47 choose another file. Before importing, we are seeing  a preview of the CSV data before inserting it into  

02:54 the orders table. The orders table has automatically  been detected and this is because the CSV file - if  

03:00 we have a look in excel - the CSV file does  include the field names in the top row, and  

03:08 this is how AppGini was able to detect that the  best match for this CSV file is the orders table.

03:16 If I don't like that automatic  detection I can just open that menu,

03:20 and here i can see all other tables I have  insert access to. So let's say that this is  

03:26 a CSV file that should belong to the customers  table so i can select the customers table and  

03:31 then i can start manually matching each column  to the corresponding field name in customers.

03:41 Now if I don't like this selection i can just  revert back to the automatically detected  

03:45 table which is orders. Here I can also change some  options of the CSV file.. For example I can change  

03:51 the field separator from comma to tab in case this  is a tab separated file. I can just type "tab" here  

03:58 and I can see the change applied in the preview. Now this is a comma separated file actually, so tab  

04:05 doesn't work as you can see and I can just revert my selection to comma.

04:11 Now if I'm happy with the preview I'm seeing  right now, I can proceed to importing CSV data.

04:16 So i click that button and now I can  either confirm or cancel. I'll confirm  

04:24 and then the importing process starts. We can see here that we have 834  

04:30 records in the csv file and we can see how  many records are being added to the database.

04:37 And you can see a detailed breakdown: how many  records are added, how many records are updated.

04:43 And we can see that the estimated  time ahead is a few seconds right now.

04:49 We're done and we can now either import another  csv file or go to the orders table and preview  

04:56 our newly inserted records. So i'll do this.

05:01 And here are our 834 records we've  just imported from the csv file.  

05:09 One intriguing behavior taking place right  now is that the total field is being emptied.  

05:16 There was some values in here  and they have changed to empty  

05:20 cells. Why is this happening? This is because  the total field is a "calculated field"  

05:27 and it's being calculated by summing the  order items for each order and displaying  

05:34 their total in here. However, the order items  table is empty so if we click on any order

05:43 we can see that there are no order items. "No matches found". And this is because the  

05:49 order items table is still empty. What we  need to do is import data into our order  

05:54 items table in order for the totals to get  calculated correctly in the orders table.

06:02 So this is what we are going  to do right now. Import csv data.

06:09 And I'll select the order details  csv file and I'll start uploading.

06:18 And here is a preview of the data in the  csv file and the order details table has  

06:24 automatically been detected correctly. And this is  again because this csv file contains field names  

06:32 in the top row, and this is how AppGini was  able to match it to the order details table.

06:37 The preview looks fine so i can  go ahead and start importing.

06:43 And now we can see a progress of importing process  we can see that we have 2180 records in this csv  

06:51 file and we have a breakdown of how many records  are inserted and how many ones are updated. And  

06:58 we have an estimated time ahead of about a minute.  One interesting feature that we've added in this  

07:04 release is the ability to pause and resume the  importing job. So i can pause the job in here  

07:10 and once it's paused, no other data are being  imported right now. I can resume it at any  

07:17 time of course. And one other interesting  feature is the "Show/hide import logs"  

07:23 If i click that button i can see a detailed  log of all the imported records. So i can see  

07:31 the position in the csv file, and the  action: whether I'm inserting or updating.  

07:37 And the id of the imported record, and whether  ownership data for this record has been added  

07:44 or not. So this is quite handy for debugging  in case anything goes wrong during importing.

07:53 One other change in here is  that now i have a "permalink".  

07:57 What this permanent does is that if i copy  this link and then, if the importing  

08:02 process goes wrong for any reason, i can just  use this link to resume importing anytime later.  

08:08 So if i go to the address bar and paste that  link i can just resume from where i left.

08:20 All right, importing has finished now and i can  go to the order details table to see the newly  

08:27 imported records. Here they are. And if i go to  the orders table i can see that the total row  

08:33 should update correctly now. As you can see  the total column is updating correctly now.  

08:41 And this is how the import csv feature  is working in our new version of AppGini.

08:48 I hope you like the improvements made in  this feature and thank you for watching.