Convert Excel Files to CSV using Python | Working with Large Excel Files in Power BI
Рет қаралды 16,195
Күн бұрын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 KZfaq Channel Below
kzfaq.info/love/GQE...
License: Creative Commons Attribution-ShareAlike 4.0 International (CC BY-SA 4.0)
Full license here: creativecommons.org/licenses
@Raymclau 2 жыл бұрын
Chandeep, I appreciate your videos very much Thank you so much you are just an awesome teacher
@tmaris 2 жыл бұрын
Great! Definitely trying this. Thanks!
@emilmubarakshin49 25 күн бұрын
Great to see you diversifying your offerings
@MaddyMugunth 2 жыл бұрын
Just wow, Thanks a bunch Chandeep.
@vashisht1 Жыл бұрын
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..
@tak0331 20 күн бұрын
This is really good Chandeep, thank you!
@bardiakhorshidi5711 2 жыл бұрын
pandas is my home and excel is my room, a great journey in the native environment thanks for that
@christopherlawes9286 Жыл бұрын
Super good instruction. Thank you!
@GoodlyChandeep Жыл бұрын
Glad you enjoyed it!
@daphnednls 10 ай бұрын
Great solution!
@bhuvanashri-yp5cw 11 ай бұрын
nice video and superb explanation
@nikhilmartha4832 Жыл бұрын
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.
@BigtsGameTavern Жыл бұрын
Awesome, this was very helpful 👍
@GoodlyChandeep Жыл бұрын
Glad it was helpful!
@victor_wang_1 2 жыл бұрын
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?
@shubhabratadey 21 күн бұрын
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?
@abhijeetshetye8785 Жыл бұрын
Very helpful video👍🏻🤘🏻 Thnx
@GoodlyChandeep Жыл бұрын
Glad it was helpful!
@NavinKumar-tv9hg Жыл бұрын
beautiful explanation
@GoodlyChandeep Жыл бұрын
Glad you think so!
@techuser876 Жыл бұрын
Very good tutorial👍
@GoodlyChandeep Жыл бұрын
Glad you like it !
@HachiAdachi 2 жыл бұрын
Power Automate would be another option especially if no-code-low-code is your jam. I have no idea about performance, though.
@johnpineda7692 9 ай бұрын
Thanks Bro!
@thebhaskarjoshi 2 жыл бұрын
New Learning!
@inaammuaz2284 2 жыл бұрын
Thank you
@smartwork4768 2 жыл бұрын
Thank you dear.
@GoodlyChandeep 2 жыл бұрын
💚
@X_x_kingfisher_x_X Жыл бұрын
MashaAllah you were good in there.
@GoodlyChandeep Жыл бұрын
Thank you!
@marcosoliveira8731 8 ай бұрын
Nicely done sir. +1
@manideepak6820 2 жыл бұрын
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
@josericardo3015 Жыл бұрын
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
@hemant5757 2 жыл бұрын
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/
@yossiper7221 2 жыл бұрын
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/
@bhagyapante3022 4 ай бұрын
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
@SWor-tj2nb 2 жыл бұрын
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
@elyorkeldiyorov3641 Жыл бұрын
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.
@elicesroman7500 2 жыл бұрын
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
@mohamedchatar4393 2 жыл бұрын
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
@krishnapatel3140 2 жыл бұрын
Pls make a video for creating XML files.
@aliyaqoob750 2 жыл бұрын
Nice but why you did not go for vba?
@vinaykhankari6703 Жыл бұрын
Dear sir please share video on creating pivot table reports using pandas.
@upmdosadno 2 жыл бұрын
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.
@sauravkragrawal 9 ай бұрын
How to loop through sub folders ?
@venkateswaram2285 Жыл бұрын
xlfile = pd.ExcelFIle(eachfile) is not working throwing error as Filenot found error eventhough we installed latest openyxl
@KuldeepSingh-nq1vi 2 жыл бұрын
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
@Aviji2525 2 жыл бұрын
Any new batch coming ??
@mfachry3012 Жыл бұрын
AttributeError: 'ExcelFile' object has no attribute 'to_csv' How do i fix this ?
@AMIN-ub8ct Жыл бұрын
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.
@einoconsult5563 25 күн бұрын
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 ?
Enderestories
Рет қаралды 8 МЛН
xunyu
Рет қаралды 787 М.