How to fetch data from EXCEL in 19.1??

Hello All,

I tried with Appian Excel Tools Plugin "Parse Excel Spreadsheet to CDT" smart service is not available in 19.1.

is there any way to get the excel data ?

Thanks!!

  Discussion posts and replies are publicly visible

Parents
  • Hi Shubam,

    you can use the below readExcelSheet function of Excel Tool plugins like this:

    load(
    local!recordSets: readExcelSheet(
    excelDocument: ri!document,
    sheetNumber: 0,
    startRow: 2,
    numberOfColumns: 0 /*reading all columns*/
    ).result.values,
    local!count: count(
    local!recordSets
    ),
    a!forEach(
    items: 1 + enumerate(
    local!count
    ),
    expression: load(
    local!data: split(
    joinarray(
    local!recordSets[fv!item],
    "; "
    ),
    "; "
    ),
    'type!{urn:com:appian:types:DTP}DTP_CustomerDetails'(
    FirstName: local!data[1],
    LastName: local!data[2],
    Address: local!data[3],
    Phone_Number: local!data[4]
    )
    )
    )
    )

Reply
  • Hi Shubam,

    you can use the below readExcelSheet function of Excel Tool plugins like this:

    load(
    local!recordSets: readExcelSheet(
    excelDocument: ri!document,
    sheetNumber: 0,
    startRow: 2,
    numberOfColumns: 0 /*reading all columns*/
    ).result.values,
    local!count: count(
    local!recordSets
    ),
    a!forEach(
    items: 1 + enumerate(
    local!count
    ),
    expression: load(
    local!data: split(
    joinarray(
    local!recordSets[fv!item],
    "; "
    ),
    "; "
    ),
    'type!{urn:com:appian:types:DTP}DTP_CustomerDetails'(
    FirstName: local!data[1],
    LastName: local!data[2],
    Address: local!data[3],
    Phone_Number: local!data[4]
    )
    )
    )
    )

Children
  • Hi Jon,

    In my project there is a requirement to read Date from Excel(05/17/2019). In appian I am getting it as ("2019-05-17 00:00:00"), how can I cast this as a date field in appian, because the current format used in todate() gives nothing or Date out of range. Is there any different way of extracting date from excel?

  • Hi Nupoor,

    U can try this below example, it will convert your excel date format to Appian Date format:

    load(
    local!recordSets: readExcelSheet(
    excelDocument: ri!document,
    sheetNumber: 0,
    startRow: 2,
    numberOfColumns: 0 /*reading all columns*/
    ).result.values,
    /*here first date format is "05/17/2019", and 2nd date format is "17-05-2019" in excelsheet*/
    a!forEach(
    items: local!recordSets,
    expression: {
    'type!ExcelCDT'(
    date1:append(split(split(fv!item[1]," ")[1],"-")[3],"-",split(split(fv!item[1]," ")[1],"-")[2],"-",split(split(fv!item[1]," ")[1],"-")[1]),
    date2: fv!item[2]
    )
    }
    )
    )