DEV Community: Richard Roelofs

[Boost]

Richard Roelofs — Sun, 20 Jul 2025 14:33:13 +0000

Richard Roelofs

Jul 20 '25

Import multiple CSV files into Power BI Desktop

#csv #microsoft365 #python #data

Comments 1

2 min read

Import multiple CSV files into Power BI Desktop

Richard Roelofs — Sun, 20 Jul 2025 14:29:54 +0000

Introduction

"I am familiarizing myself a bit with Power BI. I was looking for a fun dataset. I like Formula 1. I searched and found it. 14 CSV files all with a different table schema. Of course, I used Chat GPT. I asked how I could do this automatically. I got an answer, but I still had to create a query 14 times. You can also read and import Excel files. I first made a PowerShell script with Chat GPT. It works fine, but the script was not very fast. I wrote the script in Python, and it is significantly faster. Perhaps if you read this, you wonder why I am sharing this? I completely understand. But by thinking that you can export it to Excel yourself and I can't find much about it on the internet. I decided to share it anyway. It can save a lot of time. Maybe you can find it on the internet. But I am not good at searching."
If you need any further assistance, feel free to ask! 😊

The Python Script.


import pandas as pd
import glob
import os
from tqdm import tqdm

# Configuration
csv_folder = r'f:/archiveF1'  # folder with your .csv files
excel_output = r'F:/samengevoegdv2.xlsx'  # output file

# Retrieve all CSV files
csv_files = glob.glob(os.path.join(csv_folder, '*.csv'))

# Write to a single Excel file, each CSV on a separate sheet
with pd.ExcelWriter(excel_output, engine='xlsxwriter') as writer:
    for file in tqdm(csv_files, desc='CSV to Excel', unit='file'):
        sheet_name = os.path.splitext(os.path.basename(file))
        df = pd.read_csv(file, delimiter=',')
        df.to_excel(writer, sheet_name=sheet_name, index=False)

print(f'Done: all {len(csv_files)} CSVs have been imported into {excel_output}')

Last Word

You can fine-tune the script a bit more with ChatGPT, for example by having it create the Excel file, with or without a specific naming convention.
ChatGPT handled everything here. Maybe you are smarter and would have chosen Excel right away. I didn’t, and once again, that’s why I’m sharing this.

How To Set Permission On a Document library on A SharePoint site.

Richard Roelofs — Tue, 15 Apr 2025 06:41:53 +0000

Introduction

At my work, we are preparing to migrate data to SharePoint Online. We are going to create a landing site where everyone within the organization has access, and we are going to create document libraries and place the data there. Employees who had access in the past will, of course, get it again. We will work with members, visitors, and owners. This inspired me to see if this could be automated.
There are other possibilities, but together with my good friend ChatGPT, we concluded that we would work in Azure with an automated account that can run a script via a Powershell script (runbook). Namely, granting permissions. The owner could then grant permissions to employees via a form hosted via a static web app. This was possible via a webhook. This all sounded really great, but since you are calling different APIs, it's more challenging than you think. I ended up in a proverbial loop because you need a token to connect to MS – Graph. However, after much research, this had to be a secure string, and so we also made the script. Token checked, it was valid, but I kept getting the message "invalid JMT token." And when I did it as plain text, I got the message that it had to be a secure string.
I spent quite some time over a period of two weeks figuring out that I couldn't get that stuff working. I had the same result with a Windows form and a PowerShell script. I converted this to an .exe, and this can then be offered at the workplace. But I managed this in 45 minutes.

Preparation

In principle, I could create the PowerShell script myself. Writing a function. But I would do this with Google in the past. Now I have created a project in ChatGPT this much quicker. Of course, I also tried this with the Azure variant, but it did not work out. I asked the question that an owner of a document library can grant Full Control, Write, and Read permissions to another person in the tenant. The setup is a kind of landing page in SharePoint where everyone has access, and it is determined at the document library level what you can access in terms of files. You only see the libraries you have permissions for. Creating the SharePoint site and creating a library is beyond the scope of this article. Do you want to apply this at work? Or are you just interested? Let me know. I do have this PowerShell script. I first created the script without a form and tested whether you can indeed grant permissions. I did this in an E5 development environment. After some testing, it worked, and I asked for a simple form to be made. This went quite quickly. I forgot to add the option to add more members at once and the question of whether you wanted to add another member. And if you answered no, the form would close neatly.

