Importing data via Javascript – not working with dates

[Victor Warner]

Sam Moffat provided the code to import a CSV file into a Form: https://www.tapforms.com/forums/forum/script-talk/.

I have adapted the script but dates are not being imported. The dates are in the format: “01/01/2020” (day/month/year). The data is imported, but the dates are skipped.

The database / forms are attached and the csv file.

Is it necessary to add something to the Javascript to get the dates to import?

Attachments:

You must be logged in to view attached files.

[Brendan]

The dates need to be actual Date objects, not strings in any particular format. So you’ll need to convert the string format of the date to actual Date objects first.

How does the computer know your date format is day/month/year and not month/day/year? You need to tell it explicitly what you say, not what you mean.

let date = new Date("01/01/2020");

But you might get different results depending on what your region settings are.

let date = new Date("01/02/2020");

Is the above January 2, 2020? Or is it February 1, 2020?

It’s best to use this format:

let date = new Date(2020, 01, 01)

The parameters are Date(year, month, day)

• This reply was modified 2 years, 4 months ago by Brendan.

[Victor Warner]

Brendan

Thank you for the response. Apologies in advance, but given my basic (almost non-existent) understanding of JavaScript, how do I incorporate into the script?

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

// replace with your field ID's

// Passport

var passport_number_id = 'fld-9fe227057cdf44bfa62ad8a97cc6a62a';
var nationality_id = 'fld-0039b290b2054881ac8f004c01903c6f';
var country_id = 'fld-6e861fab76b9457bb625953cece54c96';
var date_of_issue_id = 'fld-0154d8f9ce384e708502fdd775d7bfb1';
var date_of_expiry_id = 'fld-df90736c929549cf8b863666077937fe';
var issuing_authority_id = 'fld-d03c8c1e5fe64e4dada673cb4a6ed322';

function Import_Passport() {
	let filename = "file:///Users/victor/Desktop/Passport - test.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 = document.getFormNamed("Passport").addNewRecord();
		
		//document.getFormNamed("Other Form").addNewRecord()
		
		var newRecord = document.getFormNamed("Passport").addNewRecord();
		newRecord.setFieldValues({
			[passport_number_id]: line[0],
			[nationality_id]: line[1],
			[country_id]: line[2],
			[date_of_issue_id]: line[3],
			[date_of_expiry_id]: line[4],
			[issuing_authority_id]: line[5],
		});
		document.saveAllChanges();
	}
}

Import_Passport()

[Sam Moffatt]

Dates are always a pain. Javascript wants your dates to be in a particular non-ambiguous format when parsing them.

Personally I’d actually suggest landing it in a normal text field so that you have the original value and then using a script field to parse the value of the field into a date field.

For a parsing script for US style dates, it looks like this:

function Date_Parser() {
	var date_import = record.getFieldValue('fld-3e35e177e6174b139d3c4c8c2eea08d0');
	var date_parsed = record.getFieldValue('fld-96b53c5f607e4e7286fad5448ceae477');

	// if the date_parsed field is set, don't reset it.
	if (date_parsed) {
		return;
	}
	
	var pieces = date_import.split("/");
	var parsedDate = new Date(pieces[2], pieces[0] - 1, pieces[1]);
	
	record.setFieldValue('fld-96b53c5f607e4e7286fad5448ceae477', parsedDate);
	document.saveAllChanges();
	return parsedDate;
}

Date_Parser();

[Victor Warner]

Sam,

Thank you for the reply.

**Query 1**.
Does you code require the date data first to be imported as a text field, and then the script, in another field, with the script attached to the field to format the date?

**Query 2**.
How would the script you provided need to be adapted for a non-USA (UK) format?

**Query 3**.
More fundamentally, the database I have in Tap Forms contras a lot more Forms – and has many layouts based on and use existing dates – so I am reluctant to engage in a lot of restructuring to add more fields.

Either to do some processing of the dates in the script you originally provided or outside of Tap Forms – e.g. using a Keyboard Maestro macro to do so.

The backstory is that I am exporting several hundred records from FileMaker, each record (in Tap Form forms speak) consisting of several forms. I have to do this record by record – as the forms need to be linked together in Tap Forms – and have to be incorporate some manual data which is still recorded on paper.

Using a script in importing will speed that part of the process – the way I have been doing this until know is to use the File / Import / Records in Tap Forms and automating the process through Keyboard Maestro – but for 6 or 7 forms for each record it take several minutes – and sometimes does not work (if the computer slows down the appearance of a window or keystrokes etc are delayed in appear, etc…).

