cogWizard to perform an Excel / CSV import with Batch Import API in an application created with App Within Minutes.
TypeXAR
Developed by

Anca Paula Luca, Ludovic Dubost

Active Installs50
Rating
Rate!
1 Votes
LicenseGNU Lesser General Public License 2.1
Installable with the Extension Manager

Description

This application provides a default interface for the Batch Import API. This interface allows to import data from a file into an application created with App Within Minutes.

This application needs the Batch Import API to be installed and its features (import of Excel and/or CSV) depend on the configuration of the Batch Import API.

How to use

Go on the Home page of the application created with AppWithinMinutes (from the "Applications" panel on the right) and add, at the end of the URL ?sheet=BatchImport.BatchImportSheet. This will launch the import wizard in the context of the current application, and any data related to the wizard (like the source file attachments and persisted configuration) will be stored in the application WebHome.

Follow the steps of the wizard to perform your batch import of data.

If you need to delete all the data in the application (produced by the import or not), you can use ?sheet=BatchImport.BatchImportDeleteSheet at the end of the URL of the application home page. 

Note that all users which have the right to create documents in the application space or delete documents in the application space will be able to perform import or delete of data: batch import wizard does not enforce any rights, it only provides automation of import / delete.

Example

Here is an example of the import, performed on data based on http://www.danceireland.ie/downloads/library/Resource-room-library-catalogue.xls , during which we will import a library catalogue from excel to an application in the wiki. Since the initial excel file was designed for print, we touched it a bit to make it suitable for import. The modified version is here .

First, we create the application with a text field for the author, a number field for the number of copies in store, a date field for the publication year (whose format we configure to display only year), and a boolean field to mark whether the item can be loaned or can only be consulted in the library. We will store the title of the publication in the document title, and the id of the publication in the page name (since id is unique and page name is also an identifier of the item).

createApplication.png

Then, as explained in the "How to use" section above, we proceed to the import: in the first step of the wizard, we upload the xls file and select it:

selectFile.png

selectFile-Uploaded.png

In the second step of the wizard, we can choose the mapping between the fields of the XWiki class of the application and the columns of the file to import (note that, compared to the initial xls, we moved the DVD sheet in the first position, so we're importing DVDs now):

columnMapping.png

Then, a preview of the mapping is displayed (the first ten rows), to show how will the data from the file be processed and parsed according to the types of the fields of the XWiki Class (since conversion needs to be possible automatically): 

mappingPreview.png

Upon confirming of the mapping, we will be able to choose the overwrite options for the import: since, in the mapping step, we chose a file column to be mapped on the document name, we need to specify now how should the import behave when two rows in the file have the same value for the document name column. Also, if a page with the same name already exists in the wiki, we need to specify how should the import wizard behave in such situation.

overwriteOptions.png

The next step is an import simulation for the whole source file, showing what will happen during the actual import:

importSimulation.png

And finally, after the import simulation we can actually perform the import (using the button at the bottom of the import simulation log) and see the data imported in the application, on the application home table:

importedData.png

To show error handling and some special configurations of the import, we will now import the "Guides and Publications" from the excel in the same application and, since it has pretty irregular dates, we will import it as a CSV file this time. 

If the excel import does not work as expected, especially for numbers and dates, CSV export and import of the CSV file in the wiki can help, since it forces the serialization to string of some values which otherwise will be displayed differently than they are stored by in the excel file, thus misleading the user about the actual data format.

First we export the Guides and Publications sheet in a CSV file from the Excel tool we use (resulting this file) and then we upload it in the wizard as we did for the previous file:

importCsv.png

Upon column mapping we can see that the old mapping was stored, as much as possible, and displayed as defaults for this new import (the column names for author and publication date differ from the DVDs to Guides and Publication tab).

mappingSave.png

Since the dates of the Guides and publications are irregular, as we can see in this screenshot of the excel tool:

excelData.png

... we will also set the date format in the column mapping screen, thus helping the import tool parse these "non-conform" dates:

dateFormat.png

On the mapping preview step we only see the first 10 lines, so we cannot see the dates being parsed correctly as "MMM-yy":

mappingPreviewGP.png

... but on the simulation step we can see that the only errors are coming from 4 rows which really have unparsable dates:

importSimulationWithError.png

Note that these rows with errors are completely ignored on import, but could be imported in a second import, removing the date mapping and configuring overwrite so that the existing documents from the previous import are not modified, or they could be corrected in the initial excel so that they can be imported along with the rest of the file.

After the perform of the import in the last step of the wizard, we can see that the couple of dates in the "MMM-yy" format we saw in the initial excel are properly parsed to the corresponding dates in the wiki:

importedDateFormat.png

Finally, in the home page of our library application, we can see all the data, DVD and Guides and Publications together:

allImportedData.png

Tested on

This extension has been tested with the following configurations.

Extension VersionXWiki FlavorNotes
1.1XWiki Enterprise 4.4-rc-1

Needs workaround for versions affected by XWIKI-7942

Prerequisites & Installation Instructions

We recommend using the Extension Manager to install this extension (Make sure that the text "Installable with the Extension Manager" is displayed at the top right location on this page to know if this extension can be installed with the Extension Manager). Note that installing Extensions when being offline is currently not supported and you'd need to use some complex manual method.

You can also use the following manual method, which is useful if this extension cannot be installed with the Extension Manager or if you're using an old version of XWiki that doesn't have the Extension Manager:

  1. Log in the wiki with a user having Administration rights
  2. Go to the Administration page and select the Import category
  3. Follow the on-screen instructions to upload the downloaded XAR
  4. Click on the uploaded XAR and follow the instructions
  5. You'll also need to install all dependent Extensions that are not already installed in your wiki
  • Go to wiki Administration -> Localization and add BatchImport.Translations to the i18n bundles.

Release Notes

v1.1

  • Update parent POM to XWiki Commons 4.5.4

Dependencies

Dependencies for this extension (org.xwiki.contrib:xwiki-batchimport-application 1.3):

Tags: import
Created by Anca Luca on 2012/12/27 18:44
    

Get Connected