Addon: Data import

Hi,

@stukalov and myself created a cuba-application component that you can use. It is in preview mode currently. Before releasing it “officially” i would like to get your feedback on it. It is open source (Apache 2 license) and you can find it on Github:

If you have any thoughts on that, like bug, feature requests etc. I would love to hear from you.

I’ll just copy over the README.md so you get an impression on what it does:

CUBA Platform Component - Data import

This application component let’s you easily import data into your application from various sources.

Installation

  1. Add the following maven repository https://dl.bintray.com/mariodavid/cuba-components to the build.gradle of your CUBA application:

     buildscript {
       repositories {
         maven {
           url  "https://dl.bintray.com/mariodavid/cuba-components"
         }
       }
    }
    
  2. Select a version of the add-on which is compatible with the platform version used in your project:

Platform Version Add-on Version
6.8.x 0.1.x

The latest version is: Download

Add custom application component to your project:

  • Artifact group: de.diedavids.cuba.dataimport
  • Artifact name: data-import-global
  • Version: add-on version

Using the application component

The data-import application component helps you import data into your system from different files.

Currently the following file-types are supported:

  • Excel .xlsx
  • comma separated values .csv

In order to configure various import options, there is a UI based configuration possibility to define

  • which entity will be imported
  • which columns maps to which entity attribute
  • format configuration for dates, boolean values etc.
  • unique configurations and how to deal with these situations
  • custom groovy scripts for attributes to value mapping

There are two modes of using the data-import application component. The first one is an interactive UI wizard, which
will guide the user directly through the process of importing the data.

The second mode is, that the import configuration can be pre-defined by a developer / administrator of the system.
The end-user of the system can reuse this configurations and just uploads the file that should get imported.

Import wizard

The import wizard allows the user to interactively go through the import process and configure the above mentioned settings
for the import execution. It can be found in the main menu: Administration > Data Import > Import Wizard

Step 1: Upload file

Step 2: Configure entity mapping

The second step in the wizard allows the user to configure which columns of the import file will be mapped to which entity
attributes. The system makes suggestions based on the similarities of the entity attribute names and the column headers
in the import file, but this can be adjusted by the user if needed.

Step 3: Import Configuration

In the import configuration it is possible to define certain Format options as well as the unique configurations for this import.

Step 4: Import Preview

The last step will preview the data that was received from the import file. With “Start Import” the import process
will be triggered. Afterwards the user will see a summary of how many entities were imported correctly.

7 Likes

version 0.2.0 of “data-import” is out and waiting for you. Features: Transaction strategies, unique Configurations, Pre-Commit Script, Oracle, Postgres & MySQL support. And a proper README :slight_smile:

Check it out:

3 Likes

version 0.3.0 of app component “data-import” is out and waiting for you. Features: support for JSON & XML import files; BigDecimal auto binding; Bugfixes.

Check it out: https://github.com/mariodavid/cuba-component-data-import

1 Like

Hi Mario,

Is this supported for Oracle?

Because I am able to see in your comment but not in README document.

Can you clarify the same?

Regards,
Velankanni J

Hi Velankanni J,

If you have Oracle 12.2 then it will work. All earlier version of Oracle doesn’t support table column name length greater 30 characters.
@mario 's add-on has some columns where the name is longer than 30 characters, so it will not work with older oracle versions.

I use it with orache 12.2 and it works.

CU
Steven

Hi,

