Addon: Data import

paddu.bits · June 21, 2019, 9:14pm

Does this addon provide the feature for exporting data into excel file?

mario · June 23, 2019, 9:16am

no - this addon does not provide any kind of export functionality.

CUBA covers this area quite well out of the box with the generic excel action, the reports engine and the entity inspector import / export functionality.

Bye
Mario

mario · September 20, 2019, 7:03pm

hi,

data-import application component is now ready for @CubaPlatform 7.1 in version 0.10.0. Have fun updating! GitHub - mariodavid/cuba-component-data-import: CUBA component for easy data import

mario · November 16, 2019, 10:13am

hi,

data-import 0.11.0 for @CubaPlatform is released with some additional data types support. Check it out here: GitHub - mariodavid/cuba-component-data-import: CUBA component for easy data import

mario · December 7, 2019, 10:09am

hi,

data-import 0.11.1 bugfix release is now available. Have fun!

Cheers
Mario

mario · March 13, 2020, 6:37pm

hi,

a new version of data-import addon for @CubaPlatform is now available with version 7.2 support and an updated underlying Apache POI library. Check out 0.12.0 here: GitHub - mariodavid/cuba-component-data-import: CUBA component for easy data import

Cheers
Mario

charles.njihia · April 12, 2020, 10:58pm

Dear Mario,
Thanks for the update. I have tried the new data-import addon on 7.2 but am getting a strange error, while importing data for an entity, a localdatetime column fails with the error being “: may not be null, provided value: ‘null’”. Am definitely passing data in the xlsx file that am importing. Am doing a direct attribute load to the said column. Are there any special considerations to be made for localdatetime columns?

Regards,

Charles

willi · May 8, 2020, 6:53am

Hello Mario,
nice and useful component, works well so far
Working on my first CUBA-Proj., fighting all those obstacles and troubles, nevertheless meanwhile on a good way, xls import is a big and important aspect (approx. 1000 files a month).
Intending to use techniques/parts of your solution, two major hooks for me now:

MS SQL is not supported (I tested it and faced several probs, e.g. Datatype BigDec); what would be your proposal for a workaround, or is there a rel.plan for?
I hardly need some kind of bulk-import; which approach would you recommend (or am I too legacy minded in terms of automatically read data from the client)?

Regards
Willi

mario · May 9, 2020, 5:57am

Hi Willi,

thanks and glad you like it

Regarding MSSQL - you are right: the scripts for the addons tables itself are not (yet) part of the release. But Studio will still generate the correct SQL scripts for those tables. Or are you referring to that afterwards there are some problems (like you mentioned with BigDecimal?).

Regarding Bulk-Import:
You mean that you would like to automatically trigger a data import on a scheduled basis where before you download the file automatically and then run the import?

In this case probably The DataImportAPI abstraction is your friend (GitHub - mariodavid/cuba-component-data-import: CUBA component for easy data import). With this API you programmatically can trigger imports for preconfigured import configurations. Like in this example MlbTeamImportServiceBean.java I triggered the import programmatically. In this case it was based on a button in the UI, but of course you can chose a different invocation point.

For automatic execution, you can use CUBAs Scheduled Tasks mechanism to:

download the file from some source (HTTP, FTP etc.)
store file as a File descriptor in CUBA
trigger data import via data import API

I hope this helps as a starting point.

Bye
Mario

mario · May 9, 2020, 6:43am

Hi,

I’m not sure it is related to the latest update, but right now I think LocalDateTime will not work out of the box. You can solve it via a custom binding script (GitHub - mariodavid/cuba-component-data-import: CUBA component for easy data import) for the column that contains the LocalDateTime:

import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;

return LocalDateTime.parse(
    rawValue, 
    DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm")
)

In yyyy-MM-dd HH:mm you put your pattern of how the date is formatted (options see here:
DateTimeFormatter (Java Platform SE 8 ))

I added an example for this custom LocalDateTime in here: cuba-example-using-data-import/example-data/datatype-example/localDataTime-example at master · mariodavid/cuba-example-using-data-import · GitHub so you can play around with it.

