from __future__ import print_function
import os
import re

#http://stackoverflow.com/questions/3964681/find-all-files-in-directory-with-extension-txt-with-python
create_table_file = open('create_raw_data_table.sql', 'r')
output_file = open('import_data.sql', 'w')
print('drop table districts;', file=output_file)
print('drop table states;', file=output_file)
for line in create_table_file:
    print(line, file=output_file)
print('.mode csv', file=output_file)
print('create table if not exists districts (name TEXT, state TEXT, classification TEXT, household_total INTEGER, population_total INTEGER);', file=output_file)
print('create table if not exists states (name TEXT, state TEXT, household_poor INTEGER, household_middle INTEGER, household_rich INTEGER);', file=output_file)

def import_from_file(filename):
    print('.import "data/'+filename+'" raw_data', file=output_file)

#Use standardized names for states
def clean_state(state):
    state = re.sub(r'\([A-Z&]*\)', '', state)
    state = re.sub(r'[()\d]', '', state)
    state = re.sub('Nct of Delhi', 'Delhi', state)
    state = re.sub('JAMMU', 'Jammu', state)
    state = re.sub('Utter', 'Uttar Pradesh', state)
    state = re.sub('&', 'and', state)
    state = state.strip()
    return state

for filename in os.listdir("data/"):
    if filename.endswith('.CSV'):
        import_from_file(filename)
        state_name = re.sub(r'.CSV', '', filename)
        print('insert or replace into districts select trim(Name), "'+clean_state(state_name)+'", TRU, "No of households", "Total Population Person" from raw_data where Level=\'DISTRICT\';', file=output_file)
        print('drop table raw_data;', file=output_file)
    if filename.endswith('household_income.csv'):
        

@@ -159,1105 +159,1030 @@
 hon%0A
-create_table_file = open('create_raw_data_table.sql', 'r')%0Aoutput_file = open('import_data.sql', 'w')%0Aprint('drop table districts;', file=output_file)%0Aprint('drop table states;', file=output_file)%0Afor line in create_table_file:%0A    print(line, file=output_file)%0Aprint('.mode csv', file=output_file)%0Aprint('create table if not exists districts (name TEXT, state TEXT, classification TEXT, household_total INTEGER, population_total INTEGER);', file=output_file)%0Aprint('create table if not exists states (name TEXT, state TEXT, household_poor INTEGER, household_middle INTEGER, household_rich INTEGER);', file=output_file)%0A%0Adef import_from_file(filename):%0A    print('.import %22data/'+filename+'%22 raw_data', file=output_file)%0A%0A#Use standardized names for states%0Adef clean_state(state):%0A    state = re.sub(r'%5C(%5BA-Z&%5D*%5C)', '', state)%0A    state = re.sub(r'%5B()%5Cd%5D', '', state)%0A    state = re.sub('Nct of Delhi', 'Delhi', state)%0A    state = re.sub('JAMMU', 'Jammu', state)%0A    state = re.sub('Utter', 'Uttar Pradesh', state)%0A    state = re.sub('&', 'and', state)%0A    state = state.strip()%0A    return state%0A%0A
+%0Adef import_from_file(filename):%0A    print('.import %22data/'+filename+'%22 raw_data', file=output_file)%0A%0A#Use standardized names for states%0Adef clean_state(state):%0A    state = re.sub(r'%5C(%5BA-Z&%5D*%5C)', '', state)%0A    state = re.sub(r'%5B()%5Cd%5D', '', state)%0A    state = re.sub('Nct of Delhi', 'Delhi', state)%0A    state = re.sub('JAMMU', 'Jammu', state)%0A    state = re.sub('Utter', 'Uttar Pradesh', state)%0A    state = re.sub('&', 'and', state)%0A    state = state.strip()%0A    return state%0A%0Adef create_raw_data_table(filename):%0A    input_file = open(filename)%0A    for line in input_file:%0A        print(line, file=output_file)%0A%0Adef import_districts():%0A    create_raw_data_table('sql/create_raw_data_table_districts.sql', 'r')%0A    output_file = open('import_data.sql', 'w')%0A    print('drop table districts;', file=output_file)%0A    print('.mode csv', file=output_file)%0A    print('create table if not exists districts (name TEXT, state TEXT, classification TEXT, household_total INTEGER, population_total INTEGER);', file=output_file)%0A%0A    
 for 
@@ -1214,16 +1214,20 @@
 ata/%22):%0A
+    
     if f
@@ -1248,24 +1248,28 @@
 th('.CSV'):%0A
+    
         impo
@@ -1295,24 +1295,28 @@
 me)%0A        
+    
 state_name =
@@ -1338,32 +1338,36 @@
 , '', filename)%0A
+    
         print('i
@@ -1567,60 +1567,8 @@
     
-    print('drop table raw_data;', file=output_file)%0A
     
@@ -1617,13 +1617,4 @@
 '):%0A
-        %0A

Clean up import_data.py