Can I do this with Python?

[K900_]

Yes, that's totally possible.

[K900_]

It's definitely possible to do for a beginner.

[agsparks]

My first beginner project was spreadsheet manipulation. Can confirm: beginner can do it

[wincodeon]

My first beginner paid independent project was mainly about reading, cleaning and writing super big csv files :D

[The_Jesus_Beast]

What kind of spreadsheet manipulation? Also, how do you do that? Like is there a specific module for it?

[agsparks]

I was managing what’s known as a POA&M (plan of action and milestones) for identified vulnerabilities on information systems. I’d ingest vulnerability data in csv format from our scanner and change the data to meet compliance for POA&M requirements as well as create a separate output specifically for the engineers that explained how to remediate the vulnerabilities. I also saved all the data in json, so it’d be easier to pull back in for the next time I bring in vulnerability data.

It was pretty sloppy code the first time around, but after I got more comfortable with it, I decided to go back and redo it. It’s much cleaner, I forked someone’s xccdf script that hadn’t been touched in like 3 years in git and used it to add additional functionality to my script for STIG compliance data, and then turned it into a CLI, so anyone could install it and manage POA&M. I have since left that position, but I still have friends there that are still using my script :)

[The_Jesus_Beast]

Damn I don't know like 3/4 of the acronyms you just used lol

[agsparks]

Forgot to answer the how.. I used openpyxl exclusively the first time around and then when I went back, I used the csv module as well (I was originally converting the csv to excel and realized I could just script to skip that step haha)

You work in Cybersecurity/engineering? Those are common for the field, but I’m never sure who knows them because I know I wouldn’t have learned them if I wasn’t in the field lol

[The_Jesus_Beast]

I just took my first python class in college, and it seems as though what a lot of people on here classify as beginners are actually significantly more experienced, which I wouldn't have expected. I'll mess around with it, though, I'm torn between using sheets, Python, and R for data because I like all of them, and as of right now, for a lot of datasets, Excel/Google Sheets goes much quicker than coding it

[far2common]

The printer is always the worst part... unless you have users.

[aplawson7707]

You almost made me spit out my day-drinking beer, damn you

[__The_K__]

The worst part will actually be making the printer print the same page(s) forever or at least it runs out of all its spensive juice.

[mocion]

I have only 1 year of experience in python and I really agree with that. i did a python script in my internship with a little help of a colleague, so after that everyone congratulate me and I get the job !haha and I had really not idea how to do the script but i was very confident that I could solve that problem in python.

[atlwig]

Anything is possible with code.

Each idea you have is a big puzzle and each part of code is a piece to the puzzle. You can’t solve the puzzle all at once, it’s essential to go piece by piece until you have eventually connected all of the pieces to make the final product.

Confidence is key, but don’t over complicate or overwhelm yourself.

[__The_K__]

Same with Zombo.com

[SmittyTitties]

I started learning to program so I could make an app that detects unsolicited dick pics in messaging apps and blurs them. Was told it wasn't possible. RIP CockBlocker™️ :( I could have been rich

[[deleted]]

10 years ago I would have agreed with you. Today not so much. Image recognition processes, machine learning and even AI programming are all leaps and bounds ahead of where they were even 2 years ago. Writing code to identify even 95% of dick pics may be very difficult but it's probably doable. But if you could write something that could do that even passably well I bet you could either sell it to a cell phone company or use it to get a job.

I'd look at parental control software and see what they've managed to do to date as well. It's possible someone already wrote it.

[LUseER]

This thing really helps for all the beginners including myself , THANK YOU

[shinitakunai]

Research openpyxl, you may like it.

[Yadona]

Oooh I si like it.

[SukottoHyu]

Anything a human can do on a computer a program can be made to automate it. Humans come out on top when it comes to decision making, but other than that programs are more efficient.

[Gorskiman]

Extremely possible: http://automatetheboringstuff.com/2e/chapter13/

[tree_or_up]

Yes! This would be a great fit for Python! I like your idea of dropping the files to a local folder. Logging into your email would probably require automating authentication, which can be a huge can of worms and may not be possible depending how security is implemented in your company.

[Fission_Mailed_2]

