Using Javascript to import csv files

[Victor Warner]

It appears from another post that it possible to import csv files to a form using Javascript.

I go lost in the detail (and not being proficient in Javascript) and could not work out how to do so for myself.

Using a simple database (see attached and csv files) what are the basic format/commands in Javascript to do so?

Also whether it is possible to import into more than one form at a time.

In the attached there are two forms (Who and Passport) whether it is possible to import into both of them from the same script.

INnthe real world version I have data in Filemaker and I am exporting the data record by record and importing through File/Import/Records in Tap Forms. I have automated the steps with Keyboard Maestro – but it is time-consuming and sometimes fails (where Tap Forms or some other processes hangs for a while).

Any help would be gratefully received.

Attachments:

You must be logged in to view attached files.

[Daniel Leu]

There is currently no API available to import CSV files. One way to go around that is to use the clipboard to get data into TF (Utils.copyTextFromClipboard()) or to use external script calls with parameters (see paragraph ‘Calling a script from a URL’ in https://www.tapforms.com/help-mac/5.3/en/topic/javascript-api).

[Brendan]

I would think that you could import a CSV file because Tap Forms allows you to setup a Script Folder that has security permissions to read files from. Then you could use the var csv_data = Utils.getTextFromUrl(url); function to read in the CSV file and then just loop through it parsing out the data and writing it to the database.

[Daniel Leu]

Yeah, that’s another way to read a CSV file. I forgot about that one. I wouldn’t all it import since it doesn’t use TapForms import features.

Cool to have so many different ways to approach a task!

[Victor Warner]

Thank you for the responses.

I would be grateful for a simple illustration how to use Utils.getTextFromUrl with a CSV file stored on a computer, how to parse the CSV, and then save the elements to different fields in a form.

Since I can only do the most basic things with JavaScript I am not sure how even to begin a script such as this.

[Sam Moffatt]

So the link in the original post has an example of Utils.getTextFromUrl, the same sort of instructions apply.

For a CSV parser, I just tried Papa Parse and it worked fine. I went to their GitHub repo and copied their uncompressed Javascript code into a new form script called “PapaParse”. I did the following quick test and it worked properly:

form.runScriptNamed('PapaParse');

function Csv_Parse_Test() {

	// Replace with your own code
	var hello_world = '"Hello, World!",Test,123'; 

	var output = Papa.parse(hello_world);

	console.log(JSON.stringify(output, null, '\t'));

}

Csv_Parse_Test();

Outputted the following:

7/10/21, 5:22:19 pm / Script Examples / CSV Parse Test
{
	"data": [
		[
			"Hello, World!",
			"Test",
			"123"
		]
	],
	"errors": [],
	"meta": {
		"delimiter": ",",
		"linebreak": "\n",
		"aborted": false,
		"truncated": false,
		"cursor": 24
	}
}

This seems to be correct interpretation of the input. So let’s put that together with the earlier post:

// this imports the Papa Parse script
form.runScriptNamed('PapaParse');

// replace with your field ID's
var title_id = 'fld-4e8e68e2979643be8417c3469015abff';
var description_id = 'fld-429934cd6ae646b1ac1cf5ad659cb926';
var url_id = 'fld-b46c858779094c9d906f2ce5e5c4a028';
var upload_date_id = 'fld-fc1f8537415c4d6cabb8c0784b64f2a6';
var thumbnail_url_id = 'fld-12b4e040711b4afea624c7c049fdd7ce';

function Import_Entries() {
	let filename = "file:///Users/yourusernamehere/Documents/input.csv";
	let csvFile = Utils.getTextFromUrl(filename);
	
	if (!csvFile) {
		console.log("No CSV file?");
		return
	}
	
	var output = Papa.parse(csvFile);

	// abort if there are any errors and log to console.
	if (output.errors.length > 0) {
		console.log(errors.join("\n"));
		return;
	}

	// read each line
	for (let line of output.data) {
		var newRecord = form.addNewRecord();
		newRecord.setFieldValues({
			[title_id]: line[0],
			[description_id]: line[1],
			[url_id]: line[2],
			[upload_date_id]: line[3],
			[thumbnail_url_id]: line[4]
		});
		document.saveAllChanges();
	}
}

Import_Entries();

First thing we do is pull in the Papa Parse script. Then we have the field ID’s for our fields that we’re going to use. We could put this anywhere, I like it being in global scope since they’re immutable anyway.

We enter the function and define the path to the file. If you don’t know what this is, drag the file from Finder into the script editor and it’ll put the full path in for you. Make sure this folder is the same one you’ve given access to in the Script Folder Access in the preferences for this document. The next line imports that file for you.

We then check to see if we got anything from the CSV file, if we didn’t we log an error and return flow control. We then hand the contents of the file to Papa.parse and if it has any errors we log them and return as well.

We then read each line of the data to process it, create a new record, map the field order in the CSV to our Tap Forms field ID’s and then save the changes.

You might get a performance boost moving the document.saveAllChanges() line but there are quirks around creating new records that cause me to leave the save in the loop not outside.

I’ve not tested it but something like that should work.

[Victor Warner]

Sam,

Thank you very much for the script.

On the first run it worked – it imported the .csv file.

On subsequent runs – nothing was imported – and no error was generated.

The script as I used:

// this imports the Papa Parse script
form.runScriptNamed('PapaParse');

// replace with your field ID's

var first_name_id = 'fld-068c60aaec5047ea93f46988251ae938';
var last_name_id = 'fld-4155c0ec104e4448af17f2776880d205';
var email_id = 'fld-6a9e9e828e294e2ca694e751c7f3faf9';
var date_id = 'fld-9c739c301f614a7c8b21eb427059430e';

function Import_Entries() {
	let filename = "file:///Users/victor/Desktop/Whom.csv";

	let csvFile = Utils.getTextFromUrl(filename);
	
	if (!csvFile) {
		console.log("No CSV file?");
		return
	}
	
	var output = Papa.parse(csvFile);

	// abort if there are any errors and log to console.
	if (output.errors.length > 0) {
		console.log(errors.join("\n"));
		return;
	}

	// read each line
	for (let line of output.data) {
		var newRecord = form.addNewRecord();
		newRecord.setFieldValues({
			[first_name_id]: line[0],
			[last_name_id]: line[1],
			[email_id]: line[2],
			[date_id]: line[3],
		});
		document.saveAllChanges();
	}
}

The .csv file contains one line:

Simon,Adams,Noone@AtHome.com,01/02/1888

I tried changing the data but that made no difference.

I attach the database in case there is something in that which shows what the issue is.

Attachments:

You must be logged in to view attached files.

[Sam Moffatt]

There should be an Import_Entries() line at the bottom of the script to actually do the import. Without that line, it won’t run the function to import the data.

[Victor Warner]

Sam,

Thank you.

One question: Is it possible to import more than one .csv and put the contents of each .csv into a different form with the same script?

[Sam Moffatt]

The easiest way would be to copy the function and replace the mappings. So instead of Import_Entries() and Import_Whom() and you just change the file name, remap the field mapping and instead of form.addNewRecord() you do document.getFormNamed("Other Form").addNewRecord(). That means a bunch of duplicated code but it will work.

A more elegant solution would probably try to enable the header support and then attempt to map those across automatically. First change is to enable headers by updating the Papa.parse line to add a new header option. Then the output.data will change to be keyed by the first row field names. This means assuming the header matches your field name, otherwise you’re hard coding. So assuming the CSV header matches field names, you can then iterate over them, grab the field from TF and then update it’s value.

Here’s the full script:

function Import_Entries(formName, filename) {
	let filename = "file:///Users/victor/Desktop/" + filename;

	let csvFile = Utils.getTextFromUrl(filename);
	
	if (!csvFile) {
		console.log("No CSV file?");
		return
	}
	
	var targetForm = document.getFormNamed(formName);
	
	if (!targetForm) {
		throw new Error("Invalid form name: " + formName);
	}

	var output = Papa.parse(csvFile, { header: true });

	// abort if there are any errors and log to console.
	if (output.errors.length > 0) {
		console.log(errors.join("\n"));
		return;
	}
	
	console.log(JSON.stringify(output));

	// read each line
	for (let line of output.data) {
		var newRecord = form.addNewRecord();
		for (let fieldName in line) {
			console.log(fieldName);
			if (!line[fieldName]) {
				continue;
			}
			
			var field = targetForm.getFieldNamed(fieldName);
			if (!field) {
				console.log("Invalid field name: " + fieldName);
				continue;
			}
			
			newRecord.setFieldValue(field.getId(), line[fieldName]);
		}

		document.saveAllChanges();
	}
}

// Import entries to form "Who" from filename "Whom.csv"
Import_Entries("Who", "Whom.csv");

This is a use case I’d possibly put in my Script Manager form (and I should add Papa Parse to the repo) or some other sort of scripting form that has scripts that are agnostic of their parent form (sort of like a document script).

[Author]

Posts