How do you make a list out of a column in excel file?

Extensions DataDriver
1

Hi, Im trying to store a list from an excel file. but dont know how to work around it.
appreciate your help on this.

2

i have successfully imported the excel file and tried to store column to a list
${list} = columnHeader

but didnt work

3

Hi @Alton ,

I’m not sure what you are trying to do.

Have you taken a look at the Excel Lib :
https://rpaframework.org/libdoc/RPA_Excel_Files.html#List%20Worksheets
https://rpaframework.org/libdoc/RPA_Excel_Files.html#Append%20Rows%20To%20Worksheet
https://rpaframework.org/libdoc/RPA_Excel_Files.html#Set%20Cell%20Value
https://rpaframework.org/libdoc/RPA_Excel_Files.html#Set%20Cell%20Values

Best,
TextSolver34761

4

Hi

By list, are you referring to a column of values? if so, then the below will give you a general idea of how to do so

I use the Library ExcelLibrary (you’d need to check the . type they support, as different excel libraries vary in what they support) but the general basis of how you do it is very similar with what keywords they provide

With this, I have a helper/action robot file that I use, and I have various keywords built around data I recieve, so for example, the below grabs all cell values for each row within a range (starting _row - row_count) row count I have a keyword that gets all rows from any Excel sheet, and then it gets the cell value in a FOR loop for the column I provide.

I check the cell value before adding it to the list based on how I get the data so that may not be a requriement for you; you’d just want to strip out those bits, but just to give you a general idea of its usage for how I use it.

Custom Keyword:

Get Row Cell Values For Rows
    [Arguments]     ${starting_row}     ${row_count}       ${data_col}     ${sheet_name}
    @{cell_values}     Create List
    FOR    ${row_index}     IN RANGE     ${starting_row}    ${row_count}
        ${data}=   ExcelLibrary.Read Excel Cell      row_num=${row_index}       col_num=${data_col}       sheet_name=${sheet_name}
        IF    "${data}" != " " and "${data}" != "${EMPTY}" and "${data}" != "N/A"
             Append To List    ${cell_values}     ${data.strip()}
        END
    END
    Return From Keyword      ${cell_values}

Using the keyword:

ExcelLibrary.Open Excel Document    ${EXCEL_FILE_PATH}      doc_id=${None}
${row_count}=      Excel Get Row Count      ${starting_row}     ${sheet_name}
${row_count}=      Evaluate    ${row_count} + ${starting_row}

@{excel_range_descs}=      Get Row Cell Values For Rows
...     starting_row=${STARTING_ROW}
...     row_count=${row_count}
...     data_col=1
...     sheet_name=${SHEET_NAME}

The starting row I give is so I can jump the headers (these differ for me from sheet to sheet), and then I append it to the overall row count I receive from my keyword, so when I pass the two to the keyword, I only get from the starting data row to the end.

link to library: GitHub - peterservice-rnd/robotframework-excellib: Robot Framework library for working with Excel documents.

Doc keywords: ExcelLibrary (rawgit.com)

I use this library over others because I need to read / write while the document is open and supported. type for how I get the specs.

Hopefully, this helps you get an idea of how you can go about it :slight_smile: