AEM component: how to Fetch/Read large excel file data dynamically

[RAJREDDY07]

we have an experience/component where we read data from excel document (this is uploaded to aem)  and this excel document is huge above 4mb.  this excel document can have different headers like one document can have  firstname, last name, etc.. and another document can have state, cities, zipcode, content field.  This content field is like html text, link, phone number, anything and  author like to add labels for these headers and displaying below scenarios dynamically

And upon selection of state, we display cities 

And upon selection of cities , we display zipcodes

and upone selection of zipcode, we display content field

Please let me know how to achieve this scenario.  if excel document is not possible let me know is there any ways we can actually process this data into different formats like nodes, json or creating some apis

[RajaShankar · Accepted solution]

Hi @RAJREDDY07 

I would suggest to use

a) API if available to fetch this data in realtime and populate the component based on selection.We had implemented this kind of requirement for PIM data at run time.

b) Other option is if you can export this as a JSON format under content then you can fetch this json in your component at runtime and populate the fields based on user interactions

c) Else create a scheduler to create the values as nodes and properties in your jcr nodes which your component can parse.(If data volume is large and frequency of data change is less then you can with this option rather than option a)

Regards,

Rajashankar

[rajashekharreddy0707]

@RajaShankar 

The excel data is like tree structure data, Each state has different cities and cities has different zipcodes and zipcodes has different content fields.  how do we store all these information in node and is this performance intensity operation when reading dynamically. 

[Nitin_laad]

@RAJREDDY07 if data import is one time activity, explore ACS Commons Data API and Data importer MCP tool. 

Data Importer (adobe-consulting-services.github.io)

Data APIs (adobe-consulting-services.github.io

After getting the data in AEM, you can fetch the same in your custom component per your use case.