DEV Community

Richard Roelofs
Richard Roelofs

Posted on

Import multiple CSV files into Power BI Desktop

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}')
Enter fullscreen mode Exit fullscreen mode

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.

Top comments (1)

Collapse
 
roelr1 profile image
Richard Roelofs

Thank you!