Convert Excel Files to CSV using Python | Working with Large Excel Files in Power BI

If you have very large excel files that need to be processed in Power Query, converting them into CSVs can make the processing faster in Power Query. In this video, I will talk about how you can Convert Excel Files to CSV using Python

===== ONLINE COURSES =====
✔️ Mastering DAX in Power BI -

✔️ Power Query Course-

✔️ Master Excel Step by Step-

✔️ Business Intelligence Dashboards-

===== LINKS 🔗 =====

===== CONTACT 🌐 =====

===== CHAPTERS =====
0:00 Intro
0:46 Explanation of Data & Logic
2:42 Creating the Python File
3:39 Importing the Excel Files
6:27 Picking Up the Sheets from Excel File
8:18 Grabbing the Sheets Data
10:08 Creating the CSV files
13:57 My Courses

===== WHO AM I? =====
A lot of people think that my name is Goodly, it's NOT ;)
My name is Chandeep. Goodly is my full-time venture where I share what I learn about Excel and Power BI.
Please browse around, you'd find a ton of interesting videos that I have created :) Cheers!

- - - - -
Music By: "After The Fall"
Track Name: "Tears Of Gaia"
Published by: Chill Out Records
Official After The Fall YouTube Channel Below
License: Creative Commons Attribution-ShareAlike 4.0 International (CC BY-SA 4.0)

[tasfarsowad]

This is exactly how i want to learn programming.. thanks

[bardiakhorshidi]

pandas is my home and excel is my room, a great journey in the native environment
thanks for that

[emilmubarakshin]

Great to see you diversifying your offerings

[vashisht]

And all these years I used good old vba to get list of filename in folder and then run another macro to change the filename...I also wanted to share that there are still a lot of people in this world who do all this you sir for helping all ....my client does not allow python to run on citirx that why I use vba....but I am myself a bit skeptical whether vba will be able to handle such large data set..

[tasfarsowad]

well it worked, but i had to do some modification.
First, i needed to install pip install xlrd



In the code, I had to add one more line.Here's the code:


import os
import pandas as pd

path = "F:\\python\\pythn csv project"
files = os.listdir(path)

for eachfile in files:
cleanfilename = eachfile.replace(".xls", "")
if eachfile.endswith(".xls"):
full_path = os.path.join(path, eachfile)
xlfile = pd.ExcelFile(full_path)
sheets = xlfile.sheet_names
for eachsheet in sheets:
sheedata = xlfile.parse(eachsheet)
csvname = cleanfilename + "-" + eachsheet + ".csv"
sheedata.to_csv(csvname, index = False)



the code used in this video was giving "file not found error"

then i add the line with the help of chat gpt "full_path = os.path.join(path, eachfile)"

[shubhabratadey]

Great learning. Can you please start another channel teaching us python from the very beginning and its uses so that people like us can learn from you as well? Just a thought though...🙂

2 questions with respect to this video.

1. Can this be used on a SharePoint folder? If yes, how to do that?
2. Can this code be run automatically? If my system is shut down, can it run following a specific schedule like using Power Automate or any other means?

[Raymclau]

Chandeep, I appreciate your videos very much
Thank you so much you are just an awesome teacher

[HachiAdachi]

Power Automate would be another option especially if no-code-low-code is your jam. I have no idea about performance, though.

[victor_wang_]

Nice video. A couple remarks: If you're going to use scripting to convert the files to csv, why not just combine them all into 1 csv? Also, why not just use vba which requires no installation or IT privileges?

[tak]

This is really good Chandeep, thank you!

[SWor-tjnb]

I try to covert excel size 40 mb to csv that using more time about 4-5 mins
in my opinion, this solution might be suitable for excel small size

[upmdosadno]

i would recommend using path its core module and easier to work. In my opinion

[nikhilmartha]

That's a great video. I have a doubt, like if we wanted to do the same with the file in aws S3 instead of local file. How to proceed.

[tmaris]

Great! Definitely trying this. Thanks!

[bhagyapante]

Hi ..Could you please let me know how we can handle if there are filters on columns..I used a code similar to yours but it is not working on the files with filters

[vinaykhankari]

Dear sir please share video on creating pivot table reports using pandas.

[manideepak]

Thank you upgrading our knowledge chandeep
A small doubt
Can I convert a structureed table in a Excel sheet to a CSV using python

[yossiper]

What kind of performance problem do you have with excel as data source ? Is it the refresh time of the report taking too long ? Or is it when you interact with the report ( slice etc) ?

[krishnapatel]

Pls make a video for creating XML files.

[KuldeepSingh-nqvi]

Nice Video Bro, pls upload next video of M Language.