Forum Discussion
Import all CSV files from folder with different schemas
Hi caninetiger
Currently Power Query doesn't support to create more queries(tables) automatically or dynamically from a table. So this is not possible at present. I read your discussion in another thread about this and you may decide to use a Python script to write them into an Excel Workbook or a database. This seems the best alternative currently.
But I'd like to remind that if these files have too many rows, it may hit the data size limit as you will have 300+ tables in a semantic model. You may refer to Power BI limit for number of tables - Microsoft Fabric Community There is no document to point out that the limit for number of tables in Power BI but there is limit to data size.
Best Regards,
Jing
Hi caninetiger
Currently Power Query doesn't support to create more queries(tables) automatically or dynamically from a table. So this is not possible at present. I read your discussion in another thread about this and you may decide to use a Python script to write them into an Excel Workbook or a database. This seems the best alternative currently.
But I'd like to remind that if these files have too many rows, it may hit the data size limit as you will have 300+ tables in a semantic model. You may refer to Power BI limit for number of tables - Microsoft Fabric Community There is no document to point out that the limit for number of tables in Power BI but there is limit to data size.
Best Regards,
Jing
Here's that Python script if anyone wants to use it:
import pandas as pd
import glob
from pathlib import Path
# path to your folder
folder_path = r"C:\Users\YOUR_USER\Downloads\FOLDER_NAME/*.csv"
# create an excel file
with pd.ExcelWriter("EXCEL_FILE_NAME.xlsx") as writer:
# Get paths for every csv file in the folder
for file_path in glob.glob(folder_path):
# get each file name
file_name=Path(file_path).stem
# read the file into pandas
df = pd.read_csv(file_path)
# write the file into the excel file with each csv file becoming it's own sheet
df.to_excel(writer, sheet_name=file_name, index=False)Just a heads up about the above code, if any of your csv file names have more than 31 characters, excel will reject it as a sheet name and the code will break.
Best,
Yisroel