load_working_file() method

Loads a working file from the Working data tab to make the file available for Python/HCL scripting. The loaded file is a copy, not the original.

Syntax

hcl.load_working_file(name = "working_file_name")

Parameters

Name Description
name = working_file_name

The name of the working file. The name must be enclosed by quotation marks.

You must exactly replicate the name of the file in the Working data tab, including the file extension. The file, and the script loading the file, must be in the same robot.

working_file_name is not case sensitive. For more information, see Casing of file names.

Note

working_file_name is case sensitive in the save_working_file() method.

Returns

For success, the return code 202. For failure, an error message.

Examples

Note

You can load any supported file type from the Working data tab in a HighBond or Workflow robot and use it in a Python/HCL script. CSV and Excel files are used as examples below because they are two of the most common types of flat files used to store and transfer data.

CSV examples

Load a file from the Working data tab

You want to perform a duplicates analysis of invoice records from Q1 2022. The records are contained in the file Invoices_Q1_2022.csv that was previously uploaded to the Working data tab in the robot. In the analysis script, you must first load the file before you can perform any analysis of it.

hcl.load_working_file(name = "Invoices_Q1_2022.csv")

Load a CSV working file and convert it to a dataframe

You want to convert a loaded CSV working file to a dataframe so that you can work with the data in a tabular format using Python or HCL.

Load a CSV file and convert it to a Pandas dataframe

# Import the Pandas library 
import pandas as pd

# Load a CSV file from the Working data tab
hcl.load_working_file(name = "Invoices_Q1_2022.csv") 

# Read the CSV file to a Pandas dataframe 
invoices_q1_2022_pd_df = pd.read_csv("Invoices_Q1_2022.csv") 

# Display the Pandas dataframe
invoices_q1_2022_pd_df

Load a CSV file and convert it to an HCL dataframe

# Load a CSV file from the Working data tab
hcl.load_working_file(name = "Invoices_Q1_2022.csv")

# Read the CSV file to an HCL dataframe
invoices_q1_2022_hcl_df = hcl.read_csv("Invoices_Q1_2022.csv")

# Display the HCL dataframe
invoices_q1_2022_hcl_df

Convert an HCL dataframe to a Pandas dataframe

# Convert an HCL dataframe to a Pandas dataframe
invoices_q1_2022_pd_df = invoices_q1_2022_hcl_df.to_pandas()

# Display the Pandas dataframe
invoices_q1_2022_pd_df

Load a compressed CSV working file and convert it to a dataframe

Using the ACLScript EXPORT command, you upload the data in a local Analytics table to a compressed CSV file (*.csv.gz) in the Working data tab in a HighBond or Workflow robot. You want to load the compressed CSV file and convert it to a dataframe so that you can work with the data in a tabular format using Python or HCL.

# Import the Pandas library 
import pandas as pd

# Load a compressed CSV file (*.csv.gz) from the Working data tab
hcl.load_working_file(name = "Invoices_Q1_2022.csv.gz")

# Read the compressed CSV file to a Pandas dataframe
invoices_q1_2022_pd_df = pd.read_csv("Invoices_Q1_2022.csv.gz", compression="gzip", header=0)

# Display the Pandas dataframe
invoices_q1_2022_pd_df

Excel examples

Load an Excel working file and convert it to a dataframe

You want to convert a loaded Excel working file to a dataframe so that you can work with the data in a tabular format using Python or HCL.

Excel file with a *.xlsx file extension

The example below shows converting the loaded Excel file to a Pandas dataframe. If required, you can convert the Pandas dataframe to an HCL dataframe, as shown in the example immediately above.

# Import the Pandas library 
import pandas as pd

# Load an Excel file (*.xlsx) from the Working data tab
hcl.load_working_file(name = "Invoices_Q1_2022.xlsx") 

# Read the Excel file to a Pandas dataframe 
invoices_q1_2022_pd_df = pd.read_excel("Invoices_Q1_2022.xlsx") 

# Display the Pandas dataframe
invoices_q1_2022_pd_df

Excel file with a *.xls file extension

To convert an Excel file that uses the older *.xls format you must first install the xlrd Python library.

Note

Similar to the Jupyter Notebook App, you must perform the Python package installation ( pip install ) in a separate script editor cell.

Installing external Python packages is supported in HighBond robots but not in Workflow robots.

pip install xlrd
# Import the Pandas library 
import pandas as pd

# Load an Excel file (*.xls) from the Working data tab
hcl.load_working_file(name = "Invoices_Q1_2022.xls") 

# Read the Excel file to a Pandas dataframe 
invoices_q1_2022_pd_df = pd.read_excel("Invoices_Q1_2022.xls") 

# Display the Pandas dataframe
invoices_q1_2022_pd_df

Load an Excel working file and modify it

You want to load an Excel working file, modify it, and then re-save it to the Working data tab. You can use methods from a Python library such as pyexcel to make the modifications without needing to covert the Excel file to a Pandas or HCL dataframe.

Note

Similar to the Jupyter Notebook App, you must perform each Python package installation ( pip install ) in a separate script editor cell.

Installing external Python packages is supported in HighBond robots but not in Workflow robots.

pip install pyexcel
pip install pyexcel-xlsx
# Import the pyexcel library
import pyexcel as pe

# Import the get_data method from the pyexcel-xlsx library
from pyexcel_xlsx import get_data

# Load an Excel file from the Working data tab
hcl.load_working_file(name = "Invoices_Q1_2022.xlsx")

# Copy the Excel data to a pyexcel sheet
sheet = pe.get_sheet(file_name="Invoices_Q1_2022.xlsx")

# Delete two rows of unwanted header information from the top of the worksheet
del sheet.row[0, 1]

# Name the columns using the name values in what is now the first row in the worksheet
sheet.name_columns_by_row(0)

# Delete three unwanted columns from the worksheet
del sheet.column['Prodno', 'Quantity', 'Unit_Cost']

# Save the pyexcel sheet to a new Excel file
sheet.save_as("Invoices_Q1_2022_prepared.xlsx")

# Save the new Excel file to the Working data tab
hcl.save_working_file(name = "Invoices_Q1_2022_prepared.xlsx")

Remarks

File loading behavior

When you load a file from the Working data tab into the Python runtime environment you are loading a copy, not the original file. If a file with the same name, and identical file name casing, already exists in the runtime environment, it is automatically overwritten.

Casing of file names

When you specify working_file_name it is not case sensitive. When you run a script interactively in the script editor, whatever casing you use for working_file_name is replicated in the Session files panel. If you load the same file twice, and use different casing for the file name each time, the result is two identical session files with differently cased names. The behavior is the same when you use a task to run a script.

Once a file is loaded into the Python environment, however, file names are case sensitive. So after loading a working file, any subsequent code needs to replicate the casing that you used when you loaded the file.

To avoid simple script errors caused by mismatched file name casing, the best practice when loading a working file is to replicate the casing of the file name in the Working data tab.

Script error message

Any of these situations produce the script error message shown below:

  • the specified file does not exist in the Working data tab in the robot containing the script

  • the file name is misspelled

  • the file extension is missing

Script error message:

Exception: File not found in the Working section