webMethods

Product/components used and version/fix level:

SAG 10.7

Detailed explanation of the problem:

Requirement : Receiving the input file as an Excel Sheet (.xlsx) Example: SAG_WM_17012023.xlsx
but we need to convert the .xlsx file to SAG_WM_17012023.csv

If it is a CSV file, I can use CRLF as Record Delimiter and comma as Field Delimiter but for Excel file, we do not have such delimiters.

How to convert Excel FF to CSV FF?

Afaik, you need to use a 3rd party java library to read an excel file. If your requirement is to convert excel file to a flat file you can implement a simple java service for this purpose. You can find a lot of examples on the internet and use it as baseline for your java service.

In addition to the info @engin_arlak shared, might I share that past experience with programmatically dealing with Excel files is something to avoid if at all possible. We used POI long ago. It was okay. But any change in the Excel, particularly if the Excel file is typically edited by people, can cause the process to fail. That’s true with any file format but with Excel it is exceedingly easy to inadvertently break things.

Reporting tools and BI components love spitting out Excel. Because they work well for humans. As a “bucket” for data transfer automation, they are less compelling.

Edit: anything that can create an Excel can generally create a delimited flat file too. Ask them to do that instead. And have them follow the typical conventions so that there is no data loss and delimiters can appear within the data without being treated as delimiters.