True, I initially added oracle support, but removed it in 0.4.0 because of the 30 char limit (see https://github.com/mariodavid/cuba-component-data-import/blob/master/CHANGELOG.md#040---26052018)

Will be added somewhere before 1.0.0, but as it requires a manual testing effort and maintanance overhead during initial development, i currently did not do the refactoring.

Hi,

Thank you both @s-m-k and @mario for your prompt response.
Currently I tried in Oracle 12 c and i got 30 char error and hence asked. :slight_smile:

Regards,
Velankanni J

hi,

version 0.6.0 of app component “data-import” for @CubaPlatform is out. Features: Abort on unique violation & association binding for different datatypes. Check it out: https://github.com/mariodavid/cuba-component-data-import

Bye
Mario

Hi,

While using the import-addon for tables with complex-key, the import doesn’t happen.
The entity is taken from the existing oracle-table in additional data source.
During import configuration, the automatic mapping of key attributes didn’t happen so i mapped it manually using association type and selected id and column .
But when i import the data, getting this error
Kindly assist, let me know if any additional steps needs to be taken while working with complex key.

JPQLException: 
Exception Description: Problem compiling [SELECT e FROM cdc$AplFfCdcDataImportCompKey e where e.itemClass = :propertyValue]. 
[14, 43] The abstract schema type 'cdc$AplFfCdcDataImportCompKey' is unknown.
[52, 63] The state field path 'e.itemClass' cannot be resolved to a valid type.
org.eclipse.persistence.exceptions.JPQLException: 
Exception Description: Problem compiling [SELECT e FROM cdc$AplFfCdcDataImportCompKey e where e.itemClass = :propertyValue]. 
[14, 43] The abstract schema type 'cdc$AplFfCdcDataImportCompKey' is unknown.
[52, 63] The state field path 'e.itemClass' cannot be resolved to a valid type.
	at com.haulmont.cuba.core.sys.ServiceInterceptor.aroundInvoke(ServiceInterceptor.java:129)
	at sun.reflect.GeneratedMethodAccessor124.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:629)
	at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:618)
	at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:70)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:168)
	at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
	at com.sun.proxy.$Proxy263.doDataImport(Unknown Source)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at com.haulmont.cuba.core.sys.remoting.LocalServiceInvokerImpl.invoke(LocalServiceInvokerImpl.java:94)
	at com.haulmont.cuba.web.sys.remoting.LocalServiceProxy$LocalServiceInvocationHandler.invoke(LocalServiceProxy.java:154)
	at com.sun.proxy.$Proxy59.doDataImport(Unknown Source)
	at de.diedavids.cuba.dataimport.service.GenericDataImporterService$doDataImport.call(Unknown Source)
	at de.diedavids.cuba.dataimport.web.importwizard.ImportWithImportConfigurationWizard.startImport(ImportWithImportConfigurationWizard.groovy:165)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at com.haulmont.cuba.gui.xml.DeclarativeAction.actionPerform(DeclarativeAction.java:92)
	at com.haulmont.cuba.web.gui.components.WebButton.performAction(WebButton.java:46)
	at com.haulmont.cuba.web.gui.components.WebButton.lambda$new$61446b05$1(WebButton.java:38)
	at sun.reflect.GeneratedMethodAccessor206.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at com.vaadin.event.ListenerMethod.receiveEvent(ListenerMethod.java:510)
	at com.vaadin.event.EventRouter.fireEvent(EventRouter.java:200)
	at com.vaadin.event.EventRouter.fireEvent(EventRouter.java:163)
	at com.vaadin.server.AbstractClientConnector.fireEvent(AbstractClientConnector.java:1037)
	at com.vaadin.ui.Button.fireClick(Button.java:377)
	at com.haulmont.cuba.web.toolkit.ui.CubaButton.fireClick(CubaButton.java:54)
	at com.vaadin.ui.Button$1.click(Button.java:54)
	at sun.reflect.GeneratedMethodAccessor221.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at com.vaadin.server.ServerRpcManager.applyInvocation(ServerRpcManager.java:158)
	at com.vaadin.server.ServerRpcManager.applyInvocation(ServerRpcManager.java:119)
	at com.vaadin.server.communication.ServerRpcHandler.handleInvocation(ServerRpcHandler.java:444)
	at com.vaadin.server.communication.ServerRpcHandler.handleInvocations(ServerRpcHandler.java:409)
	at com.vaadin.server.communication.ServerRpcHandler.handleRpc(ServerRpcHandler.java:274)
	at com.vaadin.server.communication.UidlRequestHandler.synchronizedHandleRequest(UidlRequestHandler.java:90)
	at com.vaadin.server.SynchronizedRequestHandler.handleRequest(SynchronizedRequestHandler.java:41)
	at com.vaadin.server.VaadinService.handleRequest(VaadinService.java:1435)
	at com.vaadin.server.VaadinServlet.service(VaadinServlet.java:361)
	at com.haulmont.cuba.web.sys.CubaApplicationServlet.serviceAppRequest(CubaApplicationServlet.java:312)
	at com.haulmont.cuba.web.sys.CubaApplicationServlet.service(CubaApplicationServlet.java:203)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:742)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.springframework.web.filter.CompositeFilter$VirtualFilterChain.doFilter(CompositeFilter.java:107)
	at org.springframework.web.filter.CompositeFilter.doFilter(CompositeFilter.java:73)
	at com.haulmont.cuba.web.sys.CubaHttpFilter.doFilter(CubaHttpFilter.java:107)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:199)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:478)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:81)
	at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:650)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342)
	at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:803)
	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:868)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1459)
	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
	at java.lang.Thread.run(Thread.java:748)

hi,

i’ve neither tried the data-import with an additional datastore, nor with composite keys - so anything might have happened here.

