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

a!localVariables(
  local!uploadedFile,
  local!showOptionalParameters: false,
  local!selectedSheetNumber,
  /*Sheet Number Selection  */
  local!sheetNumber: a!defaultValue(local!selectedSheetNumber, 0),
  local!selectedstartingColumn,
  /*Starting Column of Excel  */
  local!startingColumn: a!defaultValue(local!selectedstartingColumn, 1),
  local!selectedendingColumn,
  /*Ending Column of Excel  */
  local!endingColumn: a!defaultValue(local!selectedendingColumn, 100),
  /*Contents of excel that came after reading  */
  local!excelContents,
  /*Keys of the Excel  */
  local!keysOftheFile: if(
    a!isNullOrEmpty(local!excelContents.data),
    null,
    reject(
      fn!isnull,
      index(
        index(local!excelContents.data, 1, null),
        "values",
        null
      )
    )
  ),
  /*just values of the file  */
  local!contentsOftheFile: if(
    a!isNullOrEmpty(local!excelContents.data),
    null,
    a!forEach(
      items: remove(local!excelContents.data, 1),
      expression: a!forEach(
        items: fv!item.values,
        expression: if(
          fv!index > count(local!keysOftheFile),
          {},
          fv!item
        )
      )
    )
  ),
  /*Creating a dictionary out of excel  */
  local!createDictionaryWithFile: if(
    a!isNullOrEmpty(local!excelContents.data),
    null,
    a!flatten(
      a!forEach(
        items: local!contentsOftheFile,
        expression: createdictionary(local!keysOftheFile, fv!item)
      )
    )
  ),
  {
    a!cardLayout(
      contents: {
        a!richTextDisplayField(
          value: a!richTextIcon(
            icon: "refresh",
            caption: "Refresh",
            link: a!dynamicLink(
              saveInto: {a!save(local!uploadedFile,null),
             a!save(local!showOptionalParameters,false),
              a!save({local!selectedendingColumn,local!selectedSheetNumber,local!selectedstartingColumn,local!excelContents},null)
              }
            ),
            linkStyle: "STANDALONE"
          ),
          align: "RIGHT"
        ),
        a!fileUploadField(
          label: "Upload File",
          labelPosition: "ABOVE",
          instructions: "Upload files of type XLXS",
          /* Saving into Application Documentation    change it to your Folder     */
          target: cons!SB_FOLDER_APPLICATIONDOCUMENTATION ,
          maxSelections: 1,
          value: local!uploadedFile,
          saveInto: { local!uploadedFile },
          validations: {
            /* Accepts only XLXS           */
            if(
              fv!files.extension <> "xlsx",
              "Upload Exce File",
              null
            )
          }
        ),
        a!richTextDisplayField(
          /*Capturing additional details for reading          */
          value: a!richTextItem(
            text: {
              "Provide Optional Values to read Excel"
            },
            link: a!dynamicLink(
              saveInto: a!save(local!showOptionalParameters, true)
            )
            
          ),
          showWhen: and(
            a!isNotNullOrEmpty(local!uploadedFile),
            not(local!showOptionalParameters)
          )
        ),
        {
          if(
            local!showOptionalParameters,
            {
              a!integerField(
                label: "Sheet Number",
                value: local!sheetNumber,
                saveInto: local!selectedSheetNumber
              ),
              a!integerField(
                label: "Start Index",
                helpTooltip: "Row number to start reading ",
                value: local!startingColumn,
                saveInto: local!selectedstartingColumn
              ),
              a!integerField(
                label: "BatchSize",
                helpTooltip: "How many rows to read",
                value: local!endingColumn,
                saveInto: local!selectedendingColumn
              )
            },
            null
          )
        },
        a!buttonArrayLayout(
          buttons: {
            a!buttonWidget(
              label: "Read Excel",
              saveInto: {
                /*Saving submitted files                */
                a!submitUploadedFiles(
                  onSuccess: a!save(
                    local!excelContents,
                    readexcelsheetpaging(
                      todocument(local!uploadedFile),
                      local!sheetNumber,
                      a!pagingInfo(
                        local!startingColumn,
                        local!endingColumn
                      )
                    )
                  )
                )
              },
              style: "OUTLINE",
              showWhen: a!isNotNullOrEmpty(local!uploadedFile)
            )
          },
          align: "START",
          marginBelow: "NONE"
          
        ),
        a!gridField(
          label: "Excel Data",
          labelPosition: "ABOVE",
          emptyGridMessage: "Excel Does not contains Any Data",
          data: local!createDictionaryWithFile,
          columns: {
            a!forEach(
              /*Creating read only grid              */
              items: local!keysOftheFile,
              expression: a!gridColumn(
                label: fv!item,
                sortField: fv!item,
                value: index(fv!row, fv!item, null)
              )
            )
          },
          showWhen: a!isNotNullOrEmpty(local!excelContents),
          validations: {}
        )
      },
      height: "AUTO",
      style: "TRANSPARENT",
      marginBelow: "STANDARD"
    )
  }
)

I wrote similar code for reading Excel sheet and showing it on grid dynamically , Go through the local variables where i used reject function with flatten to read excel data , Make the necessary changes.

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.

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
)

Thank you , I will try 

thank you I will try