Hi,
Is there any csv import project to refer.
Looks good. Is this a new add-on to be published soon as I do not see it in the add-on directory?
regards
Mortoza
It’s incubating, probably will be published next year.
Hi Konstantin
I see the data tools addon is published but it is only for “to/from JSON”. Could you please let us know when this will support csv and excel file formats? Moreover, a wizard to configure the import of data will greatly enhance user productivity, at least something like Mario created add-on in CUBA PLATFORM.
@adnan.khan
Meanwhile, you can use apache.poi.
Hi Mortoza,
The “Export/Import” actions of EntityInspector (Data Tools add-on) use the EntityImportExport bean which is located in the UI core module and available to all projects. This bean can export/import graphs of entities to/from JSON and can be used to transfer data between application instances.
The above mentioned Data Import add-on is able to work with CSV and XLSX, but it has no UI, you should configure it in the application code.
Regards,
Konstantin
1 Like
Thanks, Konstantin for the update. I see that API in the core but nothing much about excel, csv etc. If you please direct us to the relevant documentation, sample app etc. that would be very helpful.
Hi Konstantin
I tried to use it but couldn’t find the necessary API “ImportConfiguration”. What is the import package where it is located? I’m trying from one of my composite projects.
It’s from io.jmix.dataimport.configuration package.
Have you added implementation 'io.jmix.dataimport:jmix-dataimport-starter' to build.gradle as described at https://github.com/jmix-framework/jmix/tree/master/jmix-dataimport#installation ?
Hi Konstantin
Yes, I have installed the add-on to the addon I am using in my composite project as well as Have you added implementation 'io.jmix.dataimport:jmix-dataimport-starter' to build.gradle
But I still do not find the package, see here how it looks like:
Try to open your add-on in a separate IDE session.
Yes! A separate IDE session helped in reaching out using the API.
Now I’ll need help with how to use the following execution process as it seems the IDE is not recognizing properly though I have followed the documentations.
ImportResult importData(importConfiguration, file);
Here is my complete code:
@Subscribe("fileUploadBtn")
public void onFileUploadBtnFileUploadSucceeded(final FileUploadSucceededEvent<FileStorageUploadField> event) {
if (event.getReceiver() instanceof FileTemporaryStorageBuffer buffer) {
UUID fileId = buffer.getFileData().getFileInfo().getId();
log.info("FileId: " + fileId);
File file = temporaryStorage.getFile(fileId);
log.info("File from temp storage: " + file);
ImportConfiguration importConfiguration = ImportConfiguration.builder(Customer.class, InputDataFormat.XLSX)
.addSimplePropertyMapping("orderNo", "Order No")
.addSimplePropertyMapping("orderDate", "Order Date")
.addSimplePropertyMapping("amount", "Order Amount")
.addReferencePropertyMapping("customer", "customer Code", "customerCode", ReferenceImportPolicy.IGNORE_IF_MISSING)
.addUniqueEntityConfiguration(DuplicateEntityPolicy.UPDATE, "orderNo", "orderDate", "customer.customerCode")
.withDateFormat("dd/MM/yyyy HH:mm")
.withTransactionStrategy(ImportTransactionStrategy.TRANSACTION_PER_ENTITY)
.build();
ImportResult importData(importConfiguration, file);
temporaryStorage.deleteFile(fileId);
log.info("File is deleted from temp storage: " + file);
}
}
I have created a sample application as attached if you need it.
excelUploadSample.zip (183.2 KB)
Hi,
If you’re talking about this section of the documentation, then the following line
ImportResult importData(ImportConfiguration configuration, byte[] content);
is not a code sample that you must copy and paste into your code. This section just lists methods of the DataImporter bean that you can use in your code.
You need to inject the DataImporter bean and then invoke the importData method of that bean, something like this:
@Autowired
private DataImporter dataImporter;
//.....
ImportResult importResult = dataImporter.importData(...);
Hi Maxim
Thank you for your help. Here is the updated code:
if (event.getReceiver() instanceof FileTemporaryStorageBuffer buffer) {
UUID fileId = buffer.getFileData().getFileInfo().getId();
log.info("FileId: " + fileId);
File file = temporaryStorage.getFile(fileId);
log.info("File from temp storage: " + file);
ImportConfiguration importConfiguration = ImportConfiguration.builder(Customer.class, InputDataFormat.XLSX)
.addSimplePropertyMapping("orderNo", "Order No")
.addSimplePropertyMapping("orderDate", "Order Date")
.addSimplePropertyMapping("amount", "Order Amount")
.addReferencePropertyMapping("customer", "customer Code", "customerCode", ReferenceImportPolicy.IGNORE_IF_MISSING)
.addUniqueEntityConfiguration(DuplicateEntityPolicy.UPDATE, "orderNo", "orderDate", "customer.customerCode")
.withDateFormat("yyyy/MM/dd")
.withTransactionStrategy(ImportTransactionStrategy.TRANSACTION_PER_ENTITY)
.build();
try (FileInputStream fileInputStream = new FileInputStream(file)) {
byte[] byteArray = new byte[(int) file.length()];
fileInputStream.read(byteArray);
// Now, byteArray contains the file data in bytes
ImportResult importResult = dataImporter.importData(importConfiguration, byteArray);
temporaryStorage.deleteFile(fileId);
log.info("File is deleted from temp storage: " + file);
} catch (IOException e) {
e.printStackTrace();
}
}
I am getting the following exception though that field exists in the Excel file I am uploading from as attached. I have also attached the sample application.
excelUploadSample.zip (183.1 KB)
order.xlsx (4.9 KB)
Exception:
2023-10-27T23:13:54.803-04:00 ERROR 38909 --- [nio-8080-exec-3] i.j.dataimport.impl.DataImportExecutor : Entity extraction failed for data item: Item index: 2, Data: amount: 154000, orderNo: 1549, customerCode: 2001, orderDate: 2023/06/22
java.lang.IllegalArgumentException: Property 'orderNo' not found in xls_Customer
at io.jmix.core.metamodel.model.impl.MetaClassImpl.getProperty(MetaClassImpl.java:95) ~[jmix-core-2.0.2.jar:na]
at io.jmix.dataimport.property.populator.PropertyMappingContext.getMetaProperty(PropertyMappingContext.java:85) ~[jmix-dataimport-2.0.2.jar:na]
at io.jmix.dataimport.property.populator.impl.SimplePropertyValueProvider.getValue(SimplePropertyValueProvider.java:42) ~[jmix-dataimport-2.0.2.jar:na]
at io.jmix.dataimport.property.populator.impl.PropertyValueProvider.getSimpleValue(PropertyValueProvider.java:52) ~[jmix-dataimport-2.0.2.jar:na]
at io.jmix.dataimport.property.populator.impl.EntityPropertiesPopulatorImpl.populateProperty(EntityPropertiesPopulatorImpl.java:66) ~[jmix-dataimport-2.0.2.jar:na]
at io.jmix.dataimport.property.populator.impl.EntityPropertiesPopulatorImpl.lambda$populateProperties$0(EntityPropertiesPopulatorImpl.java:53) ~[jmix-dataimport-2.0.2.jar:na]
at java.base/java.util.ArrayList.forEach(ArrayList.java:1511) ~[na:na]
at io.jmix.dataimport.property.populator.impl.EntityPropertiesPopulatorImpl.populateProperties(EntityPropertiesPopulatorImpl.java:53) ~[jmix-dataimport-2.0.2.jar:na]
at io.jmix.dataimport.property.populator.impl.EntityPropertiesPopulatorImpl.populateProperties(EntityPropertiesPopulatorImpl.java:44) ~[jmix-dataimport-2.0.2.jar:na]
at io.jmix.dataimport.extractor.entity.impl.EntityExtractorImpl.extractEntity(EntityExtractorImpl.java:45) ~[jmix-dataimport-2.0.2.jar:na]
at io.jmix.dataimport.impl.DataImportExecutor.lambda$importInMultipleTransactions$4(DataImportExecutor.java:200) ~[jmix-dataimport-2.0.2.jar:na]
at java.base/java.util.ArrayList.forEach(ArrayList.java:1511) ~[na:na]
at io.jmix.dataimport.impl.DataImportExecutor.importInMultipleTransactions(DataImportExecutor.java:197) ~[jmix-dataimport-2.0.2.jar:na]
at io.jmix.dataimport.impl.DataImportExecutor.importData(DataImportExecutor.java:107) ~[jmix-dataimport-2.0.2.jar:na]
at io.jmix.dataimport.impl.DataImporterImpl.importData(DataImporterImpl.java:77) ~[jmix-dataimport-2.0.2.jar:na]
at io.jmix.dataimport.impl.DataImporterImpl.importData(DataImporterImpl.java:49) ~[jmix-dataimport-2.0.2.jar:na]
at com.company.exceluploadsample.view.order.OrderListView.onFileUploadBtnFileUploadSucceeded(OrderListView.java:70) ~[main/:na]
It seems that this exception explains what’s wrong. A given entity class doesn’t contain a given attribute.
Hi Maxim
That’s the strange problem. The excel file I am using has that column already (I have uploaded last time), the snapshot of the excel is as follows:
Moreover, the file I am uploading is order.xlsx but in the exception report, it is xls_Customer, any reason?
Do you see any possible cause of this error?
The Customer entity doesn’t have this column. In your import configuration you’re trying to fill the orderNo field of the Customer entity:
ImportConfiguration.builder(Customer.class, InputDataFormat.XLSX)
Hi Maxim
Thank you for your help, I have changed it to Order.class in ImportConfig and it is working partially.
ImportConfiguration importConfiguration = ImportConfiguration.builder(Order.class, InputDataFormat.XLSX)
.addSimplePropertyMapping("orderNo", "Order No")
.addSimplePropertyMapping("orderDate", "Order Date")
.addSimplePropertyMapping("amount", "Order Amount")
.addReferencePropertyMapping("customer", "Customer Code", "customerCode", ReferenceImportPolicy.CREATE_IF_MISSING)
.addUniqueEntityConfiguration(DuplicateEntityPolicy.UPDATE, "orderNo", "orderDate", "customer.customerCode")
.withDateFormat("yyyy/MM/dd")
.withTransactionStrategy(ImportTransactionStrategy.TRANSACTION_PER_ENTITY)
.build();
I see the customer is also created but how can I add the customer name?