# Read Excel File and Push All Data To Elasticsearch

**URL:** <https://discuss.elastic.co/t/read-excel-file-and-push-all-data-to-elasticsearch/101131>\
**Category:** Elasticsearch\
**Created:** [September 20, 2017, 8:19am UTC](https://discuss.elastic.co/t/read-excel-file-and-push-all-data-to-elasticsearch/101131 "2017-09-20T08:19:50Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![ajit](https://avatars.discourse-cdn.com/v4/letter/a/a3d4f5/32.png) [@ajit](https://discuss.elastic.co/u/ajit)\
**Post date:** [September 20, 2017, 8:19am UTC](https://discuss.elastic.co/t/read-excel-file-and-push-all-data-to-elasticsearch/101131/1 "2017-09-20T08:19:50Z")

</div>

I have created program to read excel file using java and aspose. I am creating column as key and row as value in json object but if when I read all file row wise and then my json values gets overwrite by last row values. To overcome this I am reading excel row wise and after each row I am sending data to elasticsearch but this process takes around 2hrs for 50000 records. I want code which will read all excel file and push data to elasticsearch in single trip.

Below is my code for reference.

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();
			
	SimpleDateFormat format = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
	Date d1 = new Date();
			
	List<Object> colList = new ArrayList<Object>();
	for(int i = 0; i<=0 ; i++)
	{
		for (int j = 0; j < totalColumns; j++) 
	    {
			colList.add(cells.get(i, j).getValue());
	    }
	}
			
	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",
				"/indexname/indextype/"+i,
				Collections.<String, String>emptyMap(),
				entity);
				System.out.println(EntityUtils.toString(indexResponse.getEntity()));
				
				obj.clear();
	}
```

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [September 20, 2017, 10:35am UTC](https://discuss.elastic.co/t/read-excel-file-and-push-all-data-to-elasticsearch/101131/2 "2017-09-20T10:35:55Z")

</div>

You should use the bulk API instead.

That will be much faster!

---

<div class="post-metadata">

**Author:** ![ajit](https://avatars.discourse-cdn.com/v4/letter/a/a3d4f5/32.png) [@ajit](https://discuss.elastic.co/u/ajit)\
**Post date:** [October 3, 2017, 11:16am UTC](https://discuss.elastic.co/t/read-excel-file-and-push-all-data-to-elasticsearch/101131/3 "2017-10-03T11:16:30Z")

</div>

Thank you for providing solution. Got solution with bulk API.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [October 31, 2017, 11:16am UTC](https://discuss.elastic.co/t/read-excel-file-and-push-all-data-to-elasticsearch/101131/4 "2017-10-31T11:16:33Z")

</div>

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