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
Release history Release notifications | RSS feed
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.
Source Distribution
Built Distribution
Hashes for excel2dict-0.0.2-py3-none-any.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | 664de22427c0ce1faec1cda91fa876111502344309c04971dc8a80c173c518f9 |
|
MD5 | 4589f07d841abb52949abeb00d940bf6 |
|
BLAKE2b-256 | 2675c4901603b30c9ec1044c919522e9f6b40a03c334235d67ea93b232d60af3 |