Suppose you wish to input all the data from a series of CSV files residing in a particular folder to a SQLite database, the following tools and and commands will make it a relatively fast and painless.
Each of the CSV file are assumed to be in the format where the first line is the headers and the subsequent lines are series of data. The headers need not be the same for each CSV file.
The following python modules are required.
- CSV module
- Pandas (0.15)
- SQLite3
The following lines of commands will input execute the following actions.
- Use list comprehension to get the list of file path from a particular folder.
- Convert the data from each CSV file into dict format using CSV DictReader and iterate over all the files, joining them to a list.
- Convert the list of dicts to a single Pandas Dataframe.
- Create a connection to a particular SQLite Database.
- Use Pandas to_sql() function to pass all the data to SQLite database.
- The code is displayed below.
<pre>import os
import csv
import pandas
import sqlite3 as lite
path = r'C:\folderpath\to\list\of\files'
## Get the full path of all the csv files.
full_path_list = [os.path.join(path,f) for\
f in os.listdir(path) if os.path.isfile(os.path.join(path,f)) ]
## Convert all data to list of dicts.
full_data_list = [n for f in full_path_list for n in csv.DictReader(open(f,'r'))]
## SQL database name and initialize the sql connection.
db_filename = r'c:\data\sbrtemp3.db'
con = lite.connect(db_filename)
## Convert to dataframe and write to sql database.
pandas.DataFrame(full_data_list).to_sql('test', con, flavor='sqlite',
schema=None, if_exists='replace', index=True,
index_label=None, chunksize=None, dtype=None)
## Close the SQL connection
con.close()
The Pandas to_sql() function is able to handle issues of duplicates and can be called multiple times if users required to add additional data. In addition, converting to a dataframe from a list of dicts also allows the headers to be different for different CSV files. Besides saving to database, user can also choose to consolidate to a single csv file by using Pandas to_csv() function.
Simply Python 