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.
More...
"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.
More...