Import Excel to Database

Hi,

This is the new Smart Service in Process modeler "Import Excel to Database", and there is no Documentation available regarding this.

Can anyone help me out that how to use it.

  Discussion posts and replies are publicly visible

Parents Reply
  • Hi, 

     

    I have put up a quick explanation not so nice though, please refer it for configuring the Smart service fields .

    What I would suggest is that,

    1. Don't keep your Primary key in the file, use a separate ID column in the table make it auto increment. This smart service just does insert and not update is my understanding so if you repeat with same user id you may not find the data gets updated in the DB table.

    2. Define exact no of columns you want to read from the file 

    3. Use the same column name in both excel and DB

Children
  • 0
    Certified Associate Developer
    in reply to mohamedt808
    Thank you for the detail configuration thing,let me test with those changes.
  • Trick is the to remove the last column in your excel file and use it under "New Columns" with it's value under "New Column Values". 

    For example, in my CDT, the last column is always "isActive". Now when I would want to import Excel to database, I will not include "isActive" column on excel file, rather, I will input "IsActive" as Value under 'New Columns' and "1" as value under 'New Column Values'. 

    Following the pointers/field guide that provided earlier, as inputs for 'New Column' and 'New Column Values', you should be able to import data. 

    As  mentioned, these were supposed to be optional fields, however, the node fails if they aren't populated, for some reason. 

    Keeping rest of the parameters as desired, the node should pass. It worked for me considering below: 

    1. Do not add primary field in the excel file to be imported. If it's auto-incremented, it will automatically get created
    2. Column headers as in the excel file and database should be same
    3. If there are boolean fields, ensure there is no "null" or blank cell. It should either be 1 or 0
    4. To be cent percent sure, I exported my CDT as CSV, converted it to excel file, and used that format for data backfill/import 
    5. I'm using "jdbc/appian" as Data Source Name
    6. Sheet Number starts from 0 to n
    7. Excel Document should be the file name only (without extension). Use browse and select the file to be sure 
  • Posted this on another thread, but it may helpful here too...

    I had the same issue with the PK not being generated, but figured it out.

    I had a CDT called testTable with columns: id, firstName, lastName
    id was defined as the primary key and set to autogenerate
    Database table was built from the CDT through appian

    My spreadsheet has columns for firstName and lastName, but nothing for the id.
    Looks like the plugin was making a straight insert using the columns and data supplied without knowing or doing anything about the id field.

    like this...
    insert into testTable (firstName, lastName) values ("bob", "barker")

    The trick was to create the table and CDT in the other order.

    First manually create the table in the database with an auto increment primary key
    Next create the CDT using the option, 'Create from database table or view'
    Point your CDT to the newly created table

    Now the database knows what to do about the primary key and the import will work.

    Instructions for an auto increment pk in oracle...

    https://chartio.com/resources/tutorials/how-to-define-an-auto-increment-primary-key-in-oracle/

    Hope this helps

  • 0
    Certified Associate Developer
    in reply to zakarym0001

    I am done all the things process modal successfully executed but data is not show in database although it comes in specified folder

  • 0
    Certified Associate Developer
    in reply to zakarym0001

    This helped in resolving my issue. When table is generated from CDT, the database has no idea on the sequence generation specified through Appian. While if the CDT is generated based on the database table it automatically recognizes the sequence and performs import without fail