Python or powershell for automation?

[Semidevil]

My goal is to be able to learn to write some scripts to automate my reports for work. The end result would be to push as little buttons as possible to be able to do things such as copy files from one place to the next, run SQL query, open/update excel sheets, log in and grab data from various sources and paste it somewhere, automatically send emails, etc etc, and be able to automate it in such a way that It will run at a certain time of the day.

For these type of automation, which programming should I learn? At first I was thinking powershell, but I purchased a book and it kept mentioning it was for system administrator s (I'm not a System admin, so I don't know what that entails), and also I'm about 25% into the book and I feel it's hasn't taught me much. Just take about Linux, dlls, and such. Lastly, it seems the aren't many 'for beginners' type of hits on google.

I've heard python is also possible for automation so I'm considering that.

Between the 2, which one would fit my needs? If both are possible, which one is easier and faster to learn? I'm not trying to be an expert. I just want to learn enough to get a task completed. My only programming background is SQL and R.
Thanks

 

[Ken g6]

I haven't tried either of these for automation, and I haven't automated Windows in many years. That said the first thing that springs to mind is Autoit. https://www.autoitscript.com/site/autoit/

 

[beginner99]

I would also have a look at KNIME. Don't get distracted by the mediocre page that doesn't describe that well what it does. But it's perfect for automation and report creation. No programming needed (but you always can add Java or Python or even R snippets). We use it at work for lots of things including automation and reports but also analytics and machine learning.

EDIT:

This page better describes the features than their default landing page.

 

[quikah]

I am a big fan of powershell, it has a lot of tools for system administration, but you can do all kinds of automation with it. There is a good video series with a nice intro that helped me to start learning it here:
https://mva.microsoft.com/en-us/tra...ell-3-0-jump-start-8276?l=r54IrOWy_2304984382

It is a little hard to say without knowing the exact details of what you want to automate if powershell or some other language would be better. If you need to update Excel sheets I think powershell would be easier as there are ways to interact with office using powershell. But it depends what you actually need to do.

 

[exdeath]

Sounds like this is primarily Windows and MS products you are working with so PowerShell is the way to go.

 

[Semidevil]

Thanks for the feedback. The end result of what I want it to look like, is for it to automatically(or on a schedule) send an email to a group of people with either and attachment or a link.

All the elbow work will consist of things like: logging in to external sites to retrieve data, running oracle SQL and copy and pasting data to a spreadsheet(or appending it), updating pivot tables etc etc. I wasn't sure if powershell has the power to do all that with one click.

But it sounds like it does, so I will look into it.

 

[adamantine.me]

Thanks for the feedback. The end result of what I want it to look like, is for it to automatically(or on a schedule) send an email to a group of people with either and attachment or a link.

All the elbow work will consist of things like: logging in to external sites to retrieve data, running oracle SQL and copy and pasting data to a spreadsheet(or appending it), updating pivot tables etc etc. I wasn't sure if powershell has the power to do all that with one click.

But it sounds like it does, so I will look into it.

Python is great for doing an assortment of things like you listed. I have a blog post that has a simple mailing script that you can cut and paste to learn from. You would just need to add lines of code to create attachments and so on.

 

[mouni]

@Semidevil - Which language did you use to implement for automating your work?I was checking the previous messages and have a similar question.So just wondering how did this go?

 

[dave_the_nerd]

Is this for yourself or is this for career development?

If you're just automating your own stuff on your Windows workstation, Powershell is probably better (with maybe a dash of VBScript for Excel.)

If you're trying to expand your skillset for career development purposes, the Cloud runs Linux and Python is a great cross-platform language. Also, since you mention knowing R, I'm assuming you have a data science background, and Python is a very popular tool for that too.

 

[Scarpozzi]

I have our reporting system send me a lot of reports via Email. At various times, Powershell scripts shutdown Outlook on my workstation and retrieve the report data...then another script kicks off and generates Excel outputs and HTML content that are formatted and Emails them back out.

