Importing How To's

From Pengower
Jump to: navigation, search

Learn different importing techniques.

Scripted Importing

Scripted Importing can be used to import staff details from one system to another.

Example Staff Member Import

// get the file for import
Set imp = Current_Item
Set imp_files = imp["Import File"]
Set imp_file = imp_files[1]

// skip headers
Set header = Do Read Line Params File = imp_file
Set header_arr = Do CSV Interpret Params String = header
Set dummy = header_arr[1]

Set counter = 0	
Loop
	Set csv_line = Do Read Line Params File = imp_file
	If csv_line != "#EOF"
		Set records = Do CSV Interpret Params String = csv_line
		
		Set sm = Do New Object Params Type = "Staff Member"
		Set sm["First Name"] = records[2]
		Set sm["Surname"] = records[3]
		Set sm["Type"] = records[4]
		Set sm["Is Employee"] = records[6]
		Set sm["Main Contact #"] = records[7]
		Set sm["Mobile #"] = records[8]
		Set sm["Email"] = records[11]
		
		Do Store Item Params Item = sm, Force = 1
		
		Report = "Creating staff member " + counter
		Set counter = counter + 1
	EndIf
Until csv_line = "#EOF"

Output = imp
Return_Ok

What the script is doing

1) Gets the attached file from the import object
2) Takes the file
3) If there's a header e.g. name etc it ignores that as it bypasses the first line
4) Loops through the file
5) Reads one line at a time 'Do Read'
6) Checks it hasn't read the end of file
7) 'CSV interpret' take those and turns into an array of records
8) Creates staff member and then sets value
9) Loops round unitl the end of file


Notes on How to Import

Importing of data into Pengower Applications is carried out through a specialised importing interface that can be added to any application through a standard action. The user is required to identify the type of entity to be imported, the file that contains the import data and the elements to be imported. The file containing the import data should be a standard csv (comma separated value) file with each column representing an element to be imported. Note the import routines expect all rows in the csv file to be complete. Most (though not all) element types can be imported. Below are some notes on how the csv data should be formatted for each element type.

Type Notes

Text

Text elements can be imported by inserting the desired string into the import file. If the string contains commas it should be encased in double quotes. If the string is to contain carriage returns, as in a multi-line string then the place of a carriage return should be taken by ‘~~’ (2 tilde symbols). If the text to be imported contains a double quote character (") it should be preceded by a back-slash (\) in the import file. Examples: Hello "Hello, Have we met before?" Hello~~Have we met before? 4\" Nails

Dates and Times

Dates and times can be imported subject to the constraint that the string in the import file conforms to one of the configurations shown below: yyyy-mm-dd hh:mm yyyy-mm-dd dd/mm/yyyy hh:mm dd/mm/yyyy hh:mm

Examples: 2005-06-01 18:30 2005-06-30 01/06/2005 18:30 30/06/2005 18:30


Number

Number elements can be imported so long as the string representing the number can be converted successfully into a numeric value. Examples: 100 99.56 -123000


Select

Select elements can be imported by including the string value to be selected in the import file. Note the value must match exactly (including case) the select option from the application. Example: England


Multi-Select

Multi-select elements can be imported by including appropriately matching strings in the import file as with Select elements. Note, in the case where multiple values are to be selected they should be separated by ‘#’ (hash) character in the import file. Example: England England#France


Time Span

Time span elements can be imported as long as the string representing the period conforms to one of the following configurations: ddd:hh:mm (note leading zeros not required) hh:mm Examples: 30:00:00 (30 days) 24:00 (24 hours)


True or False

True or false elements can be imported using the values ‘True’ or ‘False’ in the import file.


Relationships

Relationship elements can be imported. The string in the import file must match exactly the relationship display string for the related item, i.e. the string that is displayed when the item is observed via a relationship element on another item. In the case where the item is to be related to many instances of another type the relationship display strings for these instances must be separated by a single tilde character (~). Examples: A person works in a department. The department is ‘Finance’… John,Smith,Finance

A person has certain skills. The skills are ‘Accounting’, ‘Basket Weaving’…

John,Smith,Accounting~Basket Weaving


Auto-Generate

NOTE: The role of auto-generate elements in import files is handled in a special way. All auto-generate elements for a given type MUST be included in an import file for a type. The value given will not be used when creating the item so a dummy value can be used e.g. ‘0’ or ‘abc’, the presence of the column simply indicates to the platform that the Auto-generate element requires populating.

If the column is not included the auto generate element will not have a fixed value until it is accessed and saved within the application. In many cases this may not be problematic, but where any auto-generate values are used in a critical way it is important that they are included.

How to Import Data From CSV File (Example Script)

// load files
Set curForm = Current_Form
Set personnelFiles = curForm["File(s)"]

Set importCount = 0

// loop through all files
For i = 1 To personnelFiles.Count
	
	// in each file - import people
	Set thisFile = personnelFiles[i]
	
	Loop
		Set thisLine = Do Read Line Params File = thisFile
		If (thisLine != "#EOF")
			
			// extract data from current line of current file
			Set fields = Do CSV Interpret Params String = thisLine
			
			// write new person record
			Set newPerson = Do New Object Params Type = "Person"
			Set newPerson["First Name"] = fields[2]
			Set newPerson["Surname"] = fields[1]
			Set usOrganisation = fields[3]
			Set sOrganisation = Do Replace String Params String = usOrganisation, Replace = "'", With = "''"
			
			// link person with organisation. If none exists, create one.
			Set orgs = Get "Organisation" Where Name = "'" + sOrganisation + "'"
			If orgs.Count > 0
				Set org = orgs[1]
			Else
				Set org = Do New Object Params Type = "Organisation"
				Set org["Name"] = usOrganisation
				Do Store Item Params Item = org, Force = 1
			EndIF
			
			Set newPerson["Organisation"] = org
			
			Do Store Item Params Item = newPerson, Force = 1
			Set importCount = importCount + 1
			
		EndIf
	Until thisLine = "#EOF"
	
Next

Set importCount = "" + importCount

Output = importCount + " people were imported."

Return_Ok


How to correct Importing Problems

If entities have been imported and the import didn’t include a column for an auto-generate element that is sequential then the finalised items may be incomplete/inaccurate. This can be corrected but requires data adaptation at the db level.

(1) Remove all elements that represent instances of the incorrect auto-generate

DELETE FROM MatElement WHERE parentId = <element def id>


(2) Clear any string index value associated with the element

UPDATE MatEntity SET sIndex04 = ‘’ WHERE parentId = <entity def id>


(3) Create a method within Matisse to update the uniqueIdx for the instances…

CREATE METHOD newUnique(newVal INTEGER
RETURNS NULL
FOR MatEntity
BEGIN
	UPDATE SELF SET uniqueIdx = newVal;<br>
END;


(4) Run a SQL routine from within Matisse Enterprise Manager to cycle through and update all instances


BEGIN
	DECLARE cnt INTEGER DEFAULT 0;
	SET cnt = 1;<br>
	FOR obj AS SELECT REF(a) FROM MatEntity WHERE parentId = <entity def id>
		obj.newUnique(cnt);
		SET cnt = cnt + 1;
	END FOR;
	RETURN cnt;
END;


(5) Ensure that the next uniqueIdx value on the EntityDef is set correctly.

(6) Create and run a script routine to open and store each instance of the type to correctly reset the auto-generate value.