CSV import

Here is a tonne of code that we made to import CSV to knack on the front end. Scenario:

We run "projects" and each has a set of "budget lines", which come from other software in CSV format. Each budget line has a 3 letter code and also an associated 3 letter donor code. Projects are connected to budget lines. Budget lines are connected to donors.

This runs on a "details" view where the project code is displayed at the top. All "budget lines" are being imported to this project. The budget lines have one donor each but budget lines under the same project could have different donors.

This code does the following:

1) Look up the project record (to get the project record ID value and thus connection field)

2) Checks for existing budget lines

3) Looks up some global "scenario limits" in another object. Depending on the USD value of each budget line, the row is assigned a "default scenario".

4) Adds a file upload box ("Choose file"). Checks the selected file is CSV format.

5) Displays the CSV file in a table and with custom headers at the top (these are hard coded for now). This forces the user to check the columns are in the correct order (they should be as coming from same software always).

6) Checks for any new "donors" in the CSV file and prompts user to create the corresponding records. Finds existing "donors" and takes note of the relevant donor record IDs

7) Once all donor codes have been checked, gives the user a button "click to continue" which starts the import

8) Loops through the CSV file and creates new records for each row of the file, including determining the correct default "scenario" based on the line value.

// ===========================================================================
// ===========================================================================
// CSV import
// ===========================================================================
// ===========================================================================