It works pretty good, but Windows 10 really sucks due to my IT department's group policy stuff. They've been monkeying with security all year and I've had to make a few changes to workaround their nonsense.

Powershell can do a lot with Excel formatting to make reports look good. Creating charts isn't very easy, but formatting tables and filtering csv files for multiple sheets in a workbook is pretty nice.

 

[quikah]

Powershell can do a lot with Excel formatting to make reports look good. Creating charts isn't very easy, but formatting tables and filtering csv files for multiple sheets in a workbook is pretty nice.

Do you have some references that you used to build the excel stuff? Right now I am dumping some stuff to csv and manually manipulating in excel, would be nice to get rid of the manual part (just grouping data and adding some averages in there).

 

[EXCellR8]

Never had much luck learning Python, or any other language for that matter, but either PS or Python would work well for automating basic tasks or admin duties.

 

[Scarpozzi]

Do you have some references that you used to build the excel stuff? Right now I am dumping some stuff to csv and manually manipulating in excel, would be nice to get rid of the manual part (just grouping data and adding some averages in there).

It's better to do the calculations in powershell and use export-csv to save it. It really depends on what you're doing or trying to do. Feel free to PM me and I can shoot you some examples.

 

[mxnerd]

Why not just use VBA for Excel if you want to automate Excel stuff?

 

[Scarpozzi]

Why not just use VBA for Excel if you want to automate Excel stuff?

Powershell or VBA can do it. I switched to Powershell years ago because it was where M$ was heading with their sys admin utilities and VBA wasn't supported as readily.

Powershell can create a new Excel object and then you reference it to create a workbook and worksheets.

$excel = New-Object -ComObject excel.application

I really only use Excel for formatting because people like worksheets and colors, etc in the tables. I don't really use it for automating chart creation, buy you could.

Once you make you workbook and worksheets,you can just use a text connector to copy CSV files to cells. I use foreach loops to create pivot tables and recursive tables of various lengths. You can use commandlets like measure and compare to do most of the work.

 

[beginner99]

Why not just use VBA for Excel if you want to automate Excel stuff?

Sorry but do you still live in the 90ties?

Excel vba stuff is the worst unmaintainable horrific crap ever conceived. Yeah hyperbole but in year 2020 recommending vba is not a good idea at all.

I stand my my initial point. I would really suggest everyone here to look at KNIME (somewhat similar to alteryx but free). It's graphical and you do not need to be able to program but if you can it will help as well. KNIME can do exactly what OP described. Read from different data sources (databases, excel files, text files,...) and then join, transform, analyze the data as desired. At the end a report can be generated in different format and then sent by email to a list of users (the user list can also be read out of a database for example).

This can be schedulded either via OS scheduler running knime in batch mode or by purchasing the non-free server version.

 

[mxnerd]

I haven't programmed for a while and my focus is networking now. VBA is not updated for a while but it's not going anywhere, however.

There are tons of Excel files out there with VBA in them. Power users might still use many Excels files doing sorting, statistics & creating charts. Not everyone knows how to do web programming with .NET (.NET Core), Java, Javascript (or Typescript), Node.js, Electron, Python, Powershell or even Golang.

I know where programming is trending, but most users don't and don't know how and don't need to. They just use what's available to them and what's easy to access.

Many companies also don't want to be locked into a 3rd party solution.

 

[beginner99]

Many companies also don't want to be locked into a 3rd party solution.

And Excel + vba isn't a 3rd party solution?

 

[Scarpozzi]

Sorry but do you still live in the 90ties?

Excel vba stuff is the worst unmaintainable horrific crap ever conceived. Yeah hyperbole but in year 2020 recommending vba is not a good idea at all.

I stand my my initial point. I would really suggest everyone here to look at KNIME (somewhat similar to alteryx but free). It's graphical and you do not need to be able to program but if you can it will help as well. KNIME can do exactly what OP described. Read from different data sources (databases, excel files, text files,...) and then join, transform, analyze the data as desired. At the end a report can be generated in different format and then sent by email to a list of users (the user list can also be read out of a database for example).