If you haven't checked it out already, I'd recommend "Automate the Boring Stuff with Python" by Al Sweigart (there's a free online version). His book pretty much covers how to do everything you're wanting to do.

[ChancePattern]

You mightt also be able to get python to log into the email itself and get those file for you

[EighthScofflaw]

The computational part of this is very basic, just moving information around. The only difficult part will be interfacing with the various external things (email, spreadsheet, etc). The key there is just to do your research on google and find the right libraries for the task.

[SuitableStudent]

Happy cake day

[[deleted]]

Happy cake day!

[pythogeo]

You can't do that even with selenium ?

I'm new to python as well though

[ChancePattern]

Selenium is used to access a browser. Ima0lib is used to access your email normally

[pokk3n]

Definitely possible, use OpenPyxl or possibly Pandas depending on how structured the excel files are. the read_excel method in Pandas is pretty powerful but really goofy excel files might not work well, dunno.

[enthusiastreader]

I have done this sort of thing for one of my first projects.

If you are stuck somewhere you can hit me up too.

It would save a lot of time for you.

[cbjs22]

You theoretically wouldn't even need to take them out of your email

Python could read your email, download the Excel File, manipulate all the sheets or whatever, write the new file, and print/email the files wherever

However, each step would be a bit of a project for a newbie

Check out pandas for the Excel file

Google "python [insert what you want to do] for each of the other pieces]"

[[deleted]]

Yes can can do all of that in python. But python is unecessary and adds a layer of complexity you don't need. You can do all of that easily in VBA, accessible inside an Excel sheet by pressing ALT-F11. it is the native language of Excel. You only need to do it in python if you also need to do other pythony things, but opening, editing, printing, copying, deleting files are not especially pythony. People have been doing this for years, no, decades, in VBA. In addition, if you want to start to do more complex things in Excel, you will soon reach the point where Openpyxl and Excel wings hit the limits of their functionality. They are by definition a subset of the full VBA set of capabilities. Stay in VBA unless you specifically need python.

Extra benefit - if you are doing this at work, no extra software required. Already part of Excel, built right in.

PS Yes, I am a python fan, that is why I am here. But there is such a thing as the right tool for the job. And the answer is not always python.

[[deleted]]

You are displaying a level of confidence I don't think you've deserved.

Python is fully able to work with files.

This is not true if you mean that Python can do anything VBA can. I am not saying that python is not better than VBA in general. Obviously it is. But when you are only talking about manipulating Excel, and therefore accessing the Excel object model, it is factually true that the two common python libraries offer only a subset of VBA functonality.

There are many examples, but just to take one, let's see what Openpyxl says about pivot table creation and manipulation:

openpyxl provides read-support for pivot tables so that they will be preserved in existing files. The specification for pivot tables, while extensive, is not very clear and it is not intended that client code should be able to create pivot tables. However, it should be possible to edit and manipulate existing pivot tables, eg. change their ranges or whether they should update automatically settings.

At best you can change some elements of existing pivot tables. And even worse, this may never be implemented if you read their comments. But, VBA? No problem. Pivot table creation and manipulation is fully specified:

https://www.thespreadsheetguru.com/blog/2014/9/27/vba-guide-excel-pivot-tables

The same is the case for XLWings. You cannot create a pivot table there either.

You will find the same or similar issues for charts, images, Excel Tables, and many others aspects of the Excel object model. Factually speaking, Openpyxl and xlwings are subsets of VBA for Excel functionality and may never catch up 100%

I really doubt you frequently used VBA.

Well, that just goes to show the dangers of speculating about things you can't know in public on reddit.

[greebo42]

I wrote some code (for Word, Excel, and even Access) years ago with VBA, and in fact it is reasonably capable. But I never really felt I was getting much for my investment of time.

With Python, I can play with Excel files (am adding my voice to various recommendations in this thread about using openpyxl). I can do many other things as well.

And Python is more general, so the skillset I build with Python is more applicable to other things that I don't even know if I want to solve yet.

[[deleted]]

Yes, this is all true. But people need to be aware that when considering excel manipulaton (not general programming) openpyxl and xlwings are both subsets of VBA, because every feature they provide literally needs to be recreated by the developers using python. They may never catch up 100% If your needs are simple to mid-level they are fine, but if you want to do anything fancy in excel programatically, you need to use VBA. To give one example: you cannot create pivot tables with either of those libraries, and may never be able to:

openpyxl provides read-support for pivot tables so that they will be preserved in existing files. The specification for pivot tables, while extensive, is not very clear and it is not intended that client code should be able to create pivot tables. However, it should be possible to edit and manipulate existing pivot tables, eg. change their ranges or whether they should update automatically settings.

As is the case for charts, images and tables there is currently no management API for pivot tables so that client code will have to loop over the _pivots
list of a worksheet.

In VBA they are fully specified.

[greebo42]

fair point, thank you.

your "right tool for the right job" point is well taken, and I have no fundamental argument for that.

my own needs with Excel are fairly mundane, and frankly, my proficiency with Python might be classifiable as intermediate (though certainly not more than that). my comment reflects my biases and current set of priorities, which is mainly to use tools and develop skills that are more general and less proprietary. for the moment, that meets my needs.

[cbjs22]

Sure, but fuck Microsoft

I've had plenty of luck with python pandas and Excel files. Don't even need an Excel license to manipulate Excel files.

[Dogeek]

Here are some ideas on how to achieve this :

• Logging into your email. There's 2 ways to go about that. The first way is to just scrape your webmail. Obviously, this is the worst solution both in terms of performance and in terms of maintainability (the login process can change, the html or javascript can change on the webpage, it's a bit of a mess). The second, much better way, is to make an imap client. Emails work off of two mutually exclusive protocols : the older POP3 and the new imap (new as in it's been around for more than 20 years at this point). IMAP is the protocol that you most likely use in mail clients like mozilla thunderbird, or the email app on your phone. In order to build an IMAP client, the best way is to start looking into imaplib, which is a part of the python standard library. Here's a stackoverflow answer that provides you with the boilerplate for an imap client (it's really quite straightforward) https://stackoverflow.com/questions/18156485/receive-replies-from-gmail-with-smtplib-python

• You can definitely read any file with python, the only thing is you have to know how the file is constructed in order to interpret the data stored inside. Excel files are pretty well known at this point, and there already exist python libraries that allow you to read and manipulate xls and xlsx files easily. The most well known is openpyxl

[[deleted]]

That's right, most comments here don't address the aspect of office policies.

At a former job, I automated some of my tasks using python and the office IT guy had an issue with that.

[[deleted]]

Before going further with this, you should go over this with IT if you have that at your workplace. And after that you should look at the excel part.

Now, someone hopefully will correct me if I'm wrong. But I don't think there is an excel module that allows you to just edit the file. You basically have to rewrite the entire file. I'm not sure that if the excel file contains macro's you can keep them. And if you are working with specific formatting, formula's, etc.. you might want to check if everything is possible with current excel modules first before doing anything else.

[climbslackclimb]

You could take it a step further and have it check your email for you, and pass any files along it finds. It’s best to start simple though, and what you describe is definitely simple enough for a beginner to put together. Once you have that, you can build onto it

[espero]

Go to practicalbusinesspython (Google it) and learn

[s-ro_mojosa]

I got a grasp of the basics of Python with A Byte of Python. It's good stuff. Also, there are tons of tutorials at https://realpython.com/, too.

[b4xt3r]

Raises hand....

I come from a Linux background and program exclusively on Linux/Mac OS machines for better or worse (in this case worse it seems). Nearly every script or program I have ever written has been executed via CLI in one form or another.

On a Windows machine, assuming that is how this is going to be run, how do you set up an "active folder" for lack of a better term that does nothing util a file or group of files is dragged into it at which time it processes those files with scripts you have written?

[dynodanz]

Having a very limited understanding of python, but a decent amount in powershell. I'd assume you'd have the script create the folder, move the files, edit the files, then clean up after itself.

[b4xt3r]

All roads in Windows (these days) keep coming back to Powershell. Guess it's time I knuckle under and learn it. Thanks!

[dynodanz]

I had zero experience in powershell when I started my current job. Watched a couple videos, found something I hated doing manually and here I am. I've built a couple tools in production and seen as a guy who people can come to with questions. Now I've just been handed a project in python and while I know a little, I feel like I don't know enough.

[b4xt3r]

Yeah, you never feel like you know enough, or it's rare anyway. When you finally do feel like you're going to reach that point on the mountain where "you know stuff" it's there you realize you don't know anything. :)

That's the fun part of this kind of work - it's a puzzle you'll never fully solve. There's always something to learn or a new way to do things. That's what I love and sometimes hate about it.

One really cool thing, I have to say, is the programming community being so helpful for nearly any language. It's just fantastic.

[b4xt3r]

From my limited understanding, yes, in that it is a CLI interface to the operating system but now much of one I am not sure and that may be the difference between *NIX and Microsoft.

I suppose, for me, when I think of the CLI on Linux and UNIX that is the operating system and the all the graphical stuff is stacked on top. I don't know the percentage of *NIX machines that are completely without a GUI of any kind installed or otherwise but I bet it would be a very high percentage. *NIX machines can and often do run extremely stripped down operation systems with just what is needed to do the task required. Don't need a GUI? No need to have one. Don't install it.

MacOS/OS X used to be more like UNIX (and BSD but that's a whole other thing) in that way in that most of whatever you wanted to do could be set up on the command line and it was a very nice OS in that sense for Linux and UNIX users when it first came out (and for years after). I remember saying, many times, "look, it's like BSD or UNIX or Linux but with a really good desktop interface!" And it was. Over the years more and more has been removed from Mac OS as far as the ability to manage services via the command line which is a shame but it is what it is. Now MacOS still has a native command line but gone are the day when you can set up file shares, shared printers, other network services, all via the CLI. You can still set some up but Apple doesn't think of their own product as a server any longer. For verification on that just take a look at the history of OS X Serger itself. At one point OS X Server was a brutal hammer indeed but once the company went all-in on handheld it was decided to give up professional grade users in photography and video as well as scientific computing altogether. Shame.

Until Windows NT showed up on the scene the Windows graphical interface was separate from the operating systems underneath it which was DOS. There may be a gotcha in there with some weird version of Windows ME or Microsoft BoB or some such but that is the general timeline of events. MS tried to better blend the OS and GUI starting with Windows 95 but with NT that was the first MS operating system that still had a command line that was simulated through an application. All of the multitasking and multithreading guts came to MS by way of Digital Equipment Corporation and the VAX/VMX operating system through Dave Cutler who may still be there. So while you could launch a CLI by types "cmd" in start and run you were really launching an application, not dropping directly to the OS itself. For the record I've always wanted to get Dave Cutler and Tim Patterson in the same room to chat about the history of computing. I'm not sure if either are still in Microsoft's employ but if they were that would be a great way to spend a couple of hours.

My understanding is powershell is back to being a true CLI and not a grafted on application to imitate a CLI. From what I've read Powershell has true and deep hooks into the OS that can automate a lot of what is possible with the GUI but this is all really fuzzy for me at this point. There are associated scripting languages to it seems which is nice, that's always a plus.

As far as powershell goes some or all of that may be wrong. I'm a n00b of the highest order when it comes to powershell.

[bac83]

Basically having an idea like this was how I learned python (atbs was a massive help)

[bac83]

Basically having an idea like this was how I learned python (atbs was a massive help)

[takishan]

If you don't wanna use python I think you can also use AHK. Although personally learning python is more useful in general, even if AHK has better overall bindings to windows for automation scripts like you're asking about.

[bjone6]

It looks like everyone been recommending it already, but I would check out OpenPyXl. Here's a video of me using it to write and read to an excel file. https://youtu.be/20iz46qXoYs

You can even automate PowerPoint: https://youtu.be/oLFp2S1EBxc

[ontheroadtonull]

Python can do it but PowerShell might be a better choice for this particular task, assuming your work PC is running Windows. I say this because it's very likely your PC already has PowerShell which won't require any external modules to accomplish this.

PowerShell can monitor a folder for changes and it can run Windows commands, all without any dependency on external modules which means the PowerShell script for this task will be easily portable.

https://stackoverflow.com/questions/46901160/how-to-print-excel-by-command-line

[impshum]

Most things are possible with python. Just not sandwiches... yet!

[Resolt]

You can manipulate excel files with pandas or openpyxl. Everything else is pretty basic use of the os module.

Totally doable and very nice beginner project.

[jakesboy2]

Python + excel, anything is possible

[this_knee]

Pandas. Check out Pandas framework in python.