Import excel to database

Hi Ashok, I faced this issue too. The problem is with the excel template. The solution that worked for me was, I exported the table as CSV for Excel from database.(The table where you want to dump data). And then convert this CSV to excel, then use this template to upload data, things should work fine for sure. Also remember to separate the comma separated values to row 1 of excel - column headers. Try and let me know if you have questions !

Hi Rajashekar,

I have the similar requirement to import excel data to database.

I am using the "Import Excel to Database" smart service and doing the same what you suggested above. But i am getting an error  "???Unknown column '' in 'field list'???". I did check in logs its taking one null column and values at the time of insertion(log data is below).  Any idea what could cause this error?

'insert into `MyTable` (`ID`, `EID`, `Name`, `Notes`, `Region`, ``) values (\'2\', \'123\', \'xyz\', \'fhdf\', \'ghsdf\', \'\')',1054

Hi sujithap951
Can you open the excel file and check for the last field if it contains the empty string?

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: 

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 

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: 

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