How to export data from excel to database?

Question

Can you please explain how i can export the data from excel to database? There is no documentation or step by step process which i can follow. A simple example with few rows of data and a table will suffice.

Chris · Answer

Hi aalim, we utilize the Excel Tools plugin for reading data into an Appian process, and then persisting to the database. It has some services that can import your data directly from Excel to the database, but since it can be difficult to debug any issues with those services, I generally recommend to use the fn!readexcelsheet() function provided, such as within a script task in your process flow. 
 With this method, you will have the excel document uploaded via a!fileUpload() (or some other method to get it in to the system), then you can utilize this function over the uploaded document. Below is one of my expressions that converts an excel document into a CDT. You can perform any manipulations or adjustments during the type cast, then you have a populated CDT that you can push through a Write to Datastore service, to load your DB. 
 Note here I have a child rule!SP_readExcel_convertDate() that manipulates uploaded date values into a format the CDT accepts. 
 a!localVariables(
 local!data: readexcelsheet(
 ri!doc,
 0,
 2
 ),
 
 if(
 not(local!data.success),
 {},
 reject(
 rule!isEmpty,
 a!flatten(
 a!forEach(
 items: local!data.result,
 expression: {
 if(rule!isEmpty(index(fv!item.values,1,"")),
 null,
 'type!{urn:com:gdit:types}SP_ExcelUpload'(
 EmployeeID: index(fv!item.values,1,""),
 Purpose: index(fv!item.values,2,""),
 ProjectCode: index(fv!item.values,3,""),
 Task: index(fv!item.values,4,""),
 ExpenseDate: rule!SP_readExcel_convertDate(
 dateVal: index(split(index(fv!item.values,5,"")," "),1,null)
 ),
 PayCode: index(fv!item.values,6,""),
 Amount: index(fv!item.values,7,""),
 DeptOverride: index(fv!item.values,8,"")
 )
 )
 }
 )
 )
 )
 )
)