Imports Not appearing in Data Store

Is there an error in the process? Did you check the logs for more details?

Hello Mathieu,

When I use the Import Excel To Database smart service, I do not get an error and I am unable to access the system logs.

When I to use the readexcelsheetpaging() function, I am able to view the data in the Expression Test Output (RAW).

When I to try write the data to a CDT, I get a CastInvalid error. I am really not sure what else to try here.

Expression

a!writeToDataStoreEntity(
dataStoreEntity: 'recordType!{5d8ec9f2-52c8-4946-a0e8-9ae5ed74aac5}AIC Demographic',
valueToStore: fn!readexcelsheetpaging(
excelDocument: ri!importdata,
sheetNumber: 0,
pagingInfo: a!pagingInfo(
startIndex: 1,
batchSize: 1000,
)
),
)

Test Output Error

Expression evaluation error at function a!writeToDataStoreEntity: Could not cast from RecordType to Data Store Entity. Details: CastInvalidCould not cast from RecordType to Data Store Entity. Details: CastInvalid

The error message you see is pretty much self explaining. You try to pass a record type to the datStoreEntity parameter. That will not work. A constant pointing to that entity in the data store would be a better fit.

Now, without access to the database or log files this will become a tough job.

Can you share a bit more details about what you exactly tried before and what you observed when trying?

Thanks for the reply Stefan.

My goal is to read (or import/upload) either a .xlsx or .csv file to a staging table then after processing the data (type conversions etc.) store the data into a CDT.

So far I have used both the  'Import Excel to Database' and 'Import CSV to Database" smart services from the Excel Tools plugin. I am able to successfully run the process but data doesn't appear in the database (without errors).

When I use the readexcelsheetpaging(), I am able to view the data with a DataSubset data type.

Are able to explain why  constant pointing to that entity in the data store would be a better fit? 

a!writeToDataStoreEntity(
dataStoreEntity: cons!AIC_Demographic_DSE,
valueToStore: fn!readexcelsheetpaging(
excelDocument: ri!importdata,
sheetNumber: 0,
pagingInfo: a!pagingInfo(
startIndex: 1,
batchSize: 1000,
)
),
)

What I wrote here returned, Reaction Tree

Well, the parameter name and type is "dataStoreEntity" and not "recordType".

And, you can use the function a!writeToDataStoreEntity() only inside a saveInto parameter of a UI component.

Question: Are you aware of the differences data store entities vs. records types?

How do you check whether there is data imported without access to the DB?

Understood.

I must not be aware of the differences. My understanding is the record type is where I define my data (the imported excel file) and the data store entity is where the data is stored (the excel worksheets).

I check whether the data imported from the record type

Are you able to write an example expression using the a!writeToDataStoreEntity() function in the saveInto parameter?

Would this work? or should I move a!writeToDataStoreEntity() to the button?

a!formLayout(
label: "Form",
contents: {
a!fileUploadField(
label: "File Upload",
labelPosition: "ABOVE",
value: ri!importdata,
target: cons!AIC_target_folder,
saveInto: {a!writeToDataStoreEntity(
dataStoreEntity: cons!AIC_Demographic_DSE,
valueToStore: fn!readexcelsheetpaging(
excelDocument: ri!importdata,
sheetNumber: 0,
pagingInfo: a!pagingInfo(
startIndex: 1,
batchSize: 1000,
)
),
)},
validations: {}
)
},
buttons: a!buttonLayout(
primaryButtons: {
a!buttonWidget(
label: "Submit",
submit: true,
style: "PRIMARY"
)
},
secondaryButtons: {
a!buttonWidget(
label: "Cancel",
value: true,
saveInto: ri!cancel,
submit: true,
style: "NORMAL",
validate: false
)
}
)
)

I highly recommend the read the Appian documentation and/or attend the academy trainings to understand how Appian uses data and data stores vs. Records.

As it seems that you just started with Appian, you decided to start with a more challenging use case. On a high level, follow these steps:

- In a process start form, upload that Excel document and store it to a process variable

- Use the import Excel to Database node to write the data to the staging table.

- Run your stored procedure to transform the data to the final table

- Do you use a synced record? Use the Sync Records node to make Appian aware of the added rows as this does not happen automatically.

Ok. I will go back to training. When i created the record type i was sure to click the Sync Records before clicking next. 

When I try from the process model, I get the same error regarding CastInvalid. So I must be missing a step of converting the DataSubset Document type into something that can be written to the CDT.

An error occurred while trying to convert the given data to the type of the specified entity "demographic" [id=ff417db7-63f8-4705-8d8a-b8b0d37eb4a8@6382, type=AIC_demographic (id=4384)] (data store: AIC Data Store). Value that could not be converted: ActivityClassParameter[name=records,it=81,v=ff417db7-63f8-4705-8d8a-b8b0d37eb4a8@6382] Details: Could not cast from Data Store Entity to AIC_demographic. Details: CastInvalid (APNX-1-4400-000)

I have no idea what that WtDSE node is meant for. After that import node, the data already is in the database. The next step would be the stored procedure to transform the data.

This is the issue I am running into. When using the Import Excel to Database node, the data never appears in the database. However, the file is created in the target folder.

The WtDSE node is attended to write the imported excel file (pv!HelzleImport) into DSE AIC_demographic

Is there a certain node to transform the data?

This is the issue I am running into. When using the Import Excel to Database node, the data never appears in the database. However, the file is created in the target folder.

The WtDSE node is attended to write the imported excel file (pv!HelzleImport) into DSE AIC_demographic

Is there a certain node to transform the data?

The problem is that you use a synced record, but the import plugin does not trigger that sync. So Appian does not "know" about the new data. For a simple test, just trigger a manual sync in the record.

I have tried to manually sync and there is still not available. I have seen in other threads that using the readexcelsheetpaging() function is the best method for more flexibility and consistency. 

You mention earlier to 

The problem with readexcelsheetpaging is, that it does not scale with larger volumes of data.

To find out what is going on, you need access to the database. And when there is not data imported, then the Excel file is empty, the configuration of the node is wrong, or an error happened during import. Now, that's for you to find out.

OK. Thank you for all the help. What exactly would I need to find out with access to the database. I can tell the Console Admins to look for me.

The excel file has data

The node is configured correctly. I have

Data Source Name: ="jdbc/Appian"
Excel Document: =pv!HelzleImport
Table Name: ="aicdemographic"

It's funny to see my name in a variable.

You need to check for any error messages. The node has some outputs you can configure to check for any errors.

I want to make sure to create a new process.

I am not getting an error when running the node. Is there any other recommended way to read a excel file and store it locally? 

That node will complete but could still return an error message. You have to check the content of the process variables.

where exactly would I find the content to check

here is a screenshot from Generate Documentation 

When monitoring the process instance, you can check the content of process variables.

https://docs.appian.com/suite/help/23.2/Monitoring_and_Editing_Processes.html

I wanted to thank you for all your help. I taken a refresher in the Academy to better familiarize myself with data store entities and records types.

After alot of review, I noticed Appian changed the names of the database and the column headers. I am now attempting to solve why my updates to the XSD file does not reflect on the Data Store when saving and publishing. When I create a new version of the XSD, the Data Store doesn't verify the changes. When I check the DDL script, the changes are there. 

I dont have access to the database console, so I have just been recreating new base data types to resolve my issues. But I know this is not best practice. And will not lead me to reading excel files with multiple sheets and storing them into their specific CDT within the data store. 

But again Thank you for your guidance. My journey continues onto managing historical data and case management with Appian.