Issue Importing Data from CSV to Entity Using EntityDataLoader

Technical Questions
1

Hello Moqui Community,

I’m currently working on importing data from a CSV file into an entity using the EntityDataLoader in Moqui. However, I’m encountering an issue when a double quote (") appears somewhere in the middle of the data values.

Sample data that throws an error while importing

co.example.bi.fact.OrderItemFulfillmentFact
orderId,orderItemSeqId,externalId,orderName,orderTypeId,productStoreId,salesChannelEnumId,entryDate,orderDate,shippingCharges,productId,itemDescription,
FAO10117,101,5669763023132,"#1010101240",SALES_ORDER,STORE,POS_SALES_CHANNEL,1705232954323,1705232896000,,10016,"\"And\" Pride Tank in Grey Mix",

The value \"And\" Pride Tank in Grey Mix, contains the quote(") that causes an issue. And if the value is like "And" Pride Tank in Grey Mix also causes an issue.

Error text:

{'message':'IOException reading next record: java.io.IOException: (line 3) invalid char between encapsulated token and delimiter (line 3) invalid char between encapsulated token and delimiter','errorName':'Internal Server Error','error':500,'path':'/apps/tools/Entity/DataImport/load'}
2

Solution

While investigating, I discovered that using the withEscape method of CSVFormat (It is used in the loadFile method of the EntityDataLoader)can help manage to escape characters, making it easier to handle values with quotes. Here’s a snippet of the updated code:

Code reference

escapeSeq = '\\'

CSVFormat format = CSVFormat.newFormat(edli.csvDelimiter)
        .withCommentMarker(edli.csvCommentStart)
        .withQuote(edli.csvQuoteChar)
        .withSkipHeaderRecord(true) // TODO: remove this? does it even do anything?
        .withIgnoreEmptyLines(true)
        .withIgnoreSurroundingSpaces(true)

format = format.withEscape(escapeSeq) // Added escape character support

CSVParser parser = format.parse(reader)

By specifying an escape character (escapeSeq), we can effectively manage values with embedded quotes or other special characters.

I’ve found this solution to handle such values. Please help me further, If this solution is correct then can I create a pull request with this change?

1 Like
3

This sounds very helpful, I’d say submit a PR

1 Like
4

Thanks @michael

I already wrote an issue and linked the PR to the Issue.

Issue: Issue with CSV Parsing for Embedded Quotes Using EntityDataLoader · Issue #641 · moqui/moqui-framework · GitHub
PR: Add support for CSV escape character to EntityDataLoaderImpl by puru-khedre · Pull Request #642 · moqui/moqui-framework · GitHub

1 Like