Addon: Data import

Add-ons
#15

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!

#16

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

#17

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.

#18

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.

#19

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

#20

hi,

have you looked at the documentation about the pre commit script? - GitHub - mariodavid/cuba-component-data-import: CUBA component for easy data import 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

#21

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

#22

hi @paddu.bits,

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

#23

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

1 Like
#24

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

#25

hi,

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

Cheers
Mario

#26

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

#27

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

#28

Hello Mario,
nice and useful component, works well so far :blush:
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

#29

Hi Willi,

thanks and glad you like it :slight_smile:

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:

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

I hope this helps as a starting point.

Bye
Mario

#30

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

#31

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 :worried:), 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

#32

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 :slight_smile:

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

Cheers
Mario

#33

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 :frowning_face:) - 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

#34

@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:

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

  2. 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 :slight_smile:.

Regards,
Aleksey