Dear Team,
I'm trying to import data from an excel file, but it's difficult because the excel file's column names and order aren't in the right order.
I want to validate that the column's sequence and column name should be in chronological order.
Hi Pradip,
What method are you using to read the Excel? The built-in Platform ExcelToRecordList, or a Forge asset like Advanced Excel?
Built in ExcelToRecordList, i tried to loop it, but its not working.
Hi,
Build-in ExcelToRecordList requires column names with right order, because it will follow the structure attributes order. For a dynamic excel import you will need a forge component like Advanced Excel
As Nelson wrote, you cannot do what you want with ExcelToRecordList. It's a fairly primitive action, that needs the column headers to have the exact same name as the structure attributes. So what you could do is create a structure that matches the Excel-file layout exactly, including column names and order, and then assign the output to your list with different column orders, mapping each attribute (or, if an assign doesn't work, a ListAppendAll).
The exact structure and column orders were established by me, but a user accidentally ignored a column somewhere in the middle. After reviewing the logs, I discovered that the failure was caused by data that was not tied to the column. Then, when troubleshooting, I discovered a missing column. I would like to validate it before processing in order to prevent this from happening repeatedly.
I understand. The only way to do that is to use a Forge asset, I'd suggest to check out Advanced Excel like Nelson also suggested.
Hi @Pradip Chavhan
We can able to achieve this.
You need to store or assign the header names in order to a List of Text Variable (e.g., ExpectedHeaders). Then you can use the Advanced Excel actions like Workbook_Open_BinaryData, Worksheet_SelectByIndex, Cell_ReadByIndex from the Excel Forge component to read the header row from the uploaded Excel file.
Also, for testing, my variable has the headers in this order: Name, Age, Year, Phone. So please create an Excel file with these headers or use the sample Excel file attached. You can also test by changing the header order or names to see the validation result.
Testing URL : HederOrderVerfication
Thanks,
Senthil
Also can able to achieve with Static Entity like below with Order. Here find the updated OML for both Entity & List of Text Variable based solution.
Hope this works.