This can be schedulded either via OS scheduler running knime in batch mode or by purchasing the non-free server version.

I'll check it out. Sounds cool.

My flat file approach to csv creates HTML files through add-content and injects variables in canned tables....uses css for graphics and style. It works great because I can email HTML files and they're viewable like a dashboard without a server. I'll see what KLIME can do.

 

[mxnerd]

And Excel + vba isn't a 3rd party solution?

VBA is an MS language, how it's a 3rd party solution? Automating Excel through Powershell is great too, I'm just not familiar with it.

 

[Scarpozzi]

VBA is an MS language, how it's a 3rd party solution? Automating Excel through Powershell is great too, I'm just not familiar with it.

That's why I chose Powershell/Excel. I used to automate email creation using send-mailmessage or whatever, but no longer have sysadmin level access, so I was happy I could manipulate my exchange mailbox with Powershell. I receive daily automated reports and have not deleted any messages for years. I can use Powershell to scrape my mailbox and save attachments to a share drive. Then go back with another script and compile the data. It's pretty nice. I've got other scripts that parse massive day files and convert them to other formats that I can then import and use. Python would work just as well, but powershell is pretty easy to work with and the code was quick to write. (Though that project didn't allow for as much recursive code as I would like)

 

[beginner99]

VBA is an MS language, how it's a 3rd party solution? Automating Excel through Powershell is great too, I'm just not familiar with it.

Excel itself is a third party solution which you or the company you work for needs to pay to be able to use. One could argue the same applies for Windows itself if one would be pedantic.
(Remember this was in reply to your comment "companies don't like 3rd party apps).

 

[mxnerd]

@Scarpozzi

You can use Chrome/Firefox SingleFile extension

https://github.com/gildas-lormeau/SingleFile

SingleFile

Save a complete page into a single HTML file

chrome.google.com

SingleFile â Get this Extension for ð¦ Firefox (en-US)

Download SingleFile for Firefox. Save an entire web pageâincluding images and stylingâas a single HTML file.

addons.mozilla.org

to generate a single html report file that contains all the files you need without using zip file or convert it into pdf file.

You can use any automation tools (PS in your case)whatever you like to click on that extension button to generate the file (file name will have a date & time stamp, you probably only want to keep the date part) then email the report to anyone, who can open the file with any browser without unzipping first.

I probably used VBscript to launch Chrome and click on that extension button. Don't quite remember.

 

[Scarpozzi]

@Scarpozzi

You can use Chrome/Firefox SingleFile extension

https://github.com/gildas-lormeau/SingleFile

SingleFile

Save a complete page into a single HTML file

chrome.google.com

SingleFile â Get this Extension for ð¦ Firefox (en-US)

Download SingleFile for Firefox. Save an entire web pageâincluding images and stylingâas a single HTML file.

addons.mozilla.org

to generate a single html report file that contains all the files you need without using zip file or convert it into pdf file.

You can use any automation tools (PS in your case)whatever you like to click on that extension button to generate the file (file name will have a date & time stamp, you probably only want to keep the date part) then email the report to anyone, who can open the file with any browser without unzipping first.

I probably used VBscript to launch Chrome and click on that extension button. Don't quite remember.

I'll check it out. Already have a consistent method...very basic...using add-content to build a predefined HTML page I wrote. I just swap out values in a table and timestamps by using variables.... works great and I haven't touched the code since 2016 because of data source changes.

My only gripe with Powershell is that it sometimes doesn't execute...hits errors and fails with no obvious problem. I run it again and it works flawlessly.

 

[mxnerd]

I'll check it out. Already have a consistent method...very basic...using add-content to build a predefined HTML page I wrote. I just swap out values in a table and timestamps by using variables.... works great and I haven't touched the code since 2016 because of data source changes.

My only gripe with Powershell is that it sometimes doesn't execute...hits errors and fails with no obvious problem. I run it again and it works flawlessly.

It's useful if the web page contain other image files. If it's just a simple table, then there is no need to use the extension.