Import excel to database

Hi Sandeepr373,

did you have to parse the excel and then supply the data as a cdt into the "new column values" parameter of the smart service ?

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'. 

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: 

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 

thanks asimrasool it worked for me by following the instructions you specified

@asimrasool Your comments were very helpful. I have faced similar issue. though the trick worked, i have date and datetime fields in my data. which are nullable fields. It Appears excel is expected to source these values even though i have marked the columns as nullable in DB. has anyone faced a similar issue? 

Note: I even tried entering null and blank values for these columns.

My design looks like this

1. Let user upload either an Excel or a CSV. 

2. Export and Stage this data in DB.

3. prompt user with loaded tabular data and confirm the modification.

3. update/Modify reference tables based on the staged data.

Thanks,

Sai Manam

This thread is a few years old - but issues like this are why I always recommend using the fn!readExcelSheetPaging() function within the same Excel Tools plugin - that way you can create an expression rule to pull the raw Excel data and map it to the CDT yourself, performing any manipulations, empty checks, etc, and then simply use the Write to Data Store Entity to persist the CDT to the DB.

Hi Chris, Could please suggest , without use of CDT because we are using 22.4v and implementing write to record smart service.