Skip to main content

Spreadsheet to dictionary converter and data explorer

Project description

Introduction

xl2dict is a library to convert spreadsheets in to dictionary. The input is a spreadsheet (xls or xlsx) and the output is a list of dictionaries. The first row in the spreadsheet is treated as the header rows and each of the cells in the first row assumes the keys in the output python dictionary. This python module will also enable the user to seamlessly search for a data row in the speadsheet by specifying keyword / keywords . All the data rows containing the specified keyword in any of their cells will be returned. This behavior is extremely useful in implementing data driven and keyword driven tests and also in implementing object repositories for most opensource test automation tools.This module will also enable the users to write data in to spreadsheet rows matching a specified keyword / keywords, a feature that can be used to store dynamic data between dependent tests.

Installation

To install xl2dict, type the following command in the command line

$ pip install xl2dict

Quickstart

1. convert_sheet_to_dict()

This method will convert excel sheets to dict. The input is path to the excel file or a sheet object. if file_path is None, sheet object must be provded. This method will convert only the first sheet. If you need to convert multiple sheets, please use the method fetch_data_by_column_by_sheet_name_multiple() and fetch_data_by_column_by_index_multiple().If you need to filter data by a specific keyword, specify the dict in filter_variables_dict like {column name : keyword} . Any rows that matches the keyword in the specified column will be returned. Multiple keywords can be specified.

Usage example:

myxlobject= XlToDict()
myxlobject.convert_sheet_to_dict(file_path="Users/xyz/Desktop/myexcel.xls", sheet="First Sheet",
                                 filter_variables_dict={"User Type" : "Admin", "Environment" : "Dev"})

2. fetch_data_by_column_by_sheet_name()

This method will convert the specified sheet in the excel file to dict. The input is path to the excel file . If sheet_name is not provided, this method will convert only the first sheet. If you need to convert multiple sheets, please use the method fetch_data_by_column_by_sheet_name_multiple() or fetch_data_by_column_by_sheet_index_multiple(). If you need to filter data by a specific keyword, specify the dict in filter_variables_dict like {column name : keyword} . Any rows that matches the keyword in the specified column will be returned. Multiple keywords can be specified.

Usage example:

myxlobject= XlToDict()
myxlobject.fetch_data_by_column_by_sheet_name(file_path="Users/xyz/Desktop/myexcel.xls",
                                              sheet_name="First Sheet",
                                              filter_variables_dict={"User Type" : "Admin", "Environment" : "Dev"})

3. fetch_data_by_column_by_sheet_index()

This method will convert the specified sheet in the excel file to dict. The input is path to the excel file . If sheet_index is not provided, this method will convert only the first sheet. If you need to convert multiple sheets, please use the method fetch_data_by_column_by_sheet_name_multiple() or fetch_data_by_column_by_sheet_index_multiple(). If you need to filter data by a specific keyword, specify the dict in filter_variables_dict like {column name : keyword} . Any rows that matches the keyword in the specified column will be returned. Multiple keywords can be specified.

Usage example:

myxlobject= XlToDict()
myxlobject.fetch_data_by_column_by_sheet_index(file_path="Users/xyz/Desktop/myexcel.xls",
                                               sheet_index=1,
                                               filter_variables_dict={"User Type" : "Admin", "Environment" : "Dev"})

4. fetch_data_by_column_by_sheet_name_multiple()

This method will convert multiple sheets in the excel file to dict. The input is path to the excel file . If sheet_names is not provided, this method will convert ALL the sheets.If you need to filter data by a specific keyword / keywords, specify the dict in filter_variables_dict like {column name : keyword} . Any rows that matches the keyword in the specified column will be returned. Multiple keywords can be specified.

Usage example:

myxlobject= XlToDict()
myxlobject.fetch_data_by_column_by_sheet_name_multiple(file_path="Users/xyz/Desktop/myexcel.xls",
                                                       sheet_names=["First Sheet","Some other sheet"],
                                                       filter_variables_dict={"User Type" : "Admin", "Environment" : "Dev"})

5. fetch_data_by_column_by_sheet_index_multiple()

This method will convert multiple sheets in the excel file to dict. The input is path to the excel file . If sheet_indices is not provided, this method will convert ALL the sheets.If you need to filter data by a specific keyword / keywords, specify the dict in filter_variables_dict like {column name : keyword} . Any rows that matches the keyword in the specified column will be returned. Multiple keywords can be specified.

Usage example:

myxlobject= XlToDict()
myxlobject.fetch_data_by_column_by_sheet_index_multiple(file_path="Users/xyz/Desktop/myexcel.xls",
                                                        sheet_indices=[0,1,4,7],
                                                        filter_variables_dict={"User Type" : "Admin", "Environment" : "Dev"})

6. fetch_matching_data_row_indices()

This method will fetch all the rows matching the specified filter. The input is path to the excel file . If sheet_name_index is not provided, this method will search the first sheet sheet. If you need to filter data by a specific keyword / keywords, specify the dict in filter_variables_dict like {column name : keyword} . All the row indices that matches the keyword in the specified column will be returned. Multiple keywords can be specified.

Usage example:

myxlobject= XlToDict()
myxlobject.fetch_matching_data_row_indices(file_path="Users/xyz/Desktop/myexcel.xls",
                                           sheet_name_index="First Sheet",
                                           filter_variables_dict={"User Type" : "Admin", "Environment" : "Dev"})

myxlobject.fetch_matching_data_row_indices(file_path="Users/xyz/Desktop/myexcel.xls",
                                           sheet_name_index=5,
                                           filter_variables_dict={"User Type" : "Admin", "Environment" : "Dev"})

7. write_data_to_column()

This method will write data in to the specified column of all the rows matching the specified filter. The input is path to the excel file .If sheet_name is not provided, this method will write data in to the specified column in the first sheet sheet. If you need to write data in to rows by a specific keyword / keywords, specify the dict in filter_variables_dict like {column name : keyword} .The specified data will be written in the specified column in all rows that matches the keyword. Multiple keywords can be specified.

Usage example:

myxlobject= XlToDict()
myxlobject.write_data_to_column(file_path="Users/xyz/Desktop/myexcel.xls",column_name="Workorder Number",
                                data="999999999", sheet_name="First Sheet",
                                filter_variables_dict={"Test Case" : "Create Work Order", "Environment" : "Dev"})

Project details


Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Source Distribution

xl2dict-0.1.2.tar.gz (5.0 kB view details)

Uploaded Source

Built Distributions

xl2dict-0.1.2.macosx-10.10-x86_64.zip (9.7 kB view details)

Uploaded Source

xl2dict-0.1.2.macosx-10.10-x86_64.tar.gz (6.4 kB view details)

Uploaded Source

xl2dict-0.1.2.macosx-10.10-x86_64.exe (74.9 kB view details)

Uploaded Source

xl2dict-0.1.2-py2.7.egg (8.5 kB view details)

Uploaded Egg

File details

Details for the file xl2dict-0.1.2.tar.gz.

File metadata

  • Download URL: xl2dict-0.1.2.tar.gz
  • Upload date:
  • Size: 5.0 kB
  • Tags: Source
  • Uploaded using Trusted Publishing? No

File hashes

Hashes for xl2dict-0.1.2.tar.gz
Algorithm Hash digest
SHA256 5985b1a3326138f5f7d899075d8e7099734f925ec61b982ca61d2185f9136aeb
MD5 83d159c56a96cc4f92b4e2c6f824fac5
BLAKE2b-256 306485b2005804e0b0d447c9b20eff32a295a98f3832ab80aa6edc08407aa502

See more details on using hashes here.

File details

Details for the file xl2dict-0.1.2.macosx-10.10-x86_64.zip.

File metadata

File hashes

Hashes for xl2dict-0.1.2.macosx-10.10-x86_64.zip
Algorithm Hash digest
SHA256 91b47857414b7c055e164324000d522423d9cce075bda7edb0faf91ec2e39369
MD5 4de615adfb1926b8daea47e276be28a3
BLAKE2b-256 7cea1acb4dd21eff066bc380efd80496272f50c800ba6d3d6071b8eaf1577d14

See more details on using hashes here.

File details

Details for the file xl2dict-0.1.2.macosx-10.10-x86_64.tar.gz.

File metadata

File hashes

Hashes for xl2dict-0.1.2.macosx-10.10-x86_64.tar.gz
Algorithm Hash digest
SHA256 4cb6e62fb7501ad8ba8ed25b9566dbd8660e541ebb7e81cd396ad9c05bb80c93
MD5 625dc99e0f96be458f1d2a34ed7bc27b
BLAKE2b-256 2b877b1be7f124e341f5579be008e856db6850a9dbbff7a69338914142114e8c

See more details on using hashes here.

File details

Details for the file xl2dict-0.1.2.macosx-10.10-x86_64.exe.

File metadata

File hashes

Hashes for xl2dict-0.1.2.macosx-10.10-x86_64.exe
Algorithm Hash digest
SHA256 f0ec4d774e06b21881a922d124da33010b06a903fd2d21cd03fdd3c772a19ae9
MD5 70b829ae3168a4d7c420e18c2ad7b7b8
BLAKE2b-256 f94365e9810ef800962209cbdf3d833c62b29ffcc87b5f198bac743a1f79bf59

See more details on using hashes here.

File details

Details for the file xl2dict-0.1.2-py2.7.egg.

File metadata

  • Download URL: xl2dict-0.1.2-py2.7.egg
  • Upload date:
  • Size: 8.5 kB
  • Tags: Egg
  • Uploaded using Trusted Publishing? No

File hashes

Hashes for xl2dict-0.1.2-py2.7.egg
Algorithm Hash digest
SHA256 9bc1a66e73d1a554a53f36bae182e2fb15c8c789015fa623a5be9fad7171735c
MD5 679949ceb0935a05fee6470e12948f19
BLAKE2b-256 0615061c53dc4ba9dc09026b018a78c7ddbf9a3f213c29f9208deed58da3377a

See more details on using hashes here.

Supported by

AWS Cloud computing and Security Sponsor Datadog Monitoring Fastly CDN Google Download Analytics Pingdom Monitoring Sentry Error logging StatusPage Status page