[Sam Moffatt]

Q1: Yes, this is a three field setup. One text field to import the date as is, a second “date” field to store the canonical representation and a third script field that watches the text field and mutates it to the date field.

Q2: Swap this line around:

	var parsedDate = new Date(pieces[2], pieces[0] - 1, pieces[1]);

becomes:

	var parsedDate = new Date(pieces[2], pieces[1] - 1, pieces[0]);

The new Date() accepts a year, monthIndex (0 to 11, hence the -1) and then day, so we just need to flip the order to match.

Q3: You can add a new field to land just the imported data and keep your existing date field. That shouldn’t mess up any layout, you’re just adding a text field to land data in during import. The field itself can be hidden most of the time, or if using custom layouts just not shown. Same deal with the script field or you could achieve something similar with a form script. I perhaps err on the side of caution that it’s usually more annoying to detangle data after you’ve imported and mangled it. You could do it inline with the import parse using the same code to convert a passed date string into a JS Date object:

function parseDate(dateString) {
	let pieces = dateString.split("/");
	return new Date(pieces[2], pieces[1] - 1, pieces[0]);
}

The above assuming UK DD/MM/YYYY format already.

[Sam Moffatt]

As an aside partially the desire to land data “as is” comes from working with data architectures like the layered data architecture where as much as possible you land data without any transformations applied (such as morphing the date) so that you can get back to the original value of the data if you find something “wrong” in it later down the line (e.g. someone used the wrong date format). In your use case that might be overkill but that’s in part some of the goal there.

[Brendan]

I think maybe just modifying the code as follows might work?

let date_of_issue_string = line[3];
let date_of_expiry_string = line[4];

let date_of_issue = parseDate(date_of_issue_string); // using Sam's date parse function
let date_of_expiry = parseDate(date_of_expiry_string); // using Sam's date parse function

newRecord.setFieldValues({
   [passport_number_id]: line[0],
   [nationality_id]: line[1],
   [country_id]: line[2],
   [date_of_issue_id]: date_of_issue,
   [date_of_expiry_id]: date_of_expiry,
   [issuing_authority_id]: line[5],
});

When you call record.setFieldValues({}); Tap Forms just loops through those values calling record.setFieldValue(field_id, value);, which would do the right thing if given a Date value.

Maybe I’m missing something, but I’m not sure what the need for the extra field would be or even a Field Script. Doesn’t the code read the file, parse, and then set the values on the fields in the record? If so, then it could just transform the date values properly in JavaScript before writing them to the actual Date fields in the record.

[Sam Moffatt]

Yes, you could do it as a part of the import as well. My mind went off on another tangent :D

[Victor Warner]

Brendan, Sam,

Thank you both for the suggestions. I have incorporated what you have both suggested but I am obviously still doing something wrong. The csv file is imported and:

1. the first date (date_of_issue) is imported and formatted correctly, but
2. not the second (date_of_epxiry), which is ignored; and
3. the first date is used (date_of_issue) in the field for (date_of_epxiry).

The error message is “Date import: TypeError: undefined is not an object (evaluating ‘dateString.split’), line:(null)”

The complete script is:

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

// replace with your field ID's

// Passport

var passport_number_id = 'fld-9fe227057cdf44bfa62ad8a97cc6a62a';
var nationality_id = 'fld-0039b290b2054881ac8f004c01903c6f';
var country_id = 'fld-6e861fab76b9457bb625953cece54c96';
var date_of_issue_id = 'fld-0154d8f9ce384e708502fdd775d7bfb1';
var date_of_expiry_id = 'fld-df90736c929549cf8b863666077937fe';
var issuing_authority_id = 'fld-d03c8c1e5fe64e4dada673cb4a6ed322';
var place_passport_seen_id = 'fld-a23576f1e56b48539434de96f5afda23';

function Import_Passport() {
	let filename = "file:///Users/victor/Desktop/Passport.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 = document.getFormNamed("Passport").addNewRecord();
		
		//document.getFormNamed("Other Form").addNewRecord()
		
		let date_of_issue_string = line[3];
		let date_of_expiry_string = line[4];

		let date_of_issue = parseDate(date_of_issue_string); // using Sam's date parse function
		
			function parseDate(dateString) {
	let pieces = dateString.split("/");
	return new Date(pieces[2], pieces[1] - 1, pieces[0]);
	}
		let date_of_expiry = parseDate(date_of_issue_string); // using Sam's date parse function
		
		
		function parseDate(dateString) {
	let pieces = dateString.split("/");
	return new Date(pieces[2], pieces[1] - 1, pieces[0]);
	}
		
		
		
		var newRecord = document.getFormNamed("Passport").addNewRecord();
		newRecord.setFieldValues({
			[passport_number_id]: line[0],
			[nationality_id]: line[1],
			[country_id]: line[2],
			[date_of_issue_id]: date_of_issue,
			[date_of_expiry_id]: date_of_expiry,
			[issuing_authority_id]: line[5],
			[place_passport_seen_id]: line[6],
		});
		document.saveAllChanges();
	}
}

Import_Passport()

I would very grateful for any further help.

Victor

[Daniel Leu]

You have a little typo:

let date_of_expiry = parseDate(date_of_issue_string);
should be
let date_of_expiry = parseDate(date_of_expiry_string);

It is sufficient to declare function parseDate(dateString){...} only once. Personally, I would put it before function Import_Passport() {.

• This reply was modified 2 years, 4 months ago by Daniel Leu.

[Brendan]

You wouldn’t define a function within a for loop, so put it outside of any other function declarations. You also have it defined twice in the same code block.

[Sam Moffatt]

There is probably a record that doesn’t have a string value in it which is causing the split error. Updated the full script to shuffle the parseDate function, add a quick guard to return undefined when the dateString isn’t truthy and fix the expiry/issue mismatch.

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

// replace with your field ID's

// Passport

var passport_number_id = 'fld-9fe227057cdf44bfa62ad8a97cc6a62a';
var nationality_id = 'fld-0039b290b2054881ac8f004c01903c6f';
var country_id = 'fld-6e861fab76b9457bb625953cece54c96';
var date_of_issue_id = 'fld-0154d8f9ce384e708502fdd775d7bfb1';
var date_of_expiry_id = 'fld-df90736c929549cf8b863666077937fe';
var issuing_authority_id = 'fld-d03c8c1e5fe64e4dada673cb4a6ed322';
var place_passport_seen_id = 'fld-a23576f1e56b48539434de96f5afda23';

function parseDate(dateString) {
	if (!dateString) {
		return undefined;
	}
	let pieces = dateString.split("/");
	return new Date(pieces[2], pieces[1] - 1, pieces[0]);
}

function Import_Passport() {
	let filename = "file:///Users/victor/Desktop/Passport.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 = document.getFormNamed("Passport").addNewRecord();
		
		//document.getFormNamed("Other Form").addNewRecord()
		
		let date_of_issue_string = line[3];
		let date_of_expiry_string = line[4];

		let date_of_issue = parseDate(date_of_issue_string); // using Sam's date parse function
		
		let date_of_expiry = parseDate(date_of_expiry_string); // using Sam's date parse function	
		
		var newRecord = document.getFormNamed("Passport").addNewRecord();
		newRecord.setFieldValues({
			[passport_number_id]: line[0],
			[nationality_id]: line[1],
			[country_id]: line[2],
			[date_of_issue_id]: date_of_issue,
			[date_of_expiry_id]: date_of_expiry,
			[issuing_authority_id]: line[5],
			[place_passport_seen_id]: line[6],
		});
		document.saveAllChanges();
	}
}

Import_Passport();

• This reply was modified 2 years, 4 months ago by Sam Moffatt. Reason: forgot to close the backtick

[Victor Warner]

Sam, Brendan, Daniel,

Thank you all (particular Sam) for your responses.

I have learnt such a lot – although cannot understand it all at the moment.

Last question: if I use a file name other than “passport.csv” for

> let filename = “file:///Users/victor/Desktop/Passport.csv”;

I am getting a “No CSV file?” error.

As far as I can see once the variable “filename” is defined in the line it should use whatever is the filename?

[Sam Moffatt]

You should put the files you want to import in the “Script folder access” folder (see preferences for the document) to make sure Tap Forms can get to it otherwise MacOS may block read access. If you imported that CSV via the normal TF import process previously (File > Import) then MacOS will have remembered that you selected the CSV file and give TF access to it. This is part of the security sandboxing that prevents apps from accessing files that you haven’t explicitly granted the application access. There are also some special folders that MacOS also adds extra security to which might require you to give it access via System Preferences > Security & Privacy > Privacy.

[Victor Warner]

Sam,

Thank you for the information.

[Chris Ju]

Great thanks to Brendan and Sam also from me for that script help… I had also this date issue and that date parse function works great!

BTW: Is ist possible to get the home directory name?

[Sam Moffatt]

You should be able to use Utils.getUserName() to get the user name and use that to compose the home directory. That assumes nothing funky is going on there but in most non-enterprise use cases that assumption of default home directory location should hold.

