excel2sql ============================== Skills: SQL Python XML xml.etree.ElementTree Excel ============================== Description: A python script that converts an Excel XML spreadsheet into an sqlite database copying the rows and columns into the database just the way they are in the Excel file. The code automatically cleans the XML file in order for it to be readable by the python library. It prompts the user whether to insert empty rows into the database (just as they appear in the Excel file) or not. Note: the code supports Excel files with 1 spreadsheet and a maximum of 20 columns. If the user chooses to ignore empty rows, and there are empty rows present in the Excel file, the correspondence between the rows of the Excel file and the database will be affected as it will ommit inserting empty rows. The code has the potential to convert Excel XML files with multiple spreadsheets into one unique database table or multiple tables (one for every spreadsheet for example). ============================== Preparing the Excel file: If the file has multiple spreadsheets besides the first one, but they are empty, they can be left there but if the sheets have data, it will be added to the database, with the same columns correspondence as in the first sheet. However, the insertion of empty rows might not be done correctly. Data will be unaffected anayway. For optimal results, make sure the Excel file has only 1 spread sheet with data. ============================== Running the code: -From windows command prompt, execute the .py file by writing "python filename.py" ============================== Requirements: -Python 3.7.1 or superior installed ============================== Output: -Cleaned XML file (marked '-clean'), without schemas specifications -sqlite relational database file (.sqlite) with 1 table of the number of columns specified by the user ============================== Files uploaded: -python script: excel2sql.py -Instructions on how to save xls or xlsx Excel file as XML spreadsheet -Output files (as example): -1 Output example: Excel spreadsheet of 20 columns (no empty rows) -2 Output examples: one ignoring empty rows and another inserting empty rows -screenshots of sqlite database -screenshots of windows command prompt -screenshots of excel xls file -screenshots of xml file (before and after cleaning)
nathan-mosesuk/excel2sql
Excel to Database Converter__________________________________________________ A python script that converts an Excel XML spreadsheet into an sqlite database copying the rows and columns into the database just the way they are in the Excel file. The code automatically cleans the XML file in order for it to be readable by the python library.
Python