Get Data from XML and CSV to SQL

Get data from XML and CSV files to SQL using Python.


I started to help manage a university-wide grant. Although the grant has a long history, nothing has been done to keep a record of the applicants’ information. It is my intent to build a simple database that will allow us to track such information. The first step is to collect basic information about faculty: last name, first name, department, phone number, email address, and the semester that they applied for the grant. I know, I know, the grant information should be in a different table. Anyway, the data I can retrieve from the last two semesters are in different formats: one is in XML format and the other is in CSV format. My goal is to collect faculty’s information into a single table in SQLite3, which is part of the Python standard library and does not require you to install other specific modules to work with SQLite. My code runs in Python 3.5.

I first worked with the XML file that is named ‘Spring16Contact.xml’. Below is the screen capture of the code:


I then worked with the CSV file. Since the table has already been created, I only need to get data from the CSV file and then add them to the existing table. Note: I deleted the header row in the CSV file so the header information will not be inserted in the table. Below is the screen capture of the code:


The end result in SQLite is shown in the DB Browser for SQLite. I masked faculty’s personal information, but you can see the records from the XML and CSV files have been put into one table.