If you would like to make a contribution and add it to the addon, there was a PR for LocalDate lately. LocalDateTime should be similar I guess (Add parsing of Long and LocalDate attributes by glundy · Pull Request #148 · mariodavid/cuba-component-data-import · GitHub).

Cheers
Mario

willi · May 9, 2020, 10:01am

Hi Mario,
thx for your fast and detailed response.
MSSQL: What I’ve tested:

created an simple enitity via designer with strings, bigdec, date
cretaed a import config /w minimal changes (date mask)
import of some records: strings ok, date and bigdec empty in the data table, so it’s your second suggestion
Bulk-Import:
the final approach might be a task triggered automatic import; for the first step it would be ok to read an import folder, cloud- or local based and start the import manually as a bulk (having those files in a table, marking them and start, I guess)
the file structure would be absolutly static (finally there were a couple of referenced entities also, what I haven’t tested yet) and it doesn’t require any flexibility. So it would be acceptable to do some addional coding if nessesary …so I think I should examine your hints.

Additional question:

is it possible to handle multisheet/-tabbed XLS’s ?

If those functionalities basically are working, I would do the detailed work (facing a violent time-pressure (as always ), I should avoid doing evaluations ending in no go’s)

Long story short: Datatypes are critical, multisheet is important, autoimport would work somehow

Btw: using CUBA 7.2.4
(…and a java beginner also…)

Thanks a lot,
Willi

mario · May 9, 2020, 10:26am

Multi tab is currently not possible out of the box. Im also not sure how this should look like. I would assume you would need to have multiple import configurations. When you explain the use case in more detail I might be able to help

Also i’m not auite sure what you mean by bulk import. Multiple files at once, multiple records?

Cheers
Mario

willi · May 10, 2020, 10:02am

Hello,
for more transparency:

multi tab: data comes along within two tabs per xls, structured as a 1:n relation. One tab describes one company (vertical oriented attr list), the second consists of n vehicles, 1 per row /w several attr horizontal oriented. For sure, that generates a couple of questions, not only multiple tabs, but different structures, building up references, differentiate if owner is already imported (adding vehicles only),…
After thinking twice, at the moment I’m tending to build some external scripts/procs/makros to separate and restructure the xls-stuff, doing the imports as one-tabbed XLSs for owner and vehicles separated (relating the master-details through naming conventions or temp-key generation to some import-tables and transfer them into the app-tables/aggregates through app functionalities/tasks (additionally there were a couple of relations to master data) - missing aesthetics at all, I know but that’s live with evolving legacy stuff ) - so implementing all those detail-import aspects within the app would generate quite plenty of efforts, leading to a very specific solution. So multi-tab (and it’s details) shouldn’t be a show stopper
bulk import: means to (semi-)automatically import n of described files residing in a certain upload-folder, so the import logic may be triggered from iterating through a visible table or via task

servus,
Willi

stukalov · May 10, 2020, 10:29am

@willi,

It will never be enough, more and more people will report their cases and you can imagine how diverse this area is, right? Following the way of making this amazing add-on ‘good for all’ actually will turn it into a bunch of unsupported features. From my perspective this add-on is absolutely great in terms of the functionality it provides; it should not go far away beyond some basic import cases - as this one can cover around 80% of cases for data import. All custom or non-standard cases should be part for your own application with your own implementation.

As for your requirements:

I think you could solve the problem easily on your side - just simply pre-process your files to conform to the existing solution. This would be easiest and cheapest solution. Just make a script to combine all sheets into one with an additional tenantId column. Same can be done with a few workbooks.
If you still want to have this in the add-on, pay your attention to the fact that it was provided by community and you are able to contribute it. But remember, whatever you make publicly available become a long term game and you will need to support it nearly forever .

Regards,
Aleksey

willi · May 10, 2020, 11:46am

Hi,
totally agreed. It wasn’t my original intention to dig so deep…and not my understanding consuming support this way! (I built up my first CUBA-app right now with really a bunch of functionalities without penetrating ANYone).

Regards,
Willi

mario · May 10, 2020, 11:59am

Hi @willi,

In general from your rough description it is possible to implement with some custom logic in your app before and after the import.

Regarding your multi tab description: it seems that a couple of your described lookups etc. as well as the master detail scenarios are exceeding the limitations of the addon. One fundamental limitation is automatic import the 1 and the N of the 1:N relationship in one import step. But you can import the 1 side first and then in another import configuration the N part.

I would suggest you familiarize yourself with the various script injection points as well as the other advanced topic like unique behavior settings.

Also in your described case you should look into this section of the readme: cuba-component-data-import/README.md at master · mariodavid/cuba-component-data-import · GitHub

But perhaps it might make sense to be able to configure which excel tab is used for import.

Cheers
Mario

willi · May 10, 2020, 12:14pm

Hi,
yes, as mentioned, but back to start: MS SQL and Datatypes: I couldn’t manage to have BigDec and Dates imported (DB fields are NULL, rest ok), any hints, workaround?

Thanks and regs,
Willi

mario · May 10, 2020, 12:19pm

Does ist work if you try to apply the same import against Hsqldb?

In order to help you concretely you should provide a test app with a test import file and an.impprt configuration. Then I can take a look.

Cheers
Mario

charles.njihia · May 11, 2020, 10:15am

Dear Mario,
Thanks for your response. I will test it out and will come back on the contribution aspect.

Regards,

Charles

fabrizio.demassis · April 5, 2022, 8:28am

Hello Mario some questions on this add-on (Using version 0.7.0 on 6.10 Cuba platform):

how can I modify menu(s)? (change or add language, remove demo-menu etc.)
I can use log from add-on? I can browse from menu but with no data of import operations
similar questions (nr. 1) for Wizard add-on

It seems code is pre-compiled in add-on, even if you install in development environment.

From: web.xml

 <context-param>
        <param-name>appComponents</param-name>
        <param-value>com.haulmont.cuba com.haulmont.reports com.haulmont.fts com.haulmont.charts
            com.haulmont.addon.emailtemplates de.diedavids.cuba.wizard com.haulmont.addon.imap
            it.nexbit.cuba.security.forgotpassword it.nexbit.cuba.translationit de.diedavids.cuba.dataimport</param-value>
    </context-param>

Thank you in advance for your response
F. De Massis