citibike-data-to-sql.py

import csv
import time
import datetime as dt
import re

files = ["data/2013-7.csv","data/2013-8.csv","data/2013-9.csv","data/2013-10.csv","data/2013-11.csv", "data/2013-12.csv","data/2014-1.csv", "data/2014-2.csv"]

def convert_to_timestamp(time_string):
	month = re.search("\d+",time_string).group(0)
	if len(month) == 1: month = "0" + str(month)
	day = re.search("(?<=\/)\d+",time_string).group(0)
	if len(day) == 1: day = "0" + str(day)
	year = re.search("(?<=\d\/)\d{2}",time_string).group(0)
	time_portion = re.search("(?<=\s).*",time_string).group(0)
	format = "%m/%d/%y %H:%M"
	time_string = "%s/%s/%s %s" % (str(month),str(day),str(year),str(time_portion))
	return time.mktime(dt.datetime.strptime(time_string,format).timetuple())

new_file = open("database-import.sql","w+")
#new_file.write("from_id,from_lat,from_lon,to_id,to_lat,to_lon,cust_type\n")

for file in files:
	with open(file,"rU") as data:
		reader = csv.reader(data,delimiter=",")
		counter = 0
		for row in reader:
			try:
				#row[1] = str(convert_to_timestamp(row[1]))[:-2]
				#row[2] = str(convert_to_timestamp(row[2]))[:-2]
				row.pop(4)
				row.pop(7)
				
					
				if row[11] == "\N": row[11] = str(0)
				map(str,row)
				format = "INSERT INTO data(from_id,from_lat,from_lon,to_id,to_lat,to_lon,cust_type) VALUES(%s,%s,%s,%s,%s,%s,\"%s\");\n"
				if counter != 0: new_file.write(format % (row[3],row[4],row[5],row[6],row[7],row[8],row[10]))
				counter = counter + 1
			except Exception:
				pass
	print file
new_file.close()