Importing data via Javascript – not working with dates

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.

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

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 3 years ago by Daniel Leu.

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 3 years ago by Sam Moffatt. Reason: forgot to close the backtick

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.

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?

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…

I can’t imagine, why this is buggy:

Console:

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],

...