Skip to main content

A converting excel file to python data structure package

Project description

excel2dict

excel2dict support easy loading data from excel files.

Intalling

pip install excel2dict

Quick Over View

Assuming below sample data was saved as excel file named Book.xslx.

foo bar
'a' 1
'b' 2

Simply, To convert to JSON format text file with command line.

$ excel2dict Book.xlsx > out.json
$ less out.json
[
  {
    "foo": 'a',
    "bar": 1 
  },
  {
    "foo": 'b',
    "bar": 2 
  }
]

As well, you can do the same thing in python script.

>>> import excel2dict
>>> excel2dict.to_dict('Book.xlsx')
[
  {
    "foo": 'a',
    "bar": 1 
  },
  {
    "foo": 'b',
    "bar": 2 
  }
]

Using Sheet Definition

As above example, at simple usage, some data representing dedicated data type(boolean, date, etc) in excel can not be handled usefully.

For this use case, you use a sheet definition file. if exists, excel2dict load a definition file named sheet_definition.yaml from the directory which a target excel file is saved in or optional argument specified to by -s.

sample definition

sheets:
- name: Members
  cols: 
    - name: member_no
      schema:
        type: int
    - name: name
      schema:
        type: string
    - name: is_active
      schema:
        type: bool

Label Definition

Normally, sheet name is named in a business context in which the name may include multibyte character, space, etc. but for handling in script or JSON text file, named only ascii character is useful. For this, you can add label definition to the definition.

For Sheet

sheets:
- name: members
  label: New Members

For Column

cols: 
  - name: name
    label: Member's Name

Data Type Definition

excel2dict suppot below data type.

int

schema:
  type: int

str

schema:
  type: str

bool

schema:
  type: bool

date

schema:
  type: date

datetime

schema:
  type: datetime

For needing to adjust timezone, specifing offset is avalable.

schema:
  type: datetime
  offset: 9

For example, 2019-07-26T09:00:00 in JST, this setting convert the datetime to 2019-07-26T00:00:00

A Bit Odd Function

For rare use case, you may need to convert values defined in other sheets as nested structure.

For example, assuming there were 2 sheets as below,

Sheet1

User Access Right
Scott Admin
Tom General

Sheet2

Access Right Read Write
Admin O O
General O X

Sheet Definition

On Sheet1 setting, specify type with ref and sheet with reference sheet name.

sheets:
- name: Sheet1
  columns:
    - name: user
      label: User
      schema:
        type: int
    - name: access_right
      label: Access Right
      schema:
        type: ref
        sheet: Sheet2
- name: Sheet2
  columns:
    - name: ref_name
      label: Access Right

Output

You can get an output like below format defining as ref type.

[
    {
      "user": "Scott",
      "access_right": {
        "Read": "O",
        "Write": "O"
      }
    },
    {
      "user": "Tom",
      "access_right": {
        "Read": "O",
        "Write": "X"
      }
    }
  ]

How to specify

Required setting are type and sheet.

  • type: ref
  • sheet: reference sheet name
schema:
  type: ref
  sheet: Sheet2

For array, specifing is_array is avalable.

schema:
  type: ref
  sheet: Sheet2
  is_array: true        

Project details


Download files

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

Files for excel2dict, version 0.0.2
Filename, size File type Python version Upload date Hashes
Filename, size excel2dict-0.0.2-py3-none-any.whl (7.8 kB) File type Wheel Python version py3 Upload date Hashes View hashes
Filename, size excel2dict-0.0.2.tar.gz (6.3 kB) File type Source Python version None Upload date Hashes View hashes

Supported by

Elastic Elastic Search Pingdom Pingdom Monitoring Google Google BigQuery Sentry Sentry Error logging AWS AWS Cloud computing DataDog DataDog Monitoring Fastly Fastly CDN DigiCert DigiCert EV certificate StatusPage StatusPage Status page