$(document).on('knack-scene-render.scene_XXX', function(event, view, record) {

//Import progress window functions - a super super ugly status display window
$("<table id='statusOverlay'><tbody><tr><td><b> Import progress Window </b></td></tr></tbody></table>").css({
"position": "fixed",
"top": "20%",
"left": "60%",
"width": "40%",
"height": "90%",
"background-color": "rgba(255,255,0,.7)",
"z-index": 10000,
"vertical-align": "top",
"text-align": "right",
"color": "#000",
"font-size": "10px",
}).appendTo("#kn-scene_344");


function updateStatusInfo(myText){ //a function that updates the table display thing above
$("#statusOverlay > tbody > tr > td").append("<br />"+myText);
};

//In my example, I had to link all imported records to the currently open "Projects" details page:
var filetext
var knackProjectID //the knack record identifier for this page's project

//this is a "details" view from which I am extracting the unique front-facing project code, in order to look it up and get the knack back-end record ID
var projectCode = $("#view_831 > div.kn-details-column.first > div > div > table > tbody > tr.field_70 > td > span").text()

console.log(projectCode);

//All of our projects have different donors too. Budgetlines are connected to a project and to a donor. Projects and budgetlines are not connected directly.
//However, I can still make a details view to show all the donors associated with all the budget lines associated with the projects.
//I had to be sure the user didn't try to import a new donor, so this variable will help keep track of that:
var listOfDonors = [[],[]];
var currentProjectCurrencyID = ""
var x = {} //global counter
var existingBudgetLines //all existing budget lines
var scenarios
var initialCurrencyConversionRate //divide by this value to convert from donor currency to USD
var PPcategories

updateStatusInfo("Looking up project record");

// api to get the project record ID from the project code
var api_url = 'https://api.knack.com/v1/scenes/scene_186/views/view_889/records' //Projects table
userToken = Knack.getUserToken()
// preparing filters
var filters = [
{
field: 'field_70',
operator: 'is',
value: projectCode //find this Project Code record
}
]

// add to URL
api_url += '?filters=' + encodeURIComponent(JSON.stringify(filters));

Knack.showSpinner();
$.ajax({
url: api_url
, type: 'GET'
, headers: {
'X-Knack-Application-Id': '56fb8b0fad25a3be79b5054a'
, "X-Knack-REST-API-KEY": 'knack'
, "Authorization": userToken //userToken is a public variable I set on each page load
}
, success: function(data) { //once you successflly get the record
console.log(data)
if (data.records.length != 1) {
alert("Fatal error! Searched for project code " + projectCode + "which should be unique (1 result). Actually got " + data.length + "results. Is there a duplicate project code?!")
} else {
knackProjectID = data.records["0"].id
currentProjectCurrencyID = data.records["0"].field_922_raw["0"].id //Projects can be in different currencies, so are connected to currencies object.
console.log("Current project currency ID:") //the API call returns the connection ID (not field text) of the currencies.
console.log(currentProjectCurrencyID);
initialCurrencyConversionRate = data.records["0"].field_932_raw; //I have to convert these rates to USD as well, though
console.log("initial Currency Conversion Rate:")
console.log(initialCurrencyConversionRate)
if (initialCurrencyConversionRate == "" || initialCurrencyConversionRate == 0){ //if not entered, assume 1
updateStatusInfo("<b> No initial currency conversion rate set. Assuming donor contract currency is USD. </b>");
initialCurrencyConversionRate = 1
alert("No initial currency conversion rate has been set for this project. To work out default procurement scenarios, import will assume donor contract currency is USD. If this is incorrect, please first go back to the previous page and specify the donor contract currency.")
}
console.log("Knack Project ID:")
console.log(knackProjectID);
findExistingBudgetLines();
updateStatusInfo("Retrieved project record.");
}

}
, error: function (request, status, error) {
handleAjaxError(request, status, error);
}
});



function findExistingBudgetLines(){

var budgetLinesFromDetailsView = $("#view_831 > div.kn-details-column.first > div > div > table > tbody > tr.field_82 > td > span").text() //this is a details view showing all budget lines
existingBudgetLines = budgetLinesFromDetailsView.split(',').map(function(item) { //function take off whitespace at start/end and split by , character
return item.trim();
});
console.log("Existing budget lines:")
console.log(existingBudgetLines);

if (existingBudgetLines.length > 1) { // a few friendly notifications
updateStatusInfo("Some budget lines have already been entered for this project. These will be skipped during the import process.")
} else if (existingBudgetLines.length == 1 && existingBudgetLines[0] != "ACT") {
updateStatusInfo("There is already a budget line added to this project (other than the default 'ACT' line). Please ensure the CSV file you want to upload does not have duplicates of lines already on this system.")
} else {
updateStatusInfo("Done.");

}
updateStatusInfo("Looking up Procurement Scenario Limits"); //we have to set a "default procurement scenario" field based on the value of the line, in USD
lookupProcurementScenarioLimits();
}

function lookupProcurementScenarioLimits(){ //looks up procurement scenario limits. e.g < 200USD = scenario "A" etc
// api
var api_url = 'https://api.knack.com/v1/scenes/scene_186/views/view_987/records?sort_field=field_899&sort_order=asc' //Procurement Limits table
userToken = Knack.getUserToken();
Knack.showSpinner();
$.ajax({
url: api_url
, type: 'GET'
, headers: {
'X-Knack-Application-Id': '56fb8b0fad25a3be79b5054a'
, "X-Knack-REST-API-KEY": 'knack'
, "Authorization": userToken
}
, success: function(data) { //once you successflly get the record
scenarios = data //set variable
console.log("Scenarios:")
console.log(scenarios);
addFileBox();
}
, error: function (request, status, error) {
handleAjaxError(request, status, error);
}
});
}



function addFileBox(){

$('#view_831').append('<br><p> Please select a CSV file for import: </p>');
$('#view_831').append('<input type="file" id="myFileBox" name="file" enctype="multipart/form-data" />'); //add an input box
Knack.hideSpinner()

document.getElementById('myFileBox').addEventListener('change', function(e) { //once a file has been selected, load it up
console.log("Called fileChanged function");
var file = myFileBox.files[0];
console.log(file);
var textType = /.+(\.csv)$/ ;
console.log(file.type)
$("#myImportButton").remove();

if (file.name.match(textType)) { //check for .csv file extension
var reader = new FileReader();


reader.readAsText(file); //load/read this as a text file
reader.onload = function(e) { //once loaded, do this
fileText = reader.result;
processData(fileText);
console.log("Currency conversion rate as of addFileBox function:")
console.log(initialCurrencyConversionRate)
}

} else {
alert("File not supported! Only files with a .csv extension can be uploaded here.")
}
});
};
// Here I display the CSV file to the user but with my own "custom" headings, to make sure everything is "matching up". Then I prompt the user to continue.
function processData(fileText) {

Knack.showSpinner();
$('#view_831 > table.table-data-omset').remove() //this gets rid of the display table, in the case that the user changed the file.
var allTextLines = fileText.split(/\r\n|\n/); //split the CSV file in to rows
var headers = allTextLines[0].split(','); //split again by comma
//NOTE: THIS WORKS FOR LEGACY CSV FILES WHERE THE COMMAS IS REALLY THE DELIMITER
//EXCEL CSV FILES TAKE A DIFFERENT FORMAT, each row as follows:
//"Field1","Field2","Field3"
//Modify the above code to suit. I was importing from a program which did just strictly delimit by comma only.

console.log("Headers:")
console.log(headers)

var lines = [];

for (var i=1; i<allTextLines.length; i++) { //FOR EACH ROW
var data = allTextLines[i].split(','); //data = an individual row
console.log("Data row extracted:")
console.log(data);
if (data.length == headers.length) { //If the number of fields extracted was equal to the number of header fields (OK)
var tarr = [];
for (var j=0; j<headers.length; j++) { //FOR EACH COLUMN
tarr.push(data[j]); //Add column to second dimension of data array data[j,0], data[j,1] etc...
}
lines.push(tarr); //then push that row on to the lines object
} else { //otherwise, there was a comma within the data itself - show a warning and skip
if (data.length > 1) {
var number = i +1;
alert("Please check row " + number + " carefully and ensure there is no comma (,) present in any of the CSV cells. Otherwise this row will not be imported.")
};
}
}
console.log(lines);
console.log(lines.length);
console.log(lines[0].length);


$('#view_831').append('<table class="table table-striped table-bordered table-data-omset"><tbody></tbody></table>') //draw a table

var tableOmset = $('table.table-data-omset');

var tbodyTableOmset = tableOmset.find('tbody');

var trTableOmset = '';

//add custom headers

trTableOmset += '<tr>' ;
trTableOmset += '<td><h4>'+ " x " +'</h4></td>';

for(var b=0; b<headers.length; b++){

if (b==0) {
trTableOmset += '<td><h4>'+ "Custom header 1" +'</h4></td>';
}

else if (b==2){
trTableOmset += '<td><h4>'+ "DONOR CODE" +'</h4></td>';
}

else if (b==3){
trTableOmset += '<td><h4>'+ "BUDGET LINE (LAST THREE LETTERS)" +'</h4></td>';
}

else if (b==6){
trTableOmset += '<td><h4>'+ "LINE DESCRIPTION" +'</h4></td>';
}

else if (b==10){
trTableOmset += '<td><h4>'+ "VALUE (donor currency)" +'</h4></td>';
}
else {
trTableOmset += '<td> x </td>';
}

}

trTableOmset += '</tr>' ;

// add headers from CSV

trTableOmset += '<tr>' ;
trTableOmset += '<td><b>'+ "1" +'</b></td>'; //number first column

for(var b=0; b<headers.length; b++){ //add headers from CSV file



if(headers[b] === null || headers[b].length > 40){
headers[b]= 'x';
}

trTableOmset += '<td><b>'+ headers[b] +'</b></td>';
}

trTableOmset += '</tr>' ;



//add data, row by row
for(var a=0; a<lines.length; a++){ //for each line of CSV
if (lines[a][3]!=""){ //Some of my CSV lines were "header" information and not actual data to import. In that case, column [3] was blank
// so, if blank, do this:
currentRow = a + 2 ; //row number (a indexes from 0, excel indexes rows from 1 and has a header)
trTableOmset += '<tr>' ;
trTableOmset += '<td><b>'+ currentRow +'</b></td>'; //label the row
for(var b=0; b<lines[0].length; b++){ // for each row

if(lines[a][b] === null || lines[a][b].length > 80){ //Some of my fields had very long text that I didn't need, so I replaced with X
lines[a][b]= 'x';
}

trTableOmset += '<td>'+ lines[a][b] +'</td>'; //print
}
trTableOmset += '</tr>' ;
} else { //if a[3] was just blank
currentRow = a + 2 ; //corresponding row of the excel spreadsheet
trTableOmset += '<tr>' ;
trTableOmset += '<td><b>'+ currentRow +'</b></td>';
trTableOmset += '<td> Row in CSV is header </td>'; //just add a message in that first cell
for(var b=1; b<lines[0].length; b++){

lines[a][b]= '-'; //and a "-" character for the rest of the row

trTableOmset += '<td>'+ lines[a][b] +'</td>';
}


}
}
tbodyTableOmset.append(trTableOmset);

var donorField = 2 //One of the columns was my "donor" field. I need to check all donors in the CSV already exist, before importing.

checkDonorCodesExist(lines, donorField)

};

function checkDonorCodesExist(lines, b) {
Knack.showSpinner();
var thisDonor;
x = 0
for (var a = 0; a<lines.length; a++){ //for each line
thisDonor = lines[a][b] //this is the donor (b is column id passed to this function, not a counter now)

if ($.inArray(thisDonor, listOfDonors[0],0) == -1 && thisDonor != "-"){ //if not already in list
listOfDonors[0].push(thisDonor) // add to list
x++ //increment
}

}
console.log("List of donors:")
console.log(listOfDonors)
console.log(x)

$('#view_831').append('<br> <input type="button" value="Looks good, add these lines to project" id="myImportButton"/>') //add button
$('#myImportButton').hide(); //hide it

for (a = 0; a<listOfDonors[0].length; a++) { //for each donor in list

checkIfInList(listOfDonors[0][a], a) //check if the donor is in the list... and add if not.

}


Knack.hideSpinner(); //hide the "loading" spinny thing




function checkIfInList(donorCode, a){
var A = {} //change A to object, so I can access this inside my AJAX
A = a
var donorCODE = {}; //use object to endure reference inside AJAX
donorCODE = donorCode;
// api
var api_url = 'https://api.knack.com/v1/scenes/scene_186/views/view_988/records' //Donor List

// preparing filters
var filters = [
{
field: 'field_142',
operator: 'is',
value: donorCode //search for this donor code
}
]
console.log("Checking for donor code " + donorCode)
// add to URL
api_url += '?filters=' + encodeURIComponent(JSON.stringify(filters));

$.ajax({
url: api_url
, type: "GET"
, headers: {
'X-Knack-Application-Id': '56fb8b0fad25a3be79b5054a'
, "X-Knack-REST-API-KEY": 'knack'
, "Authorization": userToken
}
, success: function(data) {
console.log("Successful ajax call for donor code. Returned:")
console.log(data)
if (data.records.length == 0) { //if doesn't already exist
var donorName = prompt("Donor code " + donorCODE + " is not in the database. Press OK to add now. Optionally, please enter the name of the donor here ('UNHCR', 'GIZ', etc).")
if (donorName != null){ //if user didn't click cancel
addDonorCode(donorName, donorCODE, A);
Knack.showSpinner();
}
} else {
listOfDonors[1][a] = data.records["0"].id //note the record ID of this donor
console.log("List of donors:")
console.log(listOfDonors);
x--
showButton(listOfDonors[0][a])

}
}
, error: function (request, status, error) {
handleAjaxError(request, status, error); //custom error function
}
})
}


}

function addDonorCode(donorName, donorCode,A) {
var api_url = 'https://api.knack.com/v1/scenes/scene_391/views/view_989/records' //Donor create form
userToken = Knack.getUserToken();
var dataToPost = ""
dataToPost = {
field_142: donorCode,
field_641: donorName
};

$.ajax({
url: api_url
, type: "POST"
, headers: {
'X-Knack-Application-Id': '56fb8b0fad25a3be79b5054a'
, "X-Knack-REST-API-KEY": 'knack'
, "Authorization": userToken
}
, data: dataToPost
, success: function(data) {
console.log("Successful ajax post for creating donor. Returned:")
console.log(data)
x-- //increment x
showButton(data.record.field_142);
listOfDonors[1][A] = data.record.id //record the record ID from this connected donor object (in position [1,x])
Knack.hideSpinner();
}
, error: function (request, status, error) {
handleAjaxError(request, status, error);
Knack.hideSpinner();
}
})


};

function showButton(donorCode){
if (x==0){ //if all donors have been imported/dealt with (x is global var somewhere)
$("#myImportButton").show()
updateStatusInfo("Checking for donor " + donorCode + ".")
updateStatusInfo("Done checking donors, click button to continue.")
} else {
updateStatusInfo("Checking for " + donorCode + ".")
}
}

// ==========================================
// Main import code
// ==========================================

$("#view_831").on('click',"#myImportButton",function(){
console.log("Starting import")
Knack.showSpinner();
updateStatusInfo("Starting import");

var allTextLines = fileText.split(/\r\n|\n/); //split again etc
var headers = allTextLines[0].split(',');
var lines = [];

for (var i=1; i<allTextLines.length; i++) {
var data = allTextLines[i].split(','); //data = an individual row
console.log("Data row extracted:")

var tarr = [];
for (var j=0; j<headers.length; j++) {
tarr.push(data[j]);
}
lines.push(tarr);
}

addBudgetLine1(lines, 0);



//add data

function addBudgetLine1(lines, a){
console.log("Called addBudgetLine1 function. a = " + a)
console.log ("Lines.length = " + lines.length)
console.log(lines)
if (a < lines.length){ //if not finished yet
console.log("BL is:")
console.log(lines[a][3]);
console.log("Line length:")
console.log(lines[a].length)
if (lines[a][3]!="" && typeof lines[a][3] != 'undefined'){ //if budgetline code is not blank and line is not blank
var message = "Importing line " + lines[a][3]
updateStatusInfo(message) //update my box thing
var indexOfDonorID = $.inArray(lines[a][2], listOfDonors[0],0) //search listOfDonors to find record ID
var donorID = listOfDonors[1][indexOfDonorID]
var budgetLineCode = lines[a][3].substr(lines[a][3].length - 3) //last 3 digits

console.log(budgetLineCode)
if ($.inArray(budgetLineCode,existingBudgetLines) == -1){ //if not already existing
addBudgetLine2(budgetLineCode, lines[a][6],lines[a][10],donorID,a,lines);
} else {
updateStatusInfo(budgetLineCode + " is already imported. Skipping this row of the CSV file.")
a++ //increment a
addBudgetLine1(lines,a)
}

} else {
if (typeof lines[a][3] != 'undefined') {
console.log("Skipped line for a = " + a)
a++ //increment a
addBudgetLine1(lines,a)
} else {
console.log("Looks like end of file!")
a++ //increment a
addBudgetLine1(lines,a)
}

}
} else {
alert("Finished.")
Knack.hideSpinner();
}

};


//=====================
//POST/add budget line function
//=====================

function addBudgetLine2(budgetLineCode, description, totalAmount, donorID,a,lines){ //last two parameters passed just to callback function1 later
var api_url = 'https://api.knack.com/v1/scenes/scene_344/views/view_990/records' //"Add Budget Line" form
var equivalentUSD = totalAmount / initialCurrencyConversionRate
console.log("Total value (function addBudgetLine2):")
console.log(totalAmount)
console.log("Converstion rate:")
console.log(initialCurrencyConversionRate)

var dataToPost = ""
dataToPost = {
field_927: description,
field_178: knackProjectID,
field_654: totalAmount, //in donor currency
field_179: donorID,
field_82: budgetLineCode,
field_656: equivalentUSD,
field_958: currentProjectCurrencyID,
};



console.log("Equivalent USD")
console.log(equivalentUSD)

for (var k=0; k<scenarios.records.length; k++) {
if (Number(equivalentUSD) < Number(scenarios.records[k].field_899_raw)){ //find the scenario limit. If USD value is less than max value for the current scenario...
dataToPost['field_657'] = scenarios.records[k].field_898; //set scenario field
dataToPost['field_658'] = scenarios.records[k].field_900; //and an additional related field
break;
}


}
console.log("Chosen scenario:")
console.log(dataToPost['field_657'])
$.ajax({
url: api_url
, type: "POST"
, headers: {
'X-Knack-Application-Id': '56fb8b0fad25a3be79b5054a'
, "X-Knack-REST-API-KEY": 'knack'
, "Authorization": userToken
,"Content-Type":"application/json"
}
, data: JSON.stringify(dataToPost)
, success: function(data) {
console.log("Successful added line:")
console.log(data)
a++ //increment a
addBudgetLine1(lines,a) //and go back and repeat
}
, error: function (request, status, error) {
handleAjaxError(request, status, error);
Knack.hideSpinner();
}
})


};




});
});

Pastbin link with (slightly?) better formatting:

https://pastebin.com/PCCuUpFk