Imports Not appearing in Data Store

Hello,

When I import either a csv or excel file. the records do not appear on the data store. 

I do not have access to view the database but the files make it to the target folder.

Thank you for the help

  Discussion posts and replies are publicly visible

Parents Reply Children
  • 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

  • 0
    Certified Lead Developer
    in reply to Kerlin

    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

  • 0
    Certified Lead Developer
    in reply to Kerlin

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

  • 0
    Certified Lead Developer
    in reply to Kerlin

    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)

  • 0
    Certified Lead Developer
    in reply to Kerlin

    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?

  • 0
    Certified Lead Developer
    in reply to Kerlin

    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.