How to read excel file and push data into elasticsearch


(ajit) #1

I have implemented below solution but it is taking long time to push all data to elasticsearch.
I have created hashmap and I am reading file row wise. I want column name as key and respective row as value in hashmap. If I read all file and create hashmap then it is taking only last row values in elasticsearch. To overcome this problem I am reading single row, creating hashmap and pushing data to elasticsearch. Due to this row by row reading and pushing data after each row to elasticsearch it is taking long time.
Please provide me solution to read excel file and push data in elasticsearch in single trip.

Below is my code for reading file and pushing data to elasticsearch row by row.

RestClient restClient = RestClient.builder(new HttpHost("172.21.153.176", 9200, "http")).build();
JSONObject obj = new JSONObject();
FileInputStream file = new FileInputStream(new File("C:\Users\c-ajitb\Desktop\TempFiles\op\TestExcel.xlsx"));
Workbook workbook = new Workbook(file);
Worksheet worksheet = workbook.getWorksheets().get(0);
Cells cells = worksheet.getCells();

Range range = cells.getMaxDisplayRange();
int totalColumns = range.getColumnCount();
int totalRows = range.getRowCount();
RowCollection rows = cells.getRows();

int maxColumn = cells.getMaxDataColumn();
int maxRows = cells.getMaxDataRow();
System.out.println("Max Col="+maxColumn);
System.out.println("Max Row="+maxRows);
System.out.println("Total Cols="+totalColumns);
System.out.println("Total Rows="+totalRows);

SimpleDateFormat format = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
Date d1 = new Date();
System.out.println("Start Time:"+format.format(d1));

List colList = new ArrayList();
for(int i = 0; i<=0 ; i++)
{
for (int j = 0; j < totalColumns; j++)
{
colList.add(cells.get(i, j).getValue());
}
}

	System.out.println("Col List ="+colList);
	
	for (int i = 1; i < rows.getCount(); i++) 
	{
		
	    for (int j = 0; j < totalColumns; j++) 
	    {
	    	
	    	obj.put(colList.get(j), cells.get(i, j).getValue());
	        System.out.print(cells.get(i, j).getValue() + "\t");
	    }
	    System.out.println("");
	    Date d = new Date();
	    obj.put("Action_Date", dateFormat.format(d));
	    
	    String jsonString  = obj.toJSONString();
	    HttpEntity entity = new NStringEntity(jsonString, ContentType.APPLICATION_JSON);
		
		Response indexResponse = restClient.performRequest(
				"PUT",
				"/index123/indextype1/"+i,
				Collections.<String, String>emptyMap(),
				entity);
				System.out.println(EntityUtils.toString(indexResponse.getEntity()));
				
				obj.clear();		
    		}

How to push large excel data to elasticsearch using bulk API
(system) #2

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.