Manage and extract data using python and Excel tables

User data such as setting parameters are usually stored in txt, json or perhaps .csv format. One alternative to the common types of storing simple data sets used for initial settings is through using  Excel tables. Excel represents a good way to store and view tables with the extensive formatting options and  different tabs for multiple storage. This provides an edge over txt or other simple data storing. The only difficulty might be that it is not easy to retrieve the data easily unlike in .csv or txt file.

The script below will utilize the Excel tables to extract various information such as setting files, parameters values and at the same time maintain a neat way of viewing and changing all the parameters.

The script will required pyExcel which is interface module between python and excel. The concept is to retrieve all the tables and rows specified within the start and closing tag.

The advantages are:

  1. Multiple tags can be used within the same excel sheet or other excel sheets as well.
  2. Number of columns can be edited easily.
  3. Space can be inserted between rows for easy viewing.
  4. Comment can be inserted so that particular row data can be easily bypassed.
  5. Normal excel formatting can be enabled without disruption to the data retrieved hence allowing easy viewing of data.

Below is a sample table which data will be extracted to be use in subsequent python functions or modules. Note the formatting is up to the user preferences.

sample table

The script run and output are as below. Note that there are various output format to query from. Also notice that the space between the data and those commented rows are being taken care off. The script is available in GitHub.

        xls_set_class = XlsExtractor(fname = r'C:\Python27\Lib\site-packages\excel_table_extract\testset.xls', sheetname= 'Sheet1',
                                     param_start_key = 'start//', param_end_key = 'end//',
                                     header_key = 'header#3//', col_len = 3)
        xls_set_class.open_excel_and_process_block_data()

        print xls_set_class.data_label_list
        ## >>> [u'label1', u'label3']

        print xls_set_class.data_value_list
        ## >>> [[2.0, 3.0], [8.0, 9.0]]

        print xls_set_class.label_value_dict
        ## >>> {u'label1': [2.0, 3.0], u'label3': [8.0, 9.0]}

        print xls_set_class.header_list
        ## >>> [u'header1', u'header2', u'header3']

5 comments

      1. Kok Hua, sorry but I don’t quite follow you. The code has this:

        try:
        import pyET_tools.win_program_manipulate as win
        except:
        print ‘Win function not installed’
        print “Some function will be disabled if module is not present”

        And it shows up as error since the import fails. So I was hoping I could get the full win function in place.

      2. Hi Hwa Seong, I not really sure this will be an import issue as it is under the try statement, meaning if the import fails, program will continue running. This win function only will close an existing window program and not in use in Excel_table_extract so I do not think it will be a problem. Can you help to screenshot the exact error so I can look at it? Thanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s