Skip to main content

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        

Metadata

Release files for excel2dict 0.0.2

For a detailed explanation of source distributions (sdists) and built distributions (wheels), please see the package formats documentation.

Source distribution (sdist)

Source distribution for excel2dict 0.0.2
File Size Uploaded Provenance Actions
excel2dict-0.0.2.tar.gz 6.3 kB Without provenance Details for excel2dict-0.0.2.tar.gz

Built distribution (wheel)

Table of built distributions (wheels) for excel2dict 0.0.2
File Interpreter ABI Platform Actions
excel2dict-0.0.2-py3-none-any.whl Python 3 none any Details for excel2dict-0.0.2-py3-none-any.whl

Total release size: 14.1 kB

Release files / excel2dict-0.0.2.tar.gz

Download URL excel2dict-0.0.2.tar.gz
Size 6.3 kB
Tags Source
SHA-256 checksum
How to use checksums
df6ddaf4ebbcb410718eac1847925cba663b32b46153c3888d91ab826abfed1f
BLAKE2b-256 checksum
How to use checksums
3e6613994c5277964c5e5e5c466e204def0ee4fcf3c672fc380ed79b530d5bc7
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
No
Uploaded via twine/1.13.0 pkginfo/1.5.0.1 requests/2.22.0 setuptools/40.8.0 requests-toolbelt/0.9.1 tqdm/4.32.2 CPython/3.7.4rc1+

Release files / excel2dict-0.0.2-py3-none-any.whl

Download URL excel2dict-0.0.2-py3-none-any.whl
Size 7.8 kB
Tags Python 3
SHA-256 checksum
How to use checksums
664de22427c0ce1faec1cda91fa876111502344309c04971dc8a80c173c518f9
BLAKE2b-256 checksum
How to use checksums
2675c4901603b30c9ec1044c919522e9f6b40a03c334235d67ea93b232d60af3
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
No
Uploaded via twine/1.13.0 pkginfo/1.5.0.1 requests/2.22.0 setuptools/40.8.0 requests-toolbelt/0.9.1 tqdm/4.32.2 CPython/3.7.4rc1+

Security for excel2dict 0.0.2

Provenance

Provenance describes where a file came from. On PyPI, provenance is shared via attestations, which provide a verifiable record of the build or publishing details. View details, limitations and caveats.

No provenance found

None of the files in this release were uploaded with provenance metadata.

A note on package security

PyPI is a repository for distributing packages, not a service for auditing them. While third-party security researchers and our staff do their best to detect and remove malicious packages, PyPI cannot guarantee the quality, security or suitability of any given package.

To learn more about protecting yourself from malicious actors, visit our package security documentation.

Release history Release notifications | RSS feed

This release

0.0.2 This release

2 release files No provenance

Anthropic, PBC Visionary sponsor Bloomberg Visionary sponsor Hudson River Trading Visionary sponsor Meta Visionary sponsor NVIDIA Visionary sponsor Microsoft Sustainability sponsor Depot Continuous Integration AWS Cloud computing and Security Sponsor Datadog Monitoring Fastly CDN Google Download Analytics Sentry Error logging StatusPage Status page