Import data from JSON file

[Chris Ju]

Hi everyone,

I would like to import data from a JSON file. The fields should be filled accordingly. Here is an example of a JSON file with one record. Retrieving the entire file is no problem with

var data = Utils.getJsonFromUrl(url);

… but I want to fetch them one by one and populate the fields in the form’s record.

I think it is easy, but i’m still not handy with java script.

Easyest way would be from the import dialog, but that doesn’t work.

Thanks
Chris

[Chris Ju]

… Not all data records have the same fields and if i export multiple objects the JSON file looks like this (for two objects):

export_daten_multiple.json

[Daniel Leu]

What a coincidence, I’m working on getting JSON data into TF right now as well. My data is on the clipboard. So I import it from there. Next I translate it into a javascript data object. Then loop over the different entries.

For you, since not all records have the same fields, you need to check for valid content like I do with customer.name.

let customersJSON = Utils.copyTextFromClipboard();
let customers = JSON.parse(customersJSON);

for (let customer of customers){
	if (customer.name){
		console.log("Processing customer name: " + customer.name);
	} else {
		// missing customer name branch
	}
}

Hope this helps you get started.

[Chris Ju]

Thanks, that’s useful. But with my little experience with java script it isn’t possible for me to get a reliable result. Maybe someone else has solved that in the past and could give some hints.

Especially if one have a JSON file with multiple “datasets”, looping through the “datasets” and adding a new entry in TF for every “dataset” is difficult to understand for me :-( … but i’m working on it…

@Brendan (if you read this): Wouldn’t it be easier to introduce an import option for JSON files? JSON is also only a table in some way… or am I thinking wrong?

[Brendan]

Hi Chris,

Well, what you probably have is an array of dictionaries in your JSON.

So your data variable is probably just that. So you need to iterate over the array and pick out the values, create a new record for each entry in the JSON file and set the field value on it.

Here’s an example script that does that to import a file of time zones.

function Import_Time_Zones() {

    var time_zone_name_id = 'fld-1b8bca6459724faabec0d3f7a1b3e4e4';
    var country_id = 'fld-69acd4190b974d678a6e662308d580d7';
    var name_id = 'fld-4dc0850299c64960b480a3593bbdfad0';

    let url = 'file:///Users/brendan/Desktop/CitiesAndTimeZones.json';
    let cities = Utils.getJsonFromUrl(url);

    for (let index = 0; index < cities.length; index++) {
        let city = cities[index];
        let name = city.name;
        let country = city.country;
        let time_zone_name = city.timeZoneName;
        let newRecord = form.addNewRecord();
        newRecord.setFieldValue(name_id, name);
        newRecord.setFieldValue(country_id, country);
        newRecord.setFieldValue(time_zone_name_id, time_zone_name);
    }
    form.saveAllChanges();
}

Import_Time_Zones();

I’ve attached the JSON file if you want to play around with it.

One very helpful technique when dealing with JSON files, is use the JSON.stringify() function to see what kind of data you get from your JSON file.

[Brendan]

Hmm… seems the JSON file didn’t get attached. Maybe I have to zip it.

Attachments:

You must be logged in to view attached files.

[Chris Ju]

Hi Brendan, thanks for that example script. It works, if i get the JSON data from clipboard.

Using your original script to get the JSON data from file:


...

let url = 'file:///Users/jur/Desktop/CitiesAndTimeZones.json';
let cities = Utils.getJsonFromUrl(url);
console.log(cities);

for (let index = 0; index < cities.length; index++) {

...


results to:


undefined
Import Time Zones: TypeError: undefined is not an object (evaluating 'cities.length'), line:(null)


[Brendan]

Make sure that you set the Script folder on the Preferences window to your Desktop folder before you run the script. It’s probably not able to read the file due to security restrictions in macOS.

[Chris Ju]

Some things are so simple…

THANKS SO MUCH FOR YOUR HELP!

[Author]

Posts