Why Use

Features

Sys-Dev

 Use Cases

Companies

Documentation

Contact Us

Downloads

PDF of current page

Managing AGIS Inspection Data Importation 

KKSys includes tools to import data entered by field-officers using AGIS.
This allows large scale importation of inspection data without re-keying.
However, it is a technical process and care must be taken to ensure it is done properly.
The following document gives a procedural guide to the steps to take in this process.

Basic Steps in the process

  1. Download AGIS spreadsheets, ideally with the correct columns as needed.
  2. Prepare the sheets, by removing data which cannot be imported. This simply involves deleting  the top row from the sheet. 
  3. Save the edited sheets as CSV files into the correct Server Imports Store folders on the server computer, where they can be imported.
  4. Open KKSys and run the "Import AGIS Inspection Data" Action. This will ask you to enter the file names for the newly created CSV Files, and click "OK".
  5. Data to be reviewed will then be added to the "Raw data" import tables in KKSys. Users can run the Rapid Entry systems to display this data on the screen, undertake a review of the uploaded rows, and correct data as needed.
  6. Once you are happy with all the data on-screen, click the "Post" button on the Importation screens to import it. This will work through all the data checked in step 5., above and post it to permanent storage in the database's main "People", "Farmer", "Inspections" and "Farm-Fields" data-tables.

Notes on running VBA in Excel

Many steps in the preparation of a spreadsheet are identical. If the work has to be done many times it can be very worthwhile to create scripts to undertake repetitive work.

A brief document explaining how to do this can be found here: Programmatic manipulation of Excel Files (www.orixa.co.uk/111799)

Preparation of data from the downloaded AGIS Spreadsheets if they contain ADDITIONAL COLUMNS

Ignore the following section if the exported AGIS Excel files match your needs.

AGIS Data in XL format  

The image above shows the AGIS spreadsheet. Note that there are a very large number of columns (for all the questionnaire data gathered by field-officers). At present relatively few columns are imported into KKSys, but this may be extended in future.

  1. The AGIS data includes a KKIDNum. This is the most critical field in the importation process, as it is used to link the AGIS data to a specific farmer in the KKSys database. During the importation process it is vitally important that this is cross-checked to ensure that it matches a value in KKSys. The importation process assists with this, but cannot be fully automated. 
  2. The AGIS data includes a column "1.7 Farmer Status" which can have a small number of values. These values are used during the data importation process. An "old" farmer should already have their details included in KKSys. A "new" farmer should not. However it is possible for "old" farmers not to be present, or for "new" farmers to be present in the system. During the importation process KKSys tries to check for this, and match farmers to records in KKSys. Again, this cannot be fully automated and staff using KKSys need to cross-check data well.
  3. The AGIS data includes a GlobalID. This is used during the importation process to match farmer and farm-field data together.

 

AGIS Data sheets  

Note that the Excel file has multiple tabs. You will prepare each one, and then save it separately.

The main Inspection data is on the first sheet. The linked details of each farmer's farm(s) is on the second sheet.

Farm-Details Sheet

On the second sheet farmer farm-acreages are recorded.

  1. Second sheet shows farm-details.
  2. The farmer-name is carried over from the first sheet.
  3. One farmer can have multiple rows in this sheet, dependent on the number of farms they have

 

 

Farm-Details Sheet GlobalID  

On the farm-details sheet there is a "ParentGlobalID" column.

This column contains a "GUID" (Global Unique ID) which is the same as a GUID on the Inspections sheet.

Orixa will use these 2 UID columns to link the data when it is imported.

Deleting un-needed Columns in Excel  

The user should delete rows which are not needed, and then save each sheet.

For simple jobs with just a few spreadsheets this can be done manually.

  1. Select one or more column-headings.
  2. Right click on the heading and select "Delete"

 

 

Save as CSV Format  

Then save the edited sheet as a CSV File.

Note that there are several different CSV options. Take care to save as "CSV" not as "CSV UTF-8" or "CSV Apple Mac" etc.

VBA Scripts to automate preparation of the files with column-removal, row removal and automated saving

For KKSys literally thousands of Excel files will have to be imported. This means that it will be too slow to manually remove the columns for every importation process.

Because of this, it is suggested that a VBA script is used to automate the process.

As mentioned above, a brief document explaining how to do this can be found here: Programmatic manipulation of Excel Files

Script to delete the columns and first row and "SaveAs"

Sub DeleteColsAndSaveAsCSV()
  Dim sh As Worksheet
  'deleting should be done "backwards", from right to left
  Set wrk = ActiveWorkbook.ActiveSheet
    wrk.Columns("CJ:CK").Delete
    wrk.Columns("AC:CG").Delete
    wrk.Columns("M:Q").Delete
    wrk.Columns("H").Delete
    wrk.Columns("D").Delete
    wrk.Columns("C").Delete
    wrk.Columns("A").Delete
    wrk.Rows("1").Delete
    ActiveWorkbook.SaveAs Filename:="C:\ServerStorePath\AddFileName-Inspection.csv", FileFormat:=xlCSV
End Sub

Script to delete the first row and "SaveAs"

Sub DeleteColsAndSaveAsCSV()
  Dim wrk As Worksheet
  'deleting should be done "backwards", from right to left
  Set sh = ActiveWorkbook.ActiveSheet

    wrk.Rows("1").Delete
    ActiveWorkbook.SaveAs Filename:="C:\ServerStorePath\AddFileName-FD.csv", FileFormat:=xlCSV 
End Sub

NOTE:
Filename(s) must be changed prior to each use of the script. 

Also: It is possible to change the script to delete different columns and rows if the column-layout of the source Excel file is updated or changes.

Using the "Import AGIS Inspection and Farm Details Data" Action in KKSys

A System Procedure has been written to automate the first step of data-importation. In this step CSV file data is imported into temporary "importation" data-tables in KKSys. This allows it to be carefully checked and verified prior to full importation into the main database.

Import AGIS Inspection and Farm Details Data  
  1. On the System Entities Screen, click the "Societies" Actions.
  2. In the list that appears click on the "Import AGIS ..." item

Import Inspection / Farm Details data Procedure  

A form will open in which the name of the CSV files to be imported can be added. Note that both files should already be saved in the "Imports" store on the server.

  1. Type the file-name of the CSV file for newly imported Inspection data.
  2. Type the file-name of the CSL file for newly imported Farm-Details data

NOTE: You do not need to import BOTH Inspection and FarmDetails records every time the Import AGIS Data procedure is run. If one of the File-Names is left blank, KKSys will not try to import data for that table.