Importing data via Javascript – not working with dates

Tap Forms – Organizer Database App for Mac, iPhone, and iPad Forums Script Talk Importing data via Javascript – not working with dates

Viewing 21 reply threads
  • Author
    Posts
  • November 28, 2021 at 12:59 PM #45821

    Victor Warner
    Participant

    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.
    November 28, 2021 at 1:53 PM #45825

    Brendan
    Keymaster

    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 7 months ago by Brendan.
    November 28, 2021 at 4:23 PM #45828

    Victor Warner
    Participant

    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()
    
    November 28, 2021 at 6:04 PM #45830

    Sam Moffatt
    Participant

    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();
    
    November 29, 2021 at 4:53 AM #45839

    Victor Warner
    Participant

    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…).

    November 29, 2021 at 3:49 PM #45844

    Sam Moffatt
    Participant

    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.

    November 29, 2021 at 3:52 PM #45845

    Sam Moffatt
    Participant

    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.

    November 29, 2021 at 8:27 PM #45856

    Brendan
    Keymaster

    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.

    November 29, 2021 at 11:32 PM #45859

    Sam Moffatt
    Participant

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

    November 30, 2021 at 12:31 PM #45865

    Victor Warner
    Participant

    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

    November 30, 2021 at 2:51 PM #45866

    Daniel Leu
    Participant

    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 7 months ago by Daniel Leu.
    November 30, 2021 at 3:55 PM #45868

    Brendan
    Keymaster

    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.

    November 30, 2021 at 6:18 PM #45871

    Sam Moffatt
    Participant

    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 6 months, 4 weeks ago by Sam Moffatt. Reason: forgot to close the backtick
    December 1, 2021 at 4:59 AM #45880

    Victor Warner
    Participant

    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?

    December 1, 2021 at 1:08 PM #45888

    Sam Moffatt
    Participant

    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.

    December 6, 2021 at 4:05 AM #45962

    Victor Warner
    Participant

    Sam,

    Thank you for the information.

    January 28, 2022 at 10:54 AM #46486

    Chris Ju
    Participant

    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?

    January 28, 2022 at 12:21 PM #46489

    Sam Moffatt
    Participant

    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.

    January 28, 2022 at 12:52 PM #46491

    Chris Ju
    Participant

    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…

    January 28, 2022 at 3:23 PM #46499

    Chris Ju
    Participant

    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…

    January 28, 2022 at 6:09 PM #46500

    Chris Ju
    Participant

    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)

    January 28, 2022 at 6:55 PM #46503

    Sam Moffatt
    Participant

    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.

    January 29, 2022 at 12:52 AM #46509

    Chris Ju
    Participant

    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],
    
    ...
    
Viewing 21 reply threads

You must be logged in to reply to this topic.