This script will generate insert sql accroding to the config and data in the excel file
- Python
- xlrd (
pip install xlrd)
python excel2SQL.py excel_file sheet_index
| args | description |
|---|---|
| excel_file | if excel file and excel2SQL.py are in the same directory excel file path, it can be excel file name, otherwise it should be absolute path |
| sheet_index | excel worksheet index |
Line 1: TABLE_NAME
Line 2: COLUMN_NAME
Line 3: COLUMN_TYPE
| type | value | result in sql |
|---|---|---|
| string | grettygirl | values ('grettygirl') |
| int | 28 | values (28) |
| float | 12.1 | values (12.1) |
| function | fn_get_data_id | select fn_get_data_id from dual |
Line 4: data
- COLUMN_TYPE config contain 'function'
INSERT TB_USER (USER_ID, NAME, AGE, EMAIL, ADDRESS, SCORE, BIRTHDAY, DATA_ID )
SELECT 'golden_three_fat', '金三胖', 28, 'rzh0001@qq.com', 'Nanjing', 12.1, '19900416', fn_get_data_id FROM DUAL;
- COLUMN_TYPE config do not contain 'function'
INSERT TB_USER (USER_ID, NAME, AGE, EMAIL, ADDRESS, SCORE, BIRTHDAY )
VALUES ('golden_three_fat', '金三胖', 28, 'rzh0001@qq.com', 'Nanjing', 12.1, '19900416' );