Copy from Excel DAC

Use the Copy from Excel command to enhance your automation in various scenarios:

• Extract specific data from an Excel worksheet.

• Automate data extraction from an Excel file to be used in other parts of your automation process.

In the Nintex Wizard Editor, search for the Copy from Excel command and drag it into your steps. Use the table below to configure each field and understand the settings:

Field		Description	What to do
Where to copy from		The Excel file containing the data you want to copy. You can reference it using a variable, enter the full file path, or browse for the file on your local machine.	Enter the full file path (e.g., C:\Users\dev\Downloads\sample_invoices.xlsx), use a variable, or click Browse to select the file from your computer.
Password		The password to unlock the Excel file, if it is password-protected.	If applicable, enter the password to unlock the Excel file.
What to copy
Worksheet		The name of the worksheet from which to copy the data.	Enter the worksheet name (e.g., Sheet1) or use a variable to specify it.
Cells	Range	The specific range of cells to copy.	Enter the cell range in the from:to format, even if it's a single cell (e.g., A1:A1), or use a variable.
	from:To	The starting and ending columns and rows to copy.	Enter the starting column and row in the from fields (e.g., C5). You can also specify the ending column and row in the To fields. Use numbers, letters, or a variable. See Considerations.
	Entire worksheet content	Option to copy all data from the entire worksheet until the command reaches the last row and column.	Select this to copy all the data in the worksheet. The command will stop after 20 consecutive empty rows/columns.
Select in Excel		Optional feature to manually select the cells within Excel (requires Excel to be installed).	Click Select in Excel to select the cells you want to copy.
Copy method
Actual Values		Copies the raw data from Excel without preserving any formatting. This is useful when you only need the underlying data (e.g., numbers or dates) without visual styling.	Select this option if you want to copy the raw data without keeping any formatting. See Considerations.
Formatted Text		Copies the data from Excel while preserving the formatting, such as fonts, colors, and date formats. Use this option when you need to retain the visual presentation of the data.	Select this option to copy the data along with its formatting, such as dates and other visual styles. See Considerations.
How to save		The variable where the command stores the copied data.	Enter or select a variable to store the copied data.
Column delimiter		A delimiter to separate each column in the copied data.	Enter a delimiter (e.g., comma) to separate columns in the copied data or use a variable. See Best practices.
Row delimiter		A delimiter to separate each row in the copied data.	Enter a delimiter (e.g., newline) to separate rows in the copied data or use a variable. See Best practices.
Error handling
Error variable		Optional error handling with customizable error messages.	Enter an Error variable name to ensure errors will display in the View variable list command. Use the default error handling messages or customize the error message and response as needed.
Enable timeout monitoring		Optional feature to track the command's runtime and raise an error if it exceeds a set duration.	Select the checkbox to enable timeout monitoring, then set the duration (default is 1800 seconds). Customize the timeout error message, or use the default one.
Test		Optional feature to test the selected cell range and ensure the command captures the correct data.	Click Test to verify that the correct data is being captured from the specified cells.

When working with Excel commands like Copy from Excel, Paste to Excel, or Delete from Excel, there are two options for selecting the cells to use: manual selection and Select in Excel. This allows you to specify which worksheet and cell range to manipulate, making the commands flexible for different use cases.

Enter Excel data selection manually

This method allows you to manually define the worksheet content to use in your command. It's especially useful when you want to use variables to select specific cells dynamically. When using variables in any of the fields, remember to enclose the variable name in dollar signs (e.g., $MyVar$).

You can choose one of the following methods for manual selection:

• Range: Specify the range using standard Excel notation, even if it's a single cell (e.g., A1:A1), to select specific cells.

• from/To: Specify the starting and ending column and row. For example, from: C5, To: E10, or leave the To field blank to copy all rows/columns until the wizard detects 20 consecutive empty cells.

• Entire worksheet content: This option allows you to copy, paste, or delete the entire worksheet content. The wizard will stop after detecting 20 consecutive empty rows or columns.

Select in Excel

This method allows you to select the cells you want to use from within the Excel sheet. It's useful when you prefer to work directly in the file and see the data you're manipulating. Here's how it works:

1. Click Select in Excel to bring up the Select from Excel window. The selected Excel file appears behind the selector window.

   

2. Select your worksheet and cells by clicking in the Excel file to choose the worksheet (tab) and the cells you want to use in your command. The Current selection fields in the selector window fill in with your selections.

   If the selector window interferes with your view, you can drag it to a different location on your screen.

   

3. Click OK to confirm your selection. The Worksheet and Range fields in the command fill in based on your selections.

   

If you need to change your selections, you can either edit the fields manually or repeat the Select in Excel process.

Follow these best practices when using the Copy from Excel command:

• Fill in all required fields before testing or running the command (e.g., file selection, range definition).

• Always enclose variables in $ signs to ensure proper recognition by the wizard.

• At the start of the wizard, use the Set value command to define variables for common delimiters. This ensures consistent handling of row and column delimiters throughout your process:

  • Create a variable named Comma and set its value to ,.

  • Create a variable named Tab and set its value to <Tab>.

  • Create a variable named Pipe and set its value to |.

  • Create a variable named Newline and set its value to <Enter>.

• Use Formatted Text when copying dates or other formatted data to keep the formatting and avoid errors like serial numbers for dates.

• Test the data extraction using the Test button to verify correct selections before running the command.

This example shows how to extract product data from a worksheet in an Excel file, copy it into a variable, and prepare it for further processing.

1. Add the Set value command to your wizard to specify the Excel file location.

2. Add the Copy from Excel command and complete the following fields:

   • Where to copy from: $InvoiceFile$

   • Worksheet: Sheet1

   • Cells: Entire worksheet content

   • Copy method: Actual Values

   • How to save: ProductData

   • Column delimiter: , (comma)

   • Row delimiter: | (pipe)

   • Error variable: Error

3. Add the View variable list command to verify the command works without errors.

• Select in Excel and Test are unavailable until you fill in the required fields.

• When using the from/To option without specifying an endpoint, the command copies data until it detects 20 consecutive empty rows or columns.

• Using Entire worksheet content copies the entire worksheet but stops after detecting 20 consecutive empty rows/columns.

• Cells displaying as #### may mean insufficient column width; you can manually adjust this in Excel or use the Run macro command to auto-size columns.

• For the Copy method, use Formatted Text when the relevant cells are formatted as dates to avoid returning sequential serial numbers (e.g., January 1, 2008 = 39448). For all other cells, use Actual Values.