Import Excel to Database

Hi .. assuming this is functionality offered up by the Excel Tools Plug In, then there maybe documentation on the Plug In page

community.appian.com/.../excel-tools

You may need to download the JAR file to access the PDF document contained inside the JAR. 

Hi .. assuming this is functionality offered up by the Excel Tools Plug In, then there maybe documentation on the Plug In page

community.appian.com/.../excel-tools

You may need to download the JAR file to access the PDF document contained inside the JAR. 

Hi,
I am trying to import Excel into database by using "import Excel to database smart service",but getting error
???Identifier name '[success:true,result:[values:userid; username; userein]; [values:1; Maria; 101]; [values:; ; ]; [val' is too long???
Excel sheet has header name as userid,username,userein and only 1 row of values.

can anyone tell me what will be the solution.

Thanks!

@maryam, Does your table have any columns that is configured as non null ?? i.e. In this case if you have configured userid as primary key and the second row in excel sheet had a null value or space this error could occur.

@mohamed, i have changed columns to non null too but still facing same issue,there is no space or null value in excel.
could you elaborate how to configure Inputs configuration,mandatory field are datasourceName,excelDocument and tableName.

Hi Maryam, Sure i will in a few mins, before that can you share your table structure ??

yes,its just a test table and very simple structure:

 

okay, can you try loading the file without userid

tried but no luck.

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

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