Output(Powershell script)


Add-Type -AssemblyName System.Windows.Forms
Add-Type -AssemblyName System.Drawing
Import-Module PnP.PowerShell -ErrorAction Stop

# === Step 1: Connect to SharePoint ===
$siteUrl = "The Url of the SharePoint site."

try {
    Connect-PnPOnline -Url $siteUrl -UseWebLogin
    $global:connected = $true
} catch {
    [System.Windows.Forms.MessageBox]::Show("ERROR connecting: $_", "Connection Failed", "OK", "Error")
    return
}

# === Step 2: Build Form ===

$form = New-Object System.Windows.Forms.Form
$form.Text = "Set SharePoint Permissions"
$form.Size = New-Object System.Drawing.Size(600, 400)
$form.StartPosition = "CenterScreen"

# Label library
$lblLib = New-Object System.Windows.Forms.Label
$lblLib.Text = "Document Library:"
$lblLib.Location = New-Object System.Drawing.Point(10,20)
$lblLib.Size = New-Object System.Drawing.Size(200,20)
$form.Controls.Add($lblLib)

# ComboBox library
$cbLib = New-Object System.Windows.Forms.ComboBox
$cbLib.Location = New-Object System.Drawing.Point(10,40)
$cbLib.Width = 550
$form.Controls.Add($cbLib)

# Button: fetch libraries
$btnAuth = New-Object System.Windows.Forms.Button
$btnAuth.Text = "Fetch Libraries"
$btnAuth.Location = New-Object System.Drawing.Point(10, 80)
$btnAuth.Width = 550
$form.Controls.Add($btnAuth)

# Label user
$lblUser = New-Object System.Windows.Forms.Label
$lblUser.Text = "User ID (email, separate multiple with , or ;):"
$lblUser.Location = New-Object System.Drawing.Point(10,130)
$lblUser.Size = New-Object System.Drawing.Size(400,20)
$form.Controls.Add($lblUser)

# TextBox user
$txtUser = New-Object System.Windows.Forms.TextBox
$txtUser.Location = New-Object System.Drawing.Point(10,150)
$txtUser.Width = 550
$form.Controls.Add($txtUser)

# Label permissions
$lblPerm = New-Object System.Windows.Forms.Label
$lblPerm.Text = "Permission Level:"
$lblPerm.Location = New-Object System.Drawing.Point(10,190)
$lblPerm.Size = New-Object System.Drawing.Size(200,20)
$form.Controls.Add($lblPerm)

# ComboBox permissions
$cbPerm = New-Object System.Windows.Forms.ComboBox
$cbPerm.Location = New-Object System.Drawing.Point(10,210)
$cbPerm.Width = 550
$cbPerm.Items.AddRange(@("Read", "Contribute", "FullControl"))
$form.Controls.Add($cbPerm)

# Button: assign permissions
$btnSend = New-Object System.Windows.Forms.Button
$btnSend.Text = "Assign Permissions"
$btnSend.Location = New-Object System.Drawing.Point(10,260)
$btnSend.Width = 550
$form.Controls.Add($btnSend)

# Label output
$lblOutput = New-Object System.Windows.Forms.Label
$lblOutput.Location = New-Object System.Drawing.Point(10,300)
$lblOutput.Size = New-Object System.Drawing.Size(560,40)
$form.Controls.Add($lblOutput)

# === Step 3: Events ===

# Fetch libraries
$btnAuth.Add

Output(form)

Push on the button Fetch libraries. You have to authenticatie yourself on the web. After that you can assign members.

Details

Unfortunately, the PS convert to exe module that you can download from the PSGallery does not work for PowerShell 7.
If you still want to offer this, for example on an Intune workplace, you need to create a shortcut that
refers to the following
"C:\Program Files\PowerShell\7\pwsh.exe" -WindowStyle Hidden -ExecutionPolicy Bypass -File "D:\FOLDER\File.ps1"

How I Handle errors in Powershell 7 when using loops

Richard Roelofs — Mon, 31 Mar 2025 12:49:24 +0000

INTRODUCTION

In PowerShell, there are different ways to handle errors in a script. The try and catch methods are good examples of this. Personally, these did not work for me. With the advent of PowerShell 7, error handling has improved. It is easier to identify what goes wrong. However, when using a "loop," the display can become cluttered. You can avoid this by creating an object and writing its output to a file in CSV format. This can be opened in Excel, making it easier to filter.

