Excel File as Origin Source using HTTPS

The output data is in binary format and not useful

https://www.rd.usda.gov/sites/default/files/rd_grant_awards_fy20_final.xlsx

https://www.rd.usda.gov/sites/default/files/RD_GrantAwards_FY19_final.xls

@sgrewal 

As per my understanding for handling xlsx files from HTTP call can be handled by jython or custom functions.

Below the code snippet is helping me to get data from the excel file .

import pandas as pd
url='https://www.rd.usda.gov/sites/default/files/rd_grant_awards_fy20_final.xlsx?raw=true'
df=pd.read_excel(url, sheet_name='Sheet 1')
print(df)

I am planning to create a custom function to handle different api calls and return result in readable format in streamsets.

Kindly let me know if the custom function is fine with you or not.

Thanks & Regards

Bikram_

@tamilarasup 

In this case you need to install panda libraries in streamsets collector. Please try it and let me know if it works.

I am working on custom function and its ready and will provide you the details in sometime.

Thanks & Regards

Bikram_

@sgrewal @tamilarasup 

Heres a pipline i created with your https url and i was able to read the file and write to a local fs

Set HTTP Client → Data Format → Binary (make sure the max byte size is bigger than the file you are ingesting)

Local FA → Output Files 

File Type → Text

File suffix → xlsx

Local FA → Data Format → Binary

Hope it helps. 

@tamilarasup 

I could manage to read actual data by using below custom function .

If you manage to deploy this in your streamsets then you can get the data as a string from the URL. It can read data based on the URL supplied from user.

package groupid;

import com.streamsets.pipeline.api.ElDef;
import com.streamsets.pipeline.api.ElFunction;
import com.streamsets.pipeline.api.ElParam;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.OutputStream;
import java.net.HttpURLConnection;
import java.net.URL;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.net.URL;
import java.net.URLConnection;
import java.util.Iterator;

@ElDef

public class getExcelDataHttps {
    @ElFunction(
            prefix = "url",
            name = "getHttpExcelData",
            description = "Returns URL Data after the connection with given URL"
    )
    public static String getHttpExcelData(
            @ElParam("sourceURL") String sourceURL,
            

    ) throws IOException{

        //StringBuilder content = new StringBuilder();
        StringBuilder response = new StringBuilder();

       try {
        // create a url object
        URL url = new URL(sourceURL);
        URLConnection urlConnection = url.openConnection();
        XSSFWorkbook wb = new XSSFWorkbook(urlConnection.getInputStream());
        XSSFSheet sheet = wb.getSheetAt(0);     //creating a Sheet object to retrieve object
        Iterator<org.apache.poi.ss.usermodel.Row> itr = sheet.iterator();
        
           while (itr.hasNext())
              {
                Row row = (Row) itr.next();
                Iterator<Cell> cellIterator = row.cellIterator();   //iterating over each column
                while (cellIterator.hasNext())
                {
                    Cell cell = cellIterator.next();
                    switch (cell.getCellType())
                    {
                        case Cell.CELL_TYPE_STRING:    //field that represents string cell type
                            response.append(cell.getStringCellValue() + "\t\t\t");
                            break;
                        case Cell.CELL_TYPE_NUMERIC:    //field that represents number cell type
                            response.append(cell.getNumericCellValue() + "\t\t\t");
                            break;
                        default:
                    }
                }
                System.out.println("");
             }

      }

        // System.out.println(response.toString());
        return response.toString();

    }
}
 

Output from the above code as given below:

Obligation Date            States            Counties            Reps            Borrower            Grant Amount            Program            09/30/2020            AK            Bethel (CA)            Young, Don 00            Naterkaq Light Plant            316000.0            High Energy Cost Grants            09/30/2020            OH            Muskingum            Beatty, Joyce 03            Economic & Community Devlpment Institute            66154.0            Rural Microentrepreneur Assistance Program            09/30/2020            MN            Freeborn            Hagedorn, Jim 01            Trails Travel Plaza, Inc            253006.0            Higher Blends Infrastructure Incentive Program            09/30/2020            FL            Brevard            Posey, Bill 08            Southeast Petro Distribution Inc.            3537000.0            Higher Blends Infrastructure Incentive Program            09/30/2020            IA            Polk            Axne, Cindy 03            Kum & Go L.C.            1589000.0            Higher Blends Infrastructure Incentive Program            09/30/2020            WI            Milwaukee            Moore, Gwen 04            Roettgers Company Inc.            251000.0            Higher Blends Infrastructure Incentive Program            09/30/2020            KY            Jefferson            Yarmuth, John A. 03            Thorntons LLC            4268570.0            Higher Blends Infrastructure Incentive Program            09/30/2020            IA            Polk            Axne, Cindy 03            Casey's General Store, Inc.            4978400.0            Higher Blends Infrastructure Incentive Program            09/30/2020            PA            Washington            Reschenthaler, Guy 14            Authority Of The Borough Of Charleroi

Thanks & Regards

Bikram_

@sgrewal 

@tamilarasup 

Kindly mark the issue  as resolved if the above suggestion helps .