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.