Import excel to database

Question

Hi, 
 I'm using 'Import excel to database' smart service in the process. When I run the process, it executes successfully, but does not write anything to database table. Does anyone have suggestions?

asimrasool · Answer

Please refer to this thread: https://community.appian.com/discussions/f/integrations/14268/import-excel-to-database 
 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 mohamedt808 provided earlier, as inputs for 'New Column' and 'New Column Values', you should be able to import data. 
 As sandeepr373 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: 
 
 Do not add primary field in the excel file to be imported. If it's auto-incremented, it will automatically get created 
 Column headers as in the excel file and database should be same 
 If there are boolean fields, ensure there is no "null" or blank cell. It should either be 1 or 0 
 To be cent percent sure, I exported my CDT as CSV, converted it to excel file, and used that format for data backfill/import 
 I'm using "jdbc/appian" as Data Source Name 
 Sheet Number starts from 0 to n 
 Excel Document should be the file name only (without extension). Use browse and select the file to be sure

Vaisakh V Sekhar · Answer

Hi, 
 I would like to suggest a different plugin "Parse Excel to cdt" which can help you in this scenario.

Step 1: 
 Input Configurations for Parse Excel to cdt: 
 1. Excel document :The first row of the template should be identical to the cdt parameters that you have created. 
 PFA sample excel template and cdt screenshot. 
 2. Sheet number: If you have the data saved in Sheet 1 of your excel, give the value as "0" so that the smart service do not give you an invalid range error. 
 3. Row number to read from : As per requirement. (I have configured this as 1 in this case). 
 4. Number of columns to read in a row: As per your requirement .(In this i have configured it as "3" since i have 3 column values to be written into the database). 
 5. Cdt: Create a pv with the cdt type to be written into database. Ensure the pv is marked multiple. 
 Output Configurations: 
 1.return C D T : Map this into the pv created. 
 
 Step 2: Pass the pv into "Write to data store entity " smart service. Ensure that you have checked the input value as "Multiple". 
 testnew.xlsx