Importing How To's
Learn different importing techniques.
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 // skip headers Set header = Do Read Line Params File = imp_file Set header_arr = Do CSV Interpret Params String = header Set dummy = header_arr 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 Set sm["Surname"] = records Set sm["Type"] = records Set sm["Is Employee"] = records Set sm["Main Contact #"] = records Set sm["Mobile #"] = records Set sm["Email"] = records 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.
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 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 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 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 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.
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’…
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 Set newPerson["Surname"] = fields Set usOrganisation = fields 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 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.