Use Python in your environment from Win Automation

Overview

If you want to automate the input process, you may want to include a Python process as a pre-process. WinAutomation has a Python action, but it's called IronPython, and it seems difficult to use libraries such as Pandas. So I would like to explain how to execute Python from Winautomation in the environment where the library you want to use is installed.

theme

Fill 10,000 rows of Excel data including the following null (blank) with 0 (zero) and input it to another system 0 Use python for the part to be filled and Winautomation for the input part (Excel to Excel as virtual)

Prerequisites

• Windows10 64bit
• WinAutomation 9.2.1
• Python 3.8.5

There may be various ways to build a Python environment, such as using a distribution, but this time we will explain it in the Official version. If you have an installed environment, you may not be able to operate normally if there are multiple environments, so please take your own risk.

Environment

Python installation

https://www.python.org/ Download 3.8.5 from, install without passing the path. Execute scripts and commands from Py.exe, a launcher application that is installed at the same time.

Check the installation status and launcher operation by checking the version information

C:\Users\username>py -V
Python 3.8.5

Check the initially installed libraries Check for libraries that are not the latest version

C:\Users\username>py -m pip list
Package         Version
--------------- -------
pip             20.2.1
setuptools      49.6.0

C:\Users\username>py -m pip list --outdate

If not the latest version, both will be updated

C:\Users\user>py -m pip install -U library name

Reconfirm with py -m pip list

Install the libraries required for data processing

This time, pandas is used for data processing, and openpyxl and xlrd are used for reading and writing Excel files. numpy and other required libraries are installed automatically when pands is installed

C:\Users\user>py -m pip install pandas
C:\Users\user>py -m pip install openpyxl
C:\Users\user>py -m pip install xlrd

C:\Users\user>py -m pip list
Package         Version
--------------- -------
et-xmlfile      1.0.1
jdcal           1.4.1
numpy           1.19.2
openpyxl        3.0.5
pandas          1.1.2
pip             20.2.3
python-dateutil 2.8.1
pytz            2020.1
setuptools      50.3.0
six             1.15.0
xlrd            1.2.0

Add any libraries you want to use as well The code editor is Visual Studio Code.

Winautomation process

Monitoring folder settings

Set up File Monitor Trigger with Triggers from WinAutomation Console Link the process name to be started after OK

By starting Created of File Monitor Trigger The following variables automatically have values It's useful to remember because it's a variable that doesn't appear in Process Designer

Creating a process

The whole process

Explanation of each action

1.Set Variable The file name variable passed from File Monitor Trigge is too long, so I just made it easier to understand 2.Copy File(s) Copy the detected files to the Data folder to the Work folder 3.Write Text to File Creating Python Script (.py) If the data file to be input has a fixed file name, it is not necessary to generate a python script every time. In this case, it is assumed that the file name may fluctuate, such as when the date is included, so the file name is acquired from the Trigger variable and written every time. Save as UTF-8 as ZZ.py Python Script is a shabby content that just reads into Pandas and saves it with zero fill and file name 2.xlsx When created from the trigger, the file name is entered in the variable part

import pandas as pd
df = pd.read_excel('%FileTriggerFileName%')
df.fillna(0).to_excel('%FileName%2.xlsx',index = None)

4.Run DOS Command Execute the ZZ.py file created in 3

5.Launch Excel I open an Excel file generated by Python, but the path notation of the variable file name is / instead of \ only before the variable. I want to be careful because it is a fitting point Also, if you uncheck Make Instance Visible, you can execute it without displaying it on the screen, but in that case, be sure to insert Close Excel at the end of the process and close the instance or it will remain open behind the scenes.

About the process of 6 ~ 17

Instead of inputting to these other systems, data is partially transferred to blank Excel one cell at a time, so it is omitted. The reason for not doing batch copy is that it is assumed that a transaction will occur when inputting on another system.

Summary

The launcher py.exe makes it easy to run .py Script. It can be incorporated into the WinAutomation process after using a convenient library such as Pandas.

To tell the truth, if you just want to pad with zeros, you can do it from WinAutomation using Excel replacement. (Easy) However, if complicated preprocessing is required, it seems difficult to control Excel. You can do the same with WinAutomation alone by setting the process to zero if it is blank when posting. However, it will be slower than if the data was preprocessed. In my boro machine Core i7 3770 8G, when 10,000 rows * 7 columns are transferred to another Excel cell by cell, 5 minutes and 50 seconds with preprocessing It was 6 minutes and 30 seconds when posting while performing zero processing if it was blank in Win Automation without preprocessing.

I'm lonely because there is still little information on Win Automation.