Advanced Data Processing with SQLite

I have been working as a System Administrator for the last two months. Part of my new responsibilities at this new company include inventory management. I manage the onsite data room, a couple hundred wall-mounted video kiosks, over two hundred analog phones, and almost one thousand android tablets. I am constantly processing reports for these devices and which can be very time consuming.

Most of the software systems that I interact with at my new job are poorly written proprietary applications that have not been updated in at least a decade, if not longer. I am fortunate that I can export some of the data into a spreadsheet for some of these applications. It takes a bit of work since whoever created these applications decided it would be a good idea to try to format the spreadsheets by adding empty columns and rows and adjusting the cells. LibreOffice Calc allows me to export these spreadsheets to csv which can then be imported into an sqlite database. This allows me to write python scripts which can parse the data and automate repetitive tasks. I can also run simple SQL queries on data from different spreadsheets by importing them as separate tables.

Find the user’s location and his tablet’s MAC address

sqlite> .open data.db
sqlite> .headers on
sqlite> .mode csv
sqlite> .import users.csv users
sqlite> .import devices.csv devices
sqlite> .output data.csv
sqlite> select * from users inner join devices on = devices.user_id;
sqlite> .quit


This post is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License