PARTIAL CODE EXAMPLE
foreach ($i in $dataCSV) { Write-Host "user: $($i.upn)" $li = Set-MgUserLicense -UserId $i.upn -AddLicenses @() -RemoveLicenses $removeLicenses.SkuId -ErrorAction SilentlyContinue if ($null -eq $li) { $li $error = Get-Error write-verbose "$($error.ErrorDetails)" $errorData += [PSCustomObject]@{ upn = $i.upn error = $error.ErrorDetails Trace = $error.ScriptStackTrace } | Export-Csv -Path C:\temp\test.csv -Append -NoTypeInformation } else { $errorData += [PSCustomObject]@{ upn = $i.upn error = "No Error" Trace = $error.ScriptStackTrace } | Export-Csv -Path C:\temp\test.csv -Append -NoTypeInformation } }

EXPLANATION OF CODE

The code is a part of a script where a license is removed from a person. The foreach loop retrieves data from a CSV file via the variable: dataCSV, as the name of the variable suggests. It deals with the User Principal Name, which is displayed on the screen.
Then a variable: li is created with a cmdlet that removes the license. The names of the persons come from the CSV file, and the type of license from the variable: removeLicenses. How exactly this is done is not traceable from this part of the code. But this does not matter for the explanation.
In the variable: li, you include parameters -ErrorAction "SilentlyContinue". This means that errors are not shown, and the script continues and does not stop.
We will now catch the error with an if statement. If the cmdlet cannot be executed, it has no output. In PowerShell, this is comparable to the variable $null. We capture the error with Get-Error and link it to the variable $error.
We create a new CustomObject $errorData and provide it with the data we want for the CSV file with the error message. This consists of the employee's name, whether there is an error or not. In the case of an error, the trace is displayed.

Microsoft Flow send mail 30 days reminder

Richard Roelofs — Sat, 29 Mar 2025 22:29:23 +0000

Introduction

I was actually inspired by an incident at work. I check an Excel list to notify an employee when a certificate, such as one for a server or an application, is about to expire. Why doesn’t these persons do this by themselves? Good question—it sometimes goes wrong because of this :)

There are certainly other ways to solve this, but I thought it would be nice to tackle it with PowerApps by creating a flow. This is not a step-by-step article, but I will give you the key things you need to configure. How to, for example, start a flow is something you should already know or look up yourself.

How do we make this happen?

Recurrence

When creating a new flow from scratch, choose a scheduled flow. Here, you specify the name, when this flow should start, at what time, and how often.

List row present in a table

Here, we will link the Excel file. In this case, OneDrive has been chosen, but SharePoint is also an option. The requirement, however, is that it must be a table. You will likely need to convert it to a table in Excel first.

Apply to each

Now comes the most challenging part. I enlisted the help of ChatGPT because the rows with a date were being compared with an expression and were supposed to return "true," but it kept returning "false" in the flow. Therefore, four compose steps were created to ultimately use a simple condition. I will explain this step by step based on the screenshot.

Select an output from previous steps
When you click with the mouse on the empty field, you will see the screenshot below (this is not the entire screenshot). You select "value, list of items."

ExcelDatumFormatted(first compose step)

Create the following expression : addDays('1900-01-01', sub(int(items('Apply_to_each')?['datum']), 2), 'yyyy-MM-dd')

Today30min(second compose step)

Create the following expression :
string(formatDateTime(addDays(utcNow(), -30), 'yyyy-MM-dd'))

CompareDebug(third compose step)
Create the following expression : string(formatDateTime(addDays(utcNow(), -30), 'yyyy-MM-dd'))

Compose(fourth and last compose step)
Create the following expression : equals( trim(string(outputs('ExcelDatumFormatted'))), trim(string(outputs('Vandaag30min'))) )

Condition

in the left field, select the output from the last compose step. The condition should be set to "is equal to," and then type "true."

_IF YES

This reveals that I’m Dutch :) and I happen to be using Copilot right now to translate the text into English. The screenshot section "Send an email (V2)" is actually self-explanatory—you link it to the column in the Excel file, recognizable by the Excel icon. This also applies to "Update a row," but for the Id, you need to create an expression: int(items('Apply_to_each')?['Id']).

Epilogue
The expression already contains Dutch terms in the actual expression. If you encounter any issues with this, let me know, and I will adjust the article accordingly!