One time import journals data from Excel

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:

1. Create a map which Excel will use to build XML file.

For engine and car object map will look like this:

map_engine.xml

map_car.xml

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

2. Compare the data in the Excel file to the map and export data.

Data in excel must be represented as an Excel Table (insert → table)

1. For object include only properties:
  1. open Developer tab of the Ribbon in Excel (to enable this tab, go to Menu – File – Options– Customize Ribbon and select the Developer tab). There is a “Source” button on the Developer tab.
  2. Go to the Source – XML maps and add the XSD schema, saved above. After that, the task pane will look like this:
  3. Now we need to match the columns.
    This can be done by selecting column in Excel table and double click property name in Map. After that, table look like this:
  4. Data can be exported. Click button "export"
  5. Data were exported. (Auto engine compared.xlsx, Engine exported.xml)
2. 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:
  Expand source
```
<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
  Expand source
```
<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
  
  WARN: In XML file with exported data replace all <association_2>, <association_3>, <association_n> to <assocation>.

3. Preparation and setup data for import.

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

3а. Create data files without using MS Excel.

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>

4. Import data \ configure spring-bean.

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

car-xni-data

<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

car-xni-data-assocs

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

5. Logging.

Import of data logging to alfresco.log. Example:

Logs

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:

6. Delete dictionaries objects.

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