Page Comparison

...

When custom Powershell file generation scripts are properly developed, reviewed, and placed by a trusted system administrator, they will become available for selection within the Data Import Agent definition screen. When defining your data-processing Agent, you must select the "File System / Powershell" Agent Type. You can then select which custom script you'd like to use to generate the CSV file. Later, when the Agent runs the custom file generation Powershell script, the Transform/Load process will reference the output CSV file for loading into the ODS, and only then will it be transformed by your Data Map to be POSTed to the ODS. You must also remember to set a schedule within the agent, similarly to how you would set it up for FTP agents.

Instructions for System Administrators

...

Scripts need to be written in the Powershell programming language. The first line must be exactly the following:

param ($row)

This means that the script will receive each original CSV row into this script's $row variable. The rest of the script can be any valid Powershell code. The goal of the script is to inspect and possibly-modify that $row variable. Naturally there are 2 fundamental things you may need to do:

• Inspect the original value of a named column within that row.
• Alter the original value of a named column within that row.

In either case, we refer to the column by name:

$row.'Column 1' = 'New Value'  # This line completely replaces the value of the column named 'Column 1' with the brand new value 'New Value'.
$row.'Column 2' = $row.'Column 2' + '!'  # This line modifies the value of the column named 'Column 2' by adding an exclamation point to the end of the original value.

...

can take in input data from any source, but it is up to the script developer to read in that data, manipulate the data, and then save that data in an output CSV file. The only requirement is that the script returns the full path of that output file.

Representative Example: Cleaning Up Extra Character from Column

In this example, we'll experience a realistic obstacle involving a CSV file in need of row-by-row file cleanup. We 'll first attempt to use Data Import to bring in the file as-is, experiencing failures due to invalid raw data from the CSVhave a CSV file that contains an extra character for all elements in the "sasid" column and we know the ODS will reject that value in that format. We'll account for those problems this problem by introducing a custom script that cleans up each row during processingthe entire file before performing any mappings, ultimately resulting in the successful loading of the ODS.

Consider a CSV file from a third-party system which contains Student Assessment data. In this case, the third-party system produces data with excessive white space in two an extra pound sign in one of the columns we are interested in (sasid and Overallss_adj):

adminyear,DistrictNumber,DistrictName,SchoolNumber,SchoolName,sasid,"listen,listeningssingss_adj",speakingss_adj,readingss_adj,writingss_adj,comprehensionss_adj,oralss_adj,literacyss_adj,Overallss_adj
2018,255901,Grand Bend ISD,255901107,Grand Bend Elementary School, 604825 #604825,"333,444",349,270,246,289,341,258, 283 
2018,255901,Grand Bend ISD,255901107,Grand Bend Elementary School,  604826  #604826,303,392,100,100,161,348,100,  174  
2018,255901,Grand Bend ISD,255901107,Grand Bend Elementary School,   604835   #604835,363,230,152,202,215,297,177,   213   
2018,255901,Grand Bend ISD,255901107,Grand Bend Elementary School,    604864    #604864,294,262,251,263,264,278,257,    263    
2018,255901,Grand Bend ISD,255901107,Grand Bend Elementary School,     604870     #604870,209,237,269,277,251,223,273,     258     
2018,255901,Grand Bend ISD,255901107,Grand Bend Elementary School,      604888      #604888,270,237,296,251,288,254,274,      268      
2018,255901,Grand Bend ISD,255901107,Grand Bend Elementary School,       604890       #604890,270,262,289,242,283,266,266,       266       
2018,255901,Grand Bend ISD,255901107,Grand Bend Elementary School,        604904        #604904,934,948,932,926,933,941,929,        933        
2018,255901,Grand Bend ISD,255901107,Grand Bend Elementary School,         604902         #604902,939,939,928,930,931,939,929,         932         
2018,255901,Grand Bend ISD,255901107,Grand Bend Elementary School,          604876          #604876,938,925,929,916,932,932,923,          925          

Similar to the Quick Start, the user sets up any necessary Bootstrap items, a Data Map for mapping these CSV columns to ODS Student Assessements, and an Agent. Because of the invalid excess spaces in the sasid column, though, the first attempt to load the ODS rightly fails. In the Logs \ Ingestion screen, we see a detailed description of the failures like so:

...

Here, we see that the ODS rejects this first attempt, because the excess spaces resulted in invalid studentUniqueId values. Surely the extra spaces in the score values would also become problematic after correcting the studentUniqueId values. We'll need to intervene with both of these problematic columns before we could expect the ODS to accept them.

A custom script to "trim" these excess spaces pound signs is fairly straightforward:

$inputCsvFile = "C:\Temp\FileGenerationWorkingFolder\StudentAssessmentsWithExtraPoundSigns.csv"
$outputCsvFile = "C:\Temp\FileGenerationWorkingFolder\StudentAssessmentsFixed.csv"

function TransformCsv($inputCsvFile, $outputCsvFile, $alterRow) {
    Import-Csv $inputCsvFile | ForEach-Object { Invoke-Command $alterRow -ArgumentList $_ } | Export-Csv $outputCsvFile -NoTypeInformation
}

TransformCsv $inputCsvFile $outputCsvFile {
    param ($row)

    $row.'sasid' = $row.'sasid'.Trim()
$row.'Overallss_adj' = $row.'Overallss_adj'.Trim()

...

Replace("#", "")

    # Output the modified row.
    return $row
}

return $outputCsvFile

In short, there is a raw file at an arbitrary location that we need to clean up. We read in that file, remove the pound signs from the "sasid" column, and save it in a new file at any arbitrary location. The file path of the new file is then returned. 

After placing this Powershell file within the Share as described above, and updating our Agent to use this script , we retry the file by using the "File System / Powershell" Agent Type, we run Transform/Load and encounter success. The Logs \ Ingestion screen shows that cleaned-up values were POSTed to the ODS:

...