2 posts tagged with "Excel"

In the realm of data manipulation and analysis, Microsoft Excel stands as a cornerstone, offering a vast array of functions to run tasks and unearth insights from raw information. One of the fundamental operations that professionals frequently encounter is adding columns to Excel spreadsheets, a seemingly simple yet crucial task that can significantly enhance data organization and analysis.

In this blog post, we’ll dive focusing on the process of adding two columns within a spreadsheet.

To begin, Log in to your Robomotion workspace. If you haven't registered yet, you can create your workspace from the provided link. This flow utilizes Excel. If you attempt the same operations with the MS Excel Package, the flow will result in an error.

To add columns, follow these steps:

1. Use the 'Insert Column' node.
2. Begin by defining the column names.
3. Next, specify the particular column within the input of each 'Insert Column' node and add the desired data.
4. Lastly, employ the 'Save Excel' node to save the changes.

Flow Steps

1. The 'Inject' node is used to initialize the flow.
2. Three variables are defined using a "function" node. One of these variables is 'msg.excel_path', which specifies the path of the Excel file to be used. The other two variables are 'msg.ColumnName1' and 'msg.ColumnName2', both of which define column names

Code for Set Excel Properties Function Node:

msg.excel_path = ""; // Give the excel path here
msg.ColumnName1 = [""]; // Give the column name here
msg.ColumnName2 = [""]; // Give the column name here
/*
Note!!
Each insert column node is used to insert a column.
The variables msg.ColumnData1 and msg.ColumnData2 are the column names to be added.
*/
return msg;

3. 'Open Excel' node is used to open the defined Excel file. The input for the 'Open Excel' node is the variable 'msg.excel_path'.

4. The first column name that you've defined is included using the 'Insert Column' node. In this process, the 'msg.ColumnName1' variable serves as the input for the 'Insert Column' node, and this variable is then written into the Column Data field. Simultaneously, within the Column Name field, you specify the particular column where you wish to add this column name within the Excel file.

5. The second column name that has been defined is added using the 'Insert Column' node. The input of the 'Insert Column' node is the variable 'msg.ColumnName2'. This variable is written into the Column Data field. Moreover, in the Column Name field, you define the specific column in which you want the column name to be included in the Excel file.

6. The Excel file is saved at the designated Excel path. The 'Save Excel' node takes the variable 'msg.excel_path' as its input.
7. The Excel file that has been saved is closed by using the 'Close Excel' node
8. The 'Stop' node is used to stop the flow.

Here you can access the complete flow by clicking on the following link: https://casestudies.robomotion.io/designer/shared/9Qiu4RfRmKiS1wiY7c5z95

Adding a column at the end of an Excel spreadsheet is a common need that arises from various practical scenarios. Whether it's for better data organization, appending new information, performing calculations, conducting data analysis, or enhancing visualization and reporting, the ability to seamlessly add a column at the end proves to be invaluable.

In this blog, we will explore an efficient method to accomplish this task using Excel's 'insert column' node.

How to Setup?

To begin, Log in to your Robomotion workspace. If you haven't registered yet, you can create your workspace from the provided link.

About Core Excel Package

The Excel package offers a range of features and functionalities to work with spreadsheets, such as create and copy sheets, setting formulas, delete or add columns and more.

Note: This flow specifically utilizes Core Excel. It is important to note that if you attempt to perform the same operations using the MS Excel Package, it may result in errors. Therefore, it is recommended to stick with Core Excel to ensure the smooth execution of the flow.

How to add a column at the end in Excel?

To add a column at the end in Excel, you can follow these steps:

1. Utilize the 'insert column' node: Use the 'insert column' node, which allows you to seamlessly add a new column to your Excel file.

2. Locate the last column: Start by using the 'get row' node to find the last column in the current Excel file. This can be achieved by extracting the length of the msg.row object, which is the output of the 'get row' node.

3. Add the column: Once you have determined the position of the last column, use the 'insert column' node to add the new column to the Excel file. This operation ensures that the column is placed at the end of the spreadsheet.

4. Save your changes: Remember to save the Excel file after adding the column to preserve your modifications and ensure that they are retained for future use.

Flow Steps

1. ‘Inject’ node is used to initialize the flow.

2. Additionally, two crucial variables are defined using a "function" node. The first variable, 'msg.excel_path,' allows you to specify the path of the Excel file to be used within the flow. The second variable, 'msg.lastColumnData,' is where you define the name of the column you wish to add.

Code for Set Excel Properties Function Node:

msg.excel_path = ""; // Give the excel path here
msg.lastColumnData = [""]; // Give the column name here

/*
Note!!
Each insert column node is used to insert a column.
The variable msg.lastColumnData is the column name to be added.
*/
return msg;

3. 'The 'Open Excel' node is utilized to open the designated Excel file, with the 'msg.excel_path' variable serving as its input.

4. The 'Get Row' node is utilized to determine the last column in an Excel file, with the row number being the input for the 'Get Row' node.

5. The 'Get Length' node is used to obtain the length of the 'msg.row' object, which is the output of the 'Get Row' node, in order to determine the last column.

Note: To utilize the 'Get Length' node, you will need to download the 'Robomotion.Object' package. The images below demonstrate the steps to download the package.

6. In the 'Prepare Column' function node, Excel columns are defined. The last column is determined using the 'msg.len' variable, which represents the output of the 'Get Length' node, along with the following code.

Code

var array = ["A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"];
var len = msg.len;
msg.lastColumn = array[len]
return msg;

NOTE: The array variable defined in the 'Prepare Column' node is in the range of A-Z. If the column you are adding exceeds this range, you will need to define additional Excel columns to this variable, such as 'AA', 'AB', and so on.

7. The column name defined is appended to the last column found using the 'Insert Column' node.

8. The Excel file is saved to the designated Excel path using the 'Save Excel' node, with the 'msg.excel_path' variable serving as the input.

9. The saved Excel file is closed using the 'Close Excel' node.

10. The 'Stop' node is utilized to halt the flow of execution.

Here you can access the complete flow by clicking on the following link: https://casestudies.robomotion.io/designer/shared/Y8JFddYmk6qTsbCE5HDHT8