How to read the specific column value from excel using SenseTalk?

Eggplant Functional
,
1

Hello Team,

I have checked Excel File Interaction | EPF Docs web page, But I am not able to find the option to fetch the specifc column value from excel and do the operation.

Assume my excel has 100+ column and 5000 rows , in that I need to read A,D,F,AA,CT columns values only.

Do we have any methods /fucntions like cellrange?

put worksheet1.cellRange ("A6", "CT6") into columnsBasic

or
is there any effective ways we can delete uncessary column value after reading using cellRange?

Regards
D.Thangaraj

2

Hey @Thanagaraj,

We do have cellRange:
put worksheet1.cellRange("C","E") into columnsBasic

This reads the columns you specified and all of them will be put into a list
Excel File Interaction | EPF Docs (eggplantsoftware.com)

Or do you mean to extract only the row values of row 2,10,15,50,75 for all columns?
You can also connect to an excel file as a database

You can read rows by specifying the row numbers
put worksheet1.cellRange(2,10,15,50,75) into rowBasic

Cheers,
Karsten

1 Like
3

Hello Karsten,

I have connected excel as database, But after reading how to extract the worksheet and provide the excel cell range.

The below code reading as Table
" " "
put “Y:\igpm_downloaded_files” into desination_folder
put “Centralized_iGPM-RG_Configuration(10).xlsx” into file_name
put desination_folder&""&file_name into excel_name
set myExcelDB to Table(3) of {type:“excel”, file:excel_name}
log myExcelDB
" " "
While try to access as worksheet it’s giving as error.

" " "
set Spreadsheet to worksheet(3) of{type:“excel”, file:excel_name}

set worksheet(3) of {type:"excel", file:excel_name} to mySheet
log mySheet


put Spreadsheet to worksheet(3) of  {type:"excel", file:excel_name} into mySheet
log mySheet
4

Hey @Thanagaraj,

The error message would be helpful. I can see that you are opening the Excel as a Database. This database connection does not offer the cellrange nor worksheet commands. You cannot mix Excel as a database and excel File Interaction commands.

Here is my code that works with Excel worksheet and cell range etc. Excel File Interaction | EPF Docs (eggplantsoftware.com)

set MyExcelFile to Workbook(resourcePath("Centralized_iGPM-RG_Configuration.xlsx")) // Sets MyExcelFile as a reference variable for the Excel file

put worksheet("Sheet1") of MyExcelFile into worksheet1 ## use the Name of the worksheet

put worksheet1.cellRange("C","E") into columnsBasic ## extracts only these columns

put worksheet1.cellRange("2","5") into rowBasic // creates a list of lists for a range of rows

Cheers,
Karsten

5

Hello Karsten,

I have updated the intial question content for better understanding.

In that case we can’t read specific column value.Becasue cell range will always return the content between specified column range.

put worksheet1.cellRange("C","F") into columnsBasic ## extracts between C to F collumn

put worksheet1.cellRange("C","F","AA") into columnsBasic ## Will display the error.
6

Hey Thanagaraj,

Finally got what you want. Thanks.

set myExcelDB to {type: "excel", file: resourcePath("Centralized_iGPM-RG_Configuration.xlsx")} -- set the specified variable, myExcelDB, to store the contents of the referenced Excel file and establish a SenseTalk connection.

set memberTable to table "SheetOrTableName" of myExcelDB -- Sets MemberTable to be a table object which refers to the table "Members".

set the columnsToFetch of memberTable to ["Name","Last Name"] -- Update the Column headers. Uses the columnsToFetch table property to specify columns of interest. 

put the records of memberTable into CurrentMembers -- Retrieves the specified columns (Name, ID Number, and Expiration) of all of the records in the "Members" table (referred to by the table object memberTable) and stores them in the variable CurrentMembers.

put jsonformat(CurrentMembers)

The Excel interaction does only fetch the range.
feel free to add a feature request on craft.io

Cheers,
Karsten