How to View the most recent Connect Job Data Extract JSON Files

Overview

Data Extracts are created by the Connect Job and end with a .JSON file type.

They are used by the SIS Integration to import data from an organization's SIS system into SS&E based on the data extract definitons, aka logic found in the client system Informer/CSV files or Database Queries/Views and the SS&E Accelerate code.

• To identify which data feed is importing each SIS data point, use the SIS Import Data Definitions document found here.
• To learn more about the SIS Data Import process and infrastructure, click here.
• By default, the Connect job runs four times a day, every six hours, as described in the above article.
• The contents of the data extract (.Json) files depend on the source data and what is configured in the data extract definitions.
  • • If the data extract contents are missing the expected data and changes are neccessary, the changes must be made in the source data and/or the data extract definitions so that the next time the Connect Job runs it will generate a data extract .Json file with the expected data.
• Every time the Connect job completes:
  1. The Connect2 Application Log is updated and available to view from SS&E Administration > Advanced Administration > External Logging, found here.
  2. The most recent data extract used to import data into SS&E is available to view from SS&E Administration > SIS Integration Administration.

How to View the Data Extract

For each data feed, the most recent data extract used to import data into SS&E can be viewed from SS&E Administration > SIS Integration > Manual API Calls page or Datafeeds Administration.

• SS&E Administrators can use the Manual API Calls page to investigate the Connect job. Before using Manual API Calls, review the available options and warnings from here.
• The Json Detail Manual API Call opens the most recent data extract .Json file in the browser tab.
• If the data extract is too large, this process can freeze or be very slow, in which case we recommend downloading the data extract to your local computer. For instructions, see below or click here.

Where are the Data Extracts Located

By design, data extracts are created by the Connect Job and uploaded to the cloud (Amazon AWS S3). The data extract contents represent the SIS data extract definition results for each data feed imported into SS&E.

• Each data extract .Json file includes the results of the data extracted from the SIS/source data and processed by the Connect job.
• As long as the data extract definitions and connect job are processing the correct data without any issues, the data extract contents will include the expected data.
• For example, if data feeds are out of date, this indicates that the data feeds on S3 are getting stale and new files are not getting created. For more information about this, click here.

How to Download a Data Extract

By default, downloading a data extract will display the contents in one long string which can be hard to view and understand.

The following instructions explain how to find/download/view the most recent data extract as individual records for convenience/ease of use.

1. Browse to SS&E Admin > SIS Integration > Datafeeds, found here, then scroll to the relevant data feed.

2. To download the JSON file, right-click on Download Extract in the Download Extract column and select "Save link as...".

• Enter a file name for the saved JSON file. (something short and easy is helpful, such as renaming Transcriptcourse(3) to tc3.)
• Note where the file is being saved (usually this is set to the local PC's Downloads folder by default).

3. Once the JSON file is downloaded, open Windows PowerShell from the Start menu or use instructions for Excel found below.

• At the command prompt, switch to the directory from step 2. For example, enter cd Downloads.

• Use the following text to reformat the JSON file from one long record into individual records:

  1. Edit the command by replacing the FileName with the file name from step 2.
  2. Copy the command into Windows Powershell. Select "end", then "enter" to run the command.
  3. Wait for a new command prompt to appear. This confirms that the command ran successfully. 

  Windows: ((Get-Content -path FileName.json -Raw) -replace "},","},`n") | Set-Content -Path FileName.json

  OS X/Linux: sed -i 's/},/},\n/g' fileName.json

4. Open the JSON file in a Text Editor, such as Notepad++, to view the reformatted data extract.

5. Another alternative to Windows Powershell is to view the data extract files in Microsoft Excel on a PC/mac using MS Excel's Power Query with the following instructions*.

How to view individual Data Extract records in MS Excel

1. From SIS Integration -> Datafeeds Administration, right-click “Download Extract” on the JSON data you wish to view. Select “Save Link As”.

2. Choose the destination where the file should be saved.

3. Open a blank workbook in MS Excel.

4. Select the data tab, and choose Get Data>From File>From JSON.

5. Select the destination of the downloaded JSON file from step 2.

6. Select Import. This will open MS Excel’s Power Query.

7. Select “To Table”

8. Select Okay.
9. After a few moments (depending on the file size), select the column expansion button, select “OK”.

10. Your data will now appear in the Query Editor. Select “Close & Load”.

11. Your data extract will now appear as a Microsoft Excel table.

*Thank you to Jonathan Tyndall from Carteret Community College for sharing these detailed steps!