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. image.png 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 image.png 0 Use python for the part to be filled and Winautomation for the input part (Excel to Excel as virtual)

Prerequisites

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

image.png

Monitoring folder settings

Set up File Monitor Trigger with Triggers from WinAutomation Console スクリーンショット 2020-09-13 135734.jpg 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

Variable name Contents
%FileTriggerFileName% File name including extension
%FileTriggerFilePath% Keeps the full path of the file and all the properties of the file
%FileTriggerFilePath.NameWithoutExtension% File name without extension

Creating a process

The whole process スクリーンショット 2020-09-13 203140.jpg

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 image.png 2.Copy File(s) Copy the detected files to the Data folder to the Work folder スクリーンショット 2020-09-13 180433.jpg 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 スクリーンショット 2020-09-13 181647.jpg 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

スクリーンショット 2020-09-13 182956.jpg

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.

スクリーンショット 2020-09-13 183945.jpg

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. ezgif.com-crop.gif

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.

Recommended Posts

Use Python in your environment from Win Automation
Use the CASA Toolkit in your own Python environment
Easily use your own functions in Python
Use thingsspeak from python
Use config.ini in Python
Use fluentd from python
Use dates in Python
UI Automation in Python
Use Valgrind in Python
Use MySQL from Python
virtual environment in python
Use MySQL from Python
Don't use readlines () in your Python for statement!
Use jupyter-lab installed in python virtual environment (venv)
Use Python in Anaconda environment with VS Code
Use BigQuery from python.
Use profiler in Python
Development environment in Python
Use pydantic when reading environment variables in Python
Python environment construction @ Win7
Use os.getenv to get environment variables in Python
Use a free GPU in your favorite environment
Use mecab-ipadic-neologd from python
Operate mongoDB from python in ubuntu environment ① Introduction of mongoDB
Let's use def in python
Use let expression in Python
Use Anaconda in pyenv environment
Use Measurement Protocol in Python
Use callback function in Python
Use parameter store in Python
Use HTTP cache in Python
OCR from PDF in Python
Use MongoDB ODM in Python
Handle environment variables in Python
Use Random Forest in Python
Use regular expressions in Python
Use Spyder in Python IDE
UI Automation Part 2 in Python
Use e-Stat API from Python
Get your heart rate from the fitbit API in Python!
Use Stanford Core NLP from Python
[Python] logging in your own module
Use fabric as is in python (fabric3)
Get data from Quandl in Python
Install scrapy in python anaconda environment
How to use SQLite in Python
Read and use Python files from Python
Prepare your first Python development environment
Forcibly use Google Translate from python
Use rospy with virtualenv in Python3
install tensorflow in anaconda + python3.5 environment
Extract text from images in Python
Use kabu Station® API from Python
How to use Mysql in python
Use Python in pyenv with NeoVim
Use Azure Blob Storage from Python
Use the Flickr API from Python
How to use ChemSpider in Python
How to use PubChem in Python
Use django-debug-toolbar in VirtualBox / Vagrant environment
Use OpenCV with Python 3 in Window