Member-only story
Python: Using SQLite when Excel’s Limits are Surpassed
View the data and create Excel extracts with SQL
I’m not a python developer in the sense that I code in other languages at work most of the time. I do use python however for automation and to manipulate spreadsheets with pandas. I really enjoy Python and it is fun coding in this language. At home, I’ll sometimes write games or automate something.
The other day I was validating a file and I could not open it with any of my text editors. I tried to read it in Python to obtain the record count, but I got a ‘Memory Error.” It turns out it was a humongous XML file and I finally was able to view it in a browser and verified it was the correct file and that was that.
At home as a personal project, I downloaded a freely available public Excel file with 2 million records and decided to explore viewing the data by exporting it using Python into an SQLite database which is a lightweight database included with Python. When I opened the CSV file, Excel gave me a pop-up that stated that “File Not loaded Completely.” It loaded a little over a million of the 2 million records. So I loaded it with Python with the following code.
By the way, if you don’t have Pandas installed you will need to do a pip install of it in your environment.
