Thanks Thanks:  7
Showing results 1 to 7 of 7

Thread: Python scripts for combining excel files

  1. #1
    Administrator Reputation: 1348 GEZA's Avatar
    Join Date
    2007-03-12
    Location
    CN
    Posts
    1,426


    3 out of 3 members found this post helpful.

    Default Python scripts for combining excel files

    I use this scripts to combined OSS stats from multiple Huawei PRS.

    Hope it helps.

    written in python 3

    Code:
    import pandas as pdimport os
    import datetime
    
    
    rootDir = os.getcwd()
    filelist = []
    excellist = []
    
    
    today = datetime.date.today()
    delta = datetime.timedelta(days=-1)
    yesterday = today + delta
    
    
    startDate = yesterday.strftime('%Y%m%d')
    
    
    
    
    #get all xlsx files 
    for filename in os.listdir(rootDir):
            if os.path.splitext(filename)[1] == '.xlsx':
                filelist.append(filename)
    
    
    #file name of combined file
    filename_combined = startDate + 'Huawei All.xlsx'
    
    
    writer = pd.ExcelWriter(filename_combined)
    
    
    #read info to list
    for excelfile in filelist:
        excellist.append(pd.read_excel(excelfile))
    
    
    #write combined info to excel file
    newdf = pd.concat(excellist,sort=False)
    newdf.to_excel(writer,index=False)
    
    
    #save excel file
    writer.save()

  2. Thanks electron, carver, mohsenJaz thanked for this post
  3. # ADS
    Circuit advertisement
    Join Date
    Always
    Location
    Advertising world
    Posts
    Many
     

  4. #2
    Member Reputation: 439
    Join Date
    2014-03-13
    Location
    Earth
    Posts
    178


    Default Re: Python scripts for combining excel files

    Or you can try "Power query" which is a EXCEL plug-in published by Microsoft

    it is free and it has lots of training material on internet

  5. Thanks carver thanked for this post
  6. #3
    VIP Member Reputation: 1852 carver's Avatar
    Join Date
    2007-10-24
    Location
    Ukraine
    Posts
    563


    Default Re: Python scripts for combining excel files

    the beauty of scripts,
    in that they do all your work at night,
    often on Ubuntu Server,
    also add a couple of lines to the weekly report ))
    /carv

  7. #4
    Member Reputation: 83
    Join Date
    2010-05-09
    Posts
    144


    Default Re: Python scripts for combining excel files

    Quote Originally Posted by BlackX View Post
    Or you can try "Power query" which is a EXCEL plug-in published by Microsoft

    it is free and it has lots of training material on internet
    agree totally i user power query since two years now , its really powerfull , i even stopped using PRS

  8. #5
    Senior Member Reputation: 372
    Join Date
    2009-12-29
    Location
    hidden leaf village
    Posts
    222


    Default Re: Python scripts for combining excel files

    Hi,

    I'm very fluent in python, but I prefer to use Power BI to do this kind of work. My reasons:
    1. Very intuitive
    2. Anyway after you combine the files I would like to make some chart
    3. Visualizations a very beautiful (not just the boring Excel charts we have like 20 years seeming)
    3. Power BI charts are dynamic, that means you can create for example a map to show sites by clicking a table
    4. It is also FREE

  9. Thanks carver thanked for this post
  10. #6
    Member Reputation: 238
    Join Date
    2013-07-29
    Posts
    340


    Default Re: Python scripts for combining excel files

    @jkpllan3
    could share some video or tutorial to do it with Power BI , it will be very helpful
    I mean how you are Power BI in RF
    Last edited by Boraa; 2018-12-08 at 04:21 PM

  11. #7
    Member Reputation: 83
    Join Date
    2010-05-09
    Posts
    144


    Default Re: Python scripts for combining excel files

    Quote Originally Posted by Boraa View Post
    @jkpllan3
    could share some video or tutorial to do it with Power BI , it will be very helpful
    I mean how you are Power BI in RF
    https://www.edx.org/course/analyzing...soft-dat207x-0

  12. Thanks dacoder, NTCM thanked for this post

Bookmarks

Bookmarks

Posting Rules

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •