Journal data can be one-time imported from Excel. For this purpose, you need to export data from Excel to appropriate XML format first.
For example, we have Alfresco data model and incoming data to fill journals:
Model : auto-model.xml
Data: Auto engine.xlsx Auto car.xlsx
To transfer data from Excel to XML, we need:
For engine and car object map will look like this:
<object></object> - single instance of the object to be created
<properties> - properties of object.
<auto_engineCode>some value</auto_engineCode> - type (separator - "_") and value of a particular property
<association> - association of object. If object has more than 1 association, we have to enumerate them, due to the fact that the need Excel unique tags. In the above map, the association numbered like <association>, <association_2>, <association_3> etc, but after export data, you need to replace the tags back to <association>.
Each association must include the following tags:
<assocType> - association type.
<targetNodeType> - type of target node.
<targetNodePath> - path to container of target node.
<propId> - property by which the association will be searched
<propValue> - value for the search
Warn: current map (xml-file) must have at least two <object> objects, if you expect to export two or more objects.
Data in excel must be represented as an Excel Table (insert → table)
For objects that include the association, it is necessary to further specify Excel data binding tags. See this file: Auto car compared.xlsx
Note that the tag <association>, <association_2> is necessary to compare the whole group.
For example: If association in XML Map has inner tags assocType, targetNodeType, targetNodePath, propId, propValue - then Excel must have the same additional columns
Exported car: Car exported.xml.
Set of columns for simple association : assocType, targetNodeType, targetNodePath, propId, propValue:
<association> <assocType>mdmref:plntCountry</assocType> <targetNodeType>mdmref:country</targetNodeType> <targetNodePath>/app:company_home/st:sites/cm:rkk-mdm/cm:dataLists/cm:mdm-country</targetNodePath> <propId>mdmref:cntrCode</propId> <propValue>RU</propValue> </association> |
Set of columns for association with cm:authority: assocType, name, createIfNotExists
<association> <assocType>mdmref:cmbsSMApproverRU</assocType> <cm_authority> <name>vSMApprover_3PM</name> <createIfNotExists>true</createIfNotExists> </cm_authority> </association> |
Set of columns for association with cm:user: assocType, username
<association> <assocType>some:assocType</assocType> <cm_user> <username>admin</username> </cm_user> </association> |
WARN: In XML file with exported data replace all <association_2>, <association_3>, <association_n> to <assocation>.
data-set settings:
<path>/app:company_home/st:sites/cm:rkk-mdm/cm:dataLists/cm:auto-engine</path> - path to container in which the objects will be created. Mandatory tag, It is meant that the container has already been created in the system.
<type>auto:engine</type> - type of object Mandatory tag.
<cmNameFromProp>auto_engineCode</cmNameFromProp> - cm:name will be set from value of this property (warning, It must be unique, and there is a limit on the allowable characters and longer).
<cmNamePrefix>prefix-</cmNamePrefix> - to cm:name specified prefix will be added.
<uuidFromProp>auto_engineCode</uuidFromProp> - sys:node-uuid will be set from value of this property (warning, It must be unique, and there is a limit on the allowable characters and longer).
<uuidPrefix>engine-</uuidPrefix> - to uuid specified prefix will be added.
<cmTitle_RU_fromProp>auto_carModelRus</cmTitle_RU_fromProp> - set cm:title (ru_RU locale) from property value.
<cmTitle_EN_fromProp>auto_carModelEn</cmTitle_EN_fromProp> - set cm:title (en, en_US locale) from property value.
<updateEnabled>true</updateEnabled> - if true then nodes with duplicated uuid, cmName or another identity property (see <identityProp> tag) - will be updated instead of created.
<identityProp>propQname</identityProp> - additional identityProp for updating instead of creation. Use with tag <updateEnabled>. Example for cm:name <identityProp>cm:name</identityProp>.
For car and engine data, final xml data look like this: auto_car_data.xml, auto_engine_data.xml
If the structure of the output xml-data file is known, then you can compose these files by any programming language by means of simple text operations. To do this, we put the source data for the directory into a csv file, for example:
DEO 3RD Party (Z014),,fgSUPlannersZ014 DEO Export only (Z002),fgSpecHPC,fgSUPlannersDEO_Export_Z002 DEO Export only (Z018),fgSpecHPC,fgSUPlannersKALINA DEO Local (Z002),fgSpecHPC,fgSUPlannersDEO_Z002 DEO Local (Z018),fgSpecHPC,fgSUPlannersKALINA |
Then use a script like the one below (remember to change the xml structure of the output):
use warnings; my $file_name = "c:\\my_files\\Book2.csv"; open my $file_handle, "<:encoding(UTF-8)", $file_name or die "Could not open $file_name: $!"; while (my $line = <$file_handle>) { my ($clusterCategory, $spec, $suPlanners) = split(/;/, $line, 3); $suPlanners =~ s/^\s+|\s+$//g; my $assoc2 = $spec ? "<association> <assocType>mdmref:nfgngSpec</assocType> <cm_authority> <name>$spec</name> <createIfNotExists>true</createIfNotExists> </cm_authority> </association>" : ""; my $assoc3 = $suPlanners ? "<association> <assocType>mdmref:nfgngSUPlanners</assocType> <cm_authority> <name>$suPlanners</name> <createIfNotExists>true</createIfNotExists> </cm_authority> </association>" : ""; my $str = " <object> <associations> <association> <assocType>mdmref:nfgngClasterCategory</assocType> <targetNodeType>mdmref:nfgClusterCategory</targetNodeType> <targetNodePath>/app:company_home/st:sites/cm:rkk-mdm/cm:dataLists/cm:mdm-nfg-cluster-category</targetNodePath> <propId>mdmref:clusterCategory</propId> <propValue>$clusterCategory</propValue> </association> $assoc2 $assoc3 </associations> </object>\n"; push @lines, $str; } # print join("\n", @lines); open my $out_handle, ">", "out.xml" or die "Could not open out.xml: $!"; print $out_handle (@lines); |
This script is written in perl. To run it on Windows, you need to install some environment, for example Strawberry Perl for Windows (http://strawberryperl.com/), and run the script on the command line:
perl <your_script.pl>
a. Create journals\folder for objects Journals-folder-beans.xml
b. Create xni (xml node importer) objects, which include data for import. car-xni-data.xml
<bean id="car.module-component.car-xni-data" parent="project.import-spaces.1-0-1"> <property name="name" value="car-xni-data" /> <property name="description" value="Car xni data" /> <property name="executeOnceOnly" value="false" /> <property name="bootstrapViews"> <list> <props> <prop key="uuidBinding">UPDATE_EXISTING</prop> <prop key="path">/app:company_home/app:dictionary</prop> <prop key="location">alfresco/module/project-repo/bootstrap/data/mdm/car-xni-data.xml</prop> <prop key="encoding">UTF-8</prop> </props> </list> </property> </bean> |
If objects have associations, then you need to specify the dependencies, for the correct creation of associations. For specify dependecy create association 'xni:dependsOn' between xni objects. car-xni-data-assocs.xml
<bean id="car.module-component.car-xni-data-assocs" parent="project.import-spaces.1-0-1"> <property name="name" value="car-xni-data-assocs" /> <property name="description" value="Car xni data assocs - dependency" /> <property name="dependsOn" ref="car.module-component.car-xni-data"/> <property name="bootstrapViews"> <list> <props> <prop key="path">/app:company_home/app:dictionary</prop> <prop key="location">alfresco/module/unilever-repo/bootstrap/data/mdm/mdm-xni-data-assocs.xml</prop> <prop key="encoding">UTF-8</prop> </props> </list> </property> </bean> |
c. Start alfresco
d. Go to http://{host}:{port}/alfresco/s/import/xml-to-node/ This page allows you to export the required objects and monitor the status of the export.
Import of data logging to alfresco.log. Example:
5247 12:28:27,086 WARN [org.alfresco.repo.jscript.ScriptLogger] xml-to-node-parser.js Start parse... type: auto:engine 5248 12:28:27,089 WARN [org.alfresco.repo.jscript.ScriptLogger] xml-to-node-parser.js Found 11 objects. Parse in progress... 5249 12:28:27,442 WARN [org.alfresco.repo.jscript.ScriptLogger] xml-to-node-parser.js Parse ends in 0:00 (min:sec) (357 ms) 5250 12:28:27,479 WARN [org.alfresco.repo.jscript.ScriptLogger] xml-to-node-parser.js Start parse... type: auto:car 5251 12:28:27,479 WARN [org.alfresco.repo.jscript.ScriptLogger] xml-to-node-parser.js Found 29 objects. Parse in progress... 5252 12:28:29,178 WARN [org.alfresco.repo.jscript.ScriptLogger] xml-to-node-parser.js Parse ends in 0:02 (min:sec) (1701 ms) |
Imported data:
Deletion actions deleting all data in root path of xni object and change status to "Ready".
Select the item, which you want to delete and confirm the deletion.
To transfer data from XLS / XLSX / other table format using LibreOffice Calc / OpenOffice Calc, see next guide (see best answer): https://stackoverflow.com/questions/32817081/convert-calcexcel-data-in-xml-in-openoffice