[Chris Ju]

Ok, thanks. I also had this idea, but for that i had to change account name, what isn’t a problem at all…

@Brendan (if you read this): A function would be cool to get the home dir…

[Chris Ju]

I have two issues, i can’t solve:

I have two forms (F1 + F2) and i’m importing via script from csv file. With F1 all fields with dates (e.g. 13.01.2022) are fine using Sam’s function parseDate. But in F2 it doesn’t work. Same format as above and it says “undefined”. What the hell?

The second issue i have is with time fields. The import file has entries with time stamps (e.g. 17:43:56). Could these fields also be handled as dates? Is there a similar function?

Thanks for your help…

[Chris Ju]

I can’t imagine, why this is buggy:

...

let abfahrt_datum_string = line[2];
console.log(abfahrt_datum_string);
let abfahrt_uhrzeit_string = line[3];
console.log(abfahrt_uhrzeit_string);
let ankunft_datum_string = line[4];
let ankunft_uhrzeit_string = line[5];
let export_datum_string = line[22];
let export_uhrzeit_string = line[23];

let abfahrt_datum = parseDate(abfahrt_datum_string);
function parseDate(dateString) {
let pieces = dateString.split('.');
return new Date(pieces[2], pieces[1] - 1, pieces[0]);
}
// let abfahrt_datum = new Date(2022, 0, 18, 12, 0, 0);
console.log('Abfahrt_Datum: ' + abfahrt_datum);

let abfahrt_uhrzeit = parseDate(abfahrt_uhrzeit_string);
function parseDate(dateString) {
let pieces = dateString.split(":");
return new Date(1970, 0, 1, pieces[0], pieces[1], pieces[2]);
}
// let abfahrt_uhrzeit = new Date(1970, 0, 1, 17, 58, 12);
console.log('Abfahrt_Zeit: ' + abfahrt_uhrzeit);

...

Console:

13.01.2022
09:23:01
Abfahrt_Datum: Invalid Date
Abfahrt_Zeit: Sun Nov 09 1902 00:00:00 GMT+0100 (CET)

[Sam Moffatt]

You have two parseDate methods and one will likely mess with the other somewhere. You will need to give them independent names and usually extract them at the top of the script (this is what Daniel was referring to). Perhaps name one parseDate and the other parseTime to reflect that it’s handling a time.

If you look at the earlier script I posted, I had parseDate prior to the Import_Passport method. If you do something similar with the parseDate and renamed the other one parseTime, it’ll make it a little easier to read and I think should fix the error you’re getting.

[Chris Ju]

Wonderful! Thanks a lot! It works…

...

function parseDate(dateString) {
	if (!dateString) {
		return undefined;
	}
	let pieces = dateString.split('.');
	return new Date(pieces[2], pieces[1] - 1, pieces[0]);
}

function parseTime(dateString) {
	if (!dateString) {
		return undefined;
	}
	let pieces = dateString.split(":");
	return new Date(1970, 0, 1, pieces[0], pieces[1], pieces[2]);
}

// ------- Import
function Import_Entries() {
	let filename = 'file:////Users/' + user_dir + '/Documents/tapforms_dir/' + import_file_name;
	let csvFile = Utils.getTextFromUrl(filename);
	if (!csvFile) {
		console.log("No input file?");
		return
	}
	var output = Papa.parse(csvFile, {
		delimiter: ";",
		// header: false,
		});
	// # 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) {
		
		if ((line[0] !== 'Fahrzeug') || (typeof line[0] === typeof undefined)) {

		let abfahrt_datum_string = line[2];
		console.log(abfahrt_datum_string);
		let abfahrt_uhrzeit_string = line[3];
		console.log(abfahrt_uhrzeit_string);
		let ankunft_datum_string = line[4];
		let ankunft_uhrzeit_string = line[5];
		let export_datum_string = line[22];
		let export_uhrzeit_string = line[23];
		
		// # dates
		
		let abfahrt_datum = parseDate(abfahrt_datum_string);
		let ankunft_datum = parseDate(ankunft_datum_string);
		let export_datum = parseDate(export_datum_string);

		// time stamps
			
		let abfahrt_uhrzeit = parseTime(abfahrt_uhrzeit_string);
		let ankunft_uhrzeit = parseTime(ankunft_uhrzeit_string);
		let export_uhrzeit = parseTime(export_uhrzeit_string);
		
		var newRecord = form.addNewRecord();
		newRecord.setFieldValues({
			[fahrzeug_id]: line[0],

...

[Author]

Posts