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 -
goodly.co.in/learn-dax-powerbi/
✔️ Power Query Course-
goodly.co.in/learn-power-query/
✔️ Master Excel Step by Step-
goodly.co.in/learn-excel/
✔️ Business Intelligence Dashboards-
goodly.co.in/learn-excel-dash...
===== LINKS 🔗 =====
Blog 📰 - www.goodly.co.in/blog/
Corporate Training 👨🏫 - www.goodly.co.in/training/
Need my help on a Project 💻- www.goodly.co.in/consulting/
===== CONTACT 🌐 =====
Twitter - / chandeep2786
LinkedIn - / chandeepchhabra
Email - goodly.wordpress@gmail.com
===== 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
- Source: goo.gl/fh3rEJ
Official After The Fall KZread Channel Below
kzread.info/dron/GQE.html...
License: Creative Commons Attribution-ShareAlike 4.0 International (CC BY-SA 4.0)
Full license here: creativecommons.org/licenses
Пікірлер: 70
Great to see you diversifying your offerings
Great! Definitely trying this. Thanks!
Chandeep, I appreciate your videos very much Thank you so much you are just an awesome teacher
Just wow, Thanks a bunch Chandeep.
Great solution!
This is really good Chandeep, thank you!
Super good instruction. Thank you!
@GoodlyChandeep
Жыл бұрын
Glad you enjoyed it!
pandas is my home and excel is my room, a great journey in the native environment thanks for that
nice video and superb explanation
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 manually.....thank 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..
Awesome, this was very helpful 👍
@GoodlyChandeep
Жыл бұрын
Glad it was helpful!
Thanks Bro!
Thank you
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?
beautiful explanation
@GoodlyChandeep
Жыл бұрын
Glad you think so!
Very helpful video👍🏻🤘🏻 Thnx
@GoodlyChandeep
Жыл бұрын
Glad it was helpful!
New Learning!
Thank you dear.
@GoodlyChandeep
2 жыл бұрын
💚
Very good tutorial👍
@GoodlyChandeep
Жыл бұрын
Glad you like it !
Nicely done sir. +1
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.
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?
Power Automate would be another option especially if no-code-low-code is your jam. I have no idea about performance, though.
MashaAllah you were good in there.
@GoodlyChandeep
Жыл бұрын
Thank you!
Pls make a video for creating XML files.
Dear sir please share video on creating pivot table reports using pandas.
Nice but why you did not go for vba?
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
i would recommend using path its core module and easier to work. In my opinion
@GoodlyChandeep
2 жыл бұрын
Can you please share the exact script!
@Crow2525
2 жыл бұрын
I think path from pathlib. A - avoids double "//" in your path, B - allows you to chose the basename, rather than replace the ext, C - allows you to loop through the extensions rather than whole filename.
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) ?
@GoodlyChandeep
2 жыл бұрын
See this - blog.crossjoin.co.uk/2018/08/02/comparing-the-performance-of-csv-and-excel-data-sources-in-power-query/
Thank you upgrading our knowledge chandeep A small doubt Can I convert a structureed table in a Excel sheet to a CSV using python
@GoodlyChandeep
2 жыл бұрын
I haven't tried it but I'll try to follow this approach. - Using OpenPyXl library I'll try to get the table pulled up in Python - Then convert that table to a csv.
@manideepak6820
2 жыл бұрын
@@GoodlyChandeep thank you chandeep
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
@ryansodhi1815
Жыл бұрын
Same I have a 100 mb file and I'm not sure how much time it would take to make that into csv
Many Tks for the video, Chandeep! If the data inside the sheet starts in line 6, how to adjust the pyhton code?
@GoodlyChandeep
Жыл бұрын
You can use the Table.Skip function in Power Query
Any new batch coming ??
Hi chandeep can you also show how much good performance you get when you loaded that csv data instead of xl data...you said earlier it was taking 2.5 hours in processing xl data in power query
@GoodlyChandeep
2 жыл бұрын
See this - blog.crossjoin.co.uk/2018/08/02/comparing-the-performance-of-csv-and-excel-data-sources-in-power-query/
How to loop through sub folders ?
Nice Video Bro, pls upload next video of M Language.
@GoodlyChandeep
2 жыл бұрын
Next up is List.Accumulate 😉
@KuldeepSingh-nq1vi
2 жыл бұрын
Thanks Bro, After this ,pls make a video on "each" and underscore (_) statements in M Language Series.
@manideepak6820
2 жыл бұрын
@@GoodlyChandeep thank you and most waiting topic
xlfile = pd.ExcelFIle(eachfile) is not working throwing error as Filenot found error eventhough we installed latest openyxl
Thx for your video. I'm running the code in Jupyter lab and I had a couple of issues for example I could not produce the csv files and I could not use the argument 'eachfile' in the pd.excel.File. Any idea? Do you think it is because I'm running the code in jupyter lab when it should be run on a proper IDE?
@GoodlyChandeep
2 жыл бұрын
I am not sure.. try doing that in VS Code.
@vigneshwarannallasamy
2 жыл бұрын
Have you tried using the exact name pd.ExcelFile(eachfile)? You said pd.excel.File. If you used this then it's wrong. Try the above one. Generally using IDEs will show suggestions as you type. Jupyter lab is great to work on data visualization but for writing scripts it is better to use IDEs like Pycharm, VS....
@ulearning4life364
Жыл бұрын
It is not because of using a different IDE, Pd.excelfile is a pandas method and not a python base method. Therefore, You must install Pandas package before you can import pandas. CHANDEEP had done the pip install but was not covered in the video
Hello sir, this was really helpful! Thank you for that, but while I am converting excel files to csv I am losing zeros before the number. For example if I have '00356' in excel file, after converting it into csv it is becoming '356'.
@codingwannabe
Жыл бұрын
Here are two possible scenarios for that, either you need to specify and change the data type to string before converting to csv or you're viewing the data in excel. When you view data in csv format using excel it will not display the leading zeros even though they still exist in the file itself . You can test this by importing the csv file into a new file using "get data" under the "Data" tab and making sure the format of the data in the power query is in string format. Or you can read that same CSV file back into python and print the data. You'll notice that, if the data itself has the right format, the zero still exists.
Hello there, I would like to thank you for this great video but I also wanted to ask you about something: when I did this python script at the line where I had to convert the eachfile variable to an Excel by using xlfile = pd.ExcelFile(eachfile) I had this error : FileNotFoundError: [Errno 2] No such file or directory: 'C:\\Users\\$company_sales.xlsx' It is beacause of the hidden files , how can I avoid this ?
@GoodlyChandeep
2 жыл бұрын
try wrapping the path in double quotes
AttributeError: 'ExcelFile' object has no attribute 'to_csv' How do i fix this ?
Hello Guys, Even after Power Query with all its solutions. How really its needed to learn Python or R. I know excel can't handle more than 1M row, but are there any other reasons. I am asking because with Excel hypnotically I can feel the data with my hand
@StefanoVerugi
Жыл бұрын
with Excel & PQ you don't have 1M limitation, for data handling R+tidyverse library give you lots of power, Python is always good to learn because is so widespread. If you manage to pass the initial learning curve you won't regret it.
I see that the fist row of my data is : Unnamed: 0,Unnamed: 1,Unnamed: 2,Unnamed: 3,Unnamed: 4,Unnamed: 5,Unnamed: 6,Unnamed: 7,Unnamed: 8 because I have 9 columns, any clue ? Can it handle multiple headers files ? Does the data has to start in A1 ? In the comments @upmdosadno offered a different method to use path , @Crow2525 shared the script After a year, can we have a feedback or an updated version Anyway to make the variable path dynamic that it picks up the path where the script is ?