Feel free to create two issues over here: https://github.com/mariodavid/cuba-component-data-import/issues, where one deals with composite keys and another one with the additional data store. The issues should contain:

  • test project that has a domain model representing the problem
  • import configuration definition as a JSON export from Entity inspector
  • example data import file that shows the problem

That would great. Thanks!

Bye
Mario

hi,

version 0.7.0 of app component “data-import” for @CubaPlatform is out. Features: CUBA 6.10 support. Check it out: https://github.com/mariodavid/cuba-component-data-import

Thanks Mario, The additional datastore doesnt look like the problem here.
I tried using the add-on for a table which contained simple primary key and there were no issues.
I will create the issue related to complex key.

Regards,
Aruna

Hello Mario, I am trying to use a style provider for my table with the @WithImport annotation on it.

The issue is, as soon as I do this, my import button disappears. Do you have any advice for working around this situation?

@WithImport(listComponent = "jobSeekersTable")
public class JobSeekerBrowse extends AnnotatableAbstractLookup {

@Inject
private Table < JobSeeker > jobSeekersTable;

@Override
public void init(Map < String, Object > params) {
    jobSeekersTable.setStyleProvider((entity, property) -> {
        if (property == null) {
            return null;
        }
        //make cell red if null
        else if (property.equals("gender"))
            if (entity.getGender() == null) {
                return "data-warning";
            } else {
                return null;
            }

Update: of course I figure it out as soon as I post.

@WithImport(listComponent = "jobSeekersTable")
public class JobSeekerBrowse extends AnnotatableAbstractLookup {

@Inject
private Table < JobSeeker > jobSeekersTable;

@Override
public void init(Map < String, Object > params) {
///solution (seems to work) 
    super.init(params);
    browseAnnotationDispatcher.executeInit(this, params);
//end solution
    jobSeekersTable.setStyleProvider((entity, property) -> {
        if (property == null) {
            return null;
        }
        //make cell red if null
        else if (property.equals("gender"))
            if (entity.getGender() == null) {
                return "data-warning";
            } else {
                return null;
            }

Hi,

Yes thats it. It actually only should need super.init…

It is based on how https://github.com/balvi/cuba-component-declarative-controllers is implemented…

Bye
Mario

1 Like

Thanks! I will dump that extra line. This add-on has been doing some pretty heavy lifting in production. I have a client that needs to upload an excel file once a week from a vendor and this has worked flawlessly for weeks now. It was formerly handled by importing directly into the database via SSMS. This of course meant that someone more technical had to be responsible for it.

Great work!

Hi,

Thanks for the info. Good to know that someone is actually using it :slight_smile:

What size is the file - more like 100 lines or 100000 lines? Did you experienced any performance problems with it so far?

Bye

Between 2500-3000 rows. It does hang after the user begins the import, but it always works. A cool feature would be some kind of spinner or infinite progress bar letting them know not to smash the import button over and over. :slight_smile: but that is just a training issue.

the time it takes to update the table was comparable to doing so via ssms.

  • As for issues, non that could not be overcome. I have had issues with using enumerations (string) when they have spaces in them, so I just made them associations.
  • Also issues with dates in excel, which is was overcome. Excel with show a “short date” as “3/3/2018” but after the import it displays as “3/3/18” so you just have to make sure to set the date format to what you see in the preview, vs what excel is showing.

I have only run out of memory once on an enormous test excel file. If that should happen, I just coach them to split the file in two or more.

I am having an issue where I receive an “unfetched attribute” error only when I use “Update Existing Entity” in the Unique Configuration Editor.

IllegalStateException: Cannot get unfetched attribute [sector] from detached object 
com.company.sckywfdb.entity.Organization-30994 [detached]. 

All the views are correct. This issue doesn’t happen if with the “skip if exists” option, or if I just allow duplicates.

The entity is very simple: organizationName (direct), physicalZip (direct), and sector (association)

I’m not sure what is different behind the scenes when using the “Unique Configuration Editor” in Update Existing mode when it comes to associations.

In this context, “sector” has been added, and I needed to update about 10k records. I’m just going to handle it with a SQL script and a staging table for now, but I thought drop in the report.

Hi Mario

I want to put the values of the first and second columns of the excel table together, and then put the values into the properties of an entity. How do you write the Pre-Commit script?

Regards,
Chenxi

hi,

have you looked at the documentation about the pre commit script? - https://github.com/mariodavid/cuba-component-data-import#pre-commit-script there is an example available.

Probably something like this in your case would do the trick:

entity.myAttribute1 = dataRow.myAttribute.split(" ")[0]
entity.myAttribute2 = dataRow.myAttribute.split(" ")[1]
return true

Bye
Mario