How to fetch data from EXCEL in 19.1??

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]
)
)
)
)

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]
)
}
)
)

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]
)
}
)
)