how to validate extracted excel columns for data type and not null

Question

hi, 
 I am trying to upload a excel sheet and extract the values from the sheet, Would some one give an idea on how to access each columns to find if its not null and of type "Text" . I am new to this implementation. 
 I am able to write an expression to fetch all rows including the header. 
 
 Any help is appreciated.

Yogi Patel · Accepted Answer

Sri Ram Kaja suggested a way to handle this requirement. 
 You can also check the following for reference: 
 You can create a Map or CDT from the result. Then you can use this for further processing. 
 a!localVariables(
 local!excelFile: if(
 isnull(ri!docs),
 {},
 readexcelsheetpaging(
 excelDocument: ri!docs,
 sheetNumber: 0,
 pagingInfo: a!pagingInfo(startIndex: 1, batchSize: 1000)

 ).data
 ),
 local!flatten: a!flatten(local!excelFile),
 local!finaldata: {
 a!forEach(
 items: local!flatten.values,
 expression: a!map(name: index(fv!item,1), city: index(fv!item,2))
 )
 },
 local!finaldata
)

Sri Ram Kaja · Answer

Hi kowsalyavijayan , 
 We also have similar requirement where we have 15 to 20k rows in excel. The approach we followed is below. 
 Create a initial load table where all the details in excel sheet are dumped in to database table using Import excel to database plugin. Then we created a view in database with all the columns in the initial load table and we get the rows from initial load table where the validation fails. 
 Ex: If the account number cannot be null, then In View, I will add where condition Account is null. 
 So we get all the rows in view which fails the validation rules. If the view is empty, then All rows passed the validation and if any row failed, then we can see that in the view. 
 Next we created a stored procedure which will move data from initial load to my main required table. This procedure is triggered only when view is empty.

how to validate extracted excel columns for data type and not null

Top Replies