This is exactly how i want to learn programming.. thanks mannnnn
@bardiakhorshidi57112 жыл бұрын
pandas is my home and excel is my room, a great journey in the native environment thanks for that
@victor_wang_12 жыл бұрын
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?
@shubhabratadey2 ай бұрын
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?
@HachiAdachi2 жыл бұрын
Power Automate would be another option especially if no-code-low-code is your jam. I have no idea about performance, though.
@emilmubarakshin492 ай бұрын
Great to see you diversifying your offerings
@tasfarsowad7612Ай бұрын
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)"
@vashisht12 жыл бұрын
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..
@Raymclau2 жыл бұрын
Chandeep, I appreciate your videos very much Thank you so much you are just an awesome teacher
@tak03312 ай бұрын
This is really good Chandeep, thank you!
@yossiper72212 жыл бұрын
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) ?
@GoodlyChandeep2 жыл бұрын
See this - blog.crossjoin.co.uk/2018/08/02/comparing-the-performance-of-csv-and-excel-data-sources-in-power-query/
@SWor-tj2nb2 жыл бұрын
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
@bhagyapante30226 ай бұрын
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
@MaddyMugunth2 жыл бұрын
Just wow, Thanks a bunch Chandeep.
@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.
@tmaris2 жыл бұрын
Great! Definitely trying this. Thanks!
@upmdosadno2 жыл бұрын
i would recommend using path its core module and easier to work. In my opinion
@GoodlyChandeep2 жыл бұрын
Can you please share the exact script!
@Crow25252 жыл бұрын
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.
@manideepak68202 жыл бұрын
Thank you upgrading our knowledge chandeep A small doubt Can I convert a structureed table in a Excel sheet to a CSV using python
@GoodlyChandeep2 жыл бұрын
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.
@manideepak68202 жыл бұрын
@@GoodlyChandeep thank you chandeep
@christopherlawes9286 Жыл бұрын
Super good instruction. Thank you!
@GoodlyChandeep Жыл бұрын
Glad you enjoyed it!
@sauravkragrawal11 ай бұрын
How to loop through sub folders ?
@X_x_kingfisher_x_X Жыл бұрын
MashaAllah you were good in there.
@GoodlyChandeep Жыл бұрын
Thank you!
@vinaykhankari6703 Жыл бұрын
Dear sir please share video on creating pivot table reports using pandas.
@bhuvanashri-yp5cw Жыл бұрын
nice video and superb explanation
@daphnednls11 ай бұрын
Great solution!
@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
@NavinKumar-tv9hg Жыл бұрын
beautiful explanation
@GoodlyChandeep Жыл бұрын
Glad you think so!
@mfachry3012 Жыл бұрын
AttributeError: 'ExcelFile' object has no attribute 'to_csv' How do i fix this ?
@BigtsGameTavern2 жыл бұрын
Awesome, this was very helpful 👍
@GoodlyChandeep2 жыл бұрын
Glad it was helpful!
@krishnapatel31402 жыл бұрын
Pls make a video for creating XML files.
@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.
@einoconsult55632 ай бұрын
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 ?
@hemant57572 жыл бұрын
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
@GoodlyChandeep2 жыл бұрын
See this - blog.crossjoin.co.uk/2018/08/02/comparing-the-performance-of-csv-and-excel-data-sources-in-power-query/
@abhijeetshetye87852 жыл бұрын
Very helpful video👍🏻🤘🏻 Thnx
@GoodlyChandeep2 жыл бұрын
Glad it was helpful!
@techuser876 Жыл бұрын
Very good tutorial👍
@GoodlyChandeep Жыл бұрын
Glad you like it !
@KuldeepSingh-nq1vi2 жыл бұрын
Nice Video Bro, pls upload next video of M Language.
@GoodlyChandeep2 жыл бұрын
Next up is List.Accumulate 😉
@KuldeepSingh-nq1vi2 жыл бұрын
Thanks Bro, After this ,pls make a video on "each" and underscore (_) statements in M Language Series.
@manideepak68202 жыл бұрын
@@GoodlyChandeep thank you and most waiting topic
@venkateswaram2285 Жыл бұрын
xlfile = pd.ExcelFIle(eachfile) is not working throwing error as Filenot found error eventhough we installed latest openyxl
@johnpineda769211 ай бұрын
Thanks Bro!
@marcosoliveira87319 ай бұрын
Nicely done sir. +1
@thebhaskarjoshi2 жыл бұрын
New Learning!
@inaammuaz22842 жыл бұрын
Thank you
@aliyaqoob7502 жыл бұрын
Nice but why you did not go for vba?
@smartwork47682 жыл бұрын
Thank you dear.
@GoodlyChandeep2 жыл бұрын
💚
@elicesroman75002 жыл бұрын
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?
@GoodlyChandeep2 жыл бұрын
I am not sure.. try doing that in VS Code.
@vigneshwarannallasamy2 жыл бұрын
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....
@ulearning4life3642 жыл бұрын
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
@mohamedchatar43932 жыл бұрын
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 ?
@GoodlyChandeep2 жыл бұрын
try wrapping the path in double quotes
@AMIN-ub8ct2 жыл бұрын
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.