Tag Archives: Excel

Python and SQLite3

18 Dec

I need to generate a report every month. This report needs to have 30 names on it and I need to make sure than none of the people have appeared in previous months. I used Python and SQLite to store my items and to verify them. Here is what I did:

I wrote a script to empty all the data from an Excel sheet in to a SQLite Database Table. Then I wrote my program. The if __name__ == “__main__”: takes my command line arguments (load or scan and the data file.xls) and calls the appropriate function. If there is an error, it prints out the correct usage.

if __name__ == “__main__”:

if (len(sys.argv) < 3):

print “USAGE: warrant.py load/scan excelfile.xls”

elif sys.argv[1]==”load”:

load(sys.argv[2])

elif sys.argv[1]==”scan”:

scan(sys.argv[2])

else: print “Error…..Unknown Argument. \n\nUSAGE: warrant.py load/scan excel.xls”

The function scan() runs through an excel sheet and compares the names to the database. Currently, if the same last and first appear it prints out the info for the person. I will fix this by using ss# as another filter.

The load() function takes an excel file and uploads them to the database. No error checking. It will crash if the name already exists, but the list should have been cleaned with scan() first. I need to fix this anyway.

I hate error checking. My programs are usually for me so I don’t care if they fail because I can read the error and track it down. But now that I need anyone to be able to use it,  I cant have this – when the excel file doesn’t exist:

Error when Python can't find the Excel file.

Error when Python can’t find the Excel file.

Instead, I need to catch the errors and fail gracefully – like when the commands entered are wrong:

Catching an error in usage - gracefully.

Catching an error in usage – gracefully.

Fixed the error if the file is not found by using from os.path import exists then updating the if name=main to include:

elif sys.argv[1]==”load”:
           if exists(sys.argv[2]):
                  load(sys.argv[2])
           else:
                  print “file not found”

So here is my code thus far:

import sqlite3 as lite
import sys
from os.path import exists
from xlrd import open_workbook

def load(data):
    wb=open_workbook(data)
    sheet=wb.sheet_by_index(0)
    con = lite.connect(‘warrant.db’)
    with con:
        cur = con.cursor()
        for rownum in range(sheet.nrows):
            fname=sheet.cell(rownum,0).value
            lname=sheet.cell(rownum,1).value
            dob=sheet.cell(rownum,2).value
            ss=sheet.cell(rownum,3).value
            cur.execute(“INSERT INTO previous VALUES(fname,lname,dob,ss)”)
            print “Added: “+fname+”, “+lname+”, “+dob+”, “+ss

def scan(data):
    wb=open_workbook(data)
    sheet=wb.sheet_by_index(0)
    con = lite.connect(‘warrant.db’)
    with con:
        cur = con.cursor()
        for rownum in range(sheet.nrows):
            lastname=sheet.cell(rownum,1).value
            firstname=sheet.cell(rownum,0).value
            query=”SELECT * FROM previous WHERE lname='”+lastname+”‘ AND fname='”+firstname+”‘”
            cur.execute(query)    
            while True:
                row=cur.fetchone()
                if row==None:
                    break
                print row[0], row[1], row[2], row[3]

if __name__ == “__main__”:
    if (len(sys.argv) < 3):
        print “\n*************************\n\n”+”USAGE: warrant.py load/scan excelfile.xls\n\n”+”*************************”
    elif sys.argv[1]==”load”:
        if exists(sys.argv[2]):        
            load(sys.argv[2])
        else:
            print “\n***********************\nFile: “+ sys.argv[2] +” doesn’t exist.\n***********************\n”
    elif sys.argv[1]==”scan”:
        if exists(sys.argv[2]):
            scan(sys.argv[2])
        else:
            print “\n***********************\nFile: “+ sys.argv[2] +” doesn’t exist.\n***********************\n”
    else:
        print “\n*************************\n\n”+”Error…..Unknown Argument. \n\nUSAGE: warrant.py load/scan excel.xls\n\n”+”*************************”

 

Python, Point Shapefile and Qgis: Part II

24 Oct

In my first post, I showed how to create a shapefile from an excel sheet – but it was hard coded by cell. Not very useful.  Here is the code to loop through the sheet and create shapefile, then send a text telling me the job finished. The last thing to do is render the shapefile – Mapnik would work for this.
Here is the code:

import shapefile
import xlrd
import smtplib
book = xlrd.open_workbook(“NAME OF XLS FILE.XLS”)
sh = book.sheet_by_index(0)
w = shapefile.Writer(shapefile.POINT)
w.field(‘Type’)
w.field(‘Address’,’C’,’40’)

def readRow():

for rownum in range(sh.nrows):
rows=sh.row_values(rownum)
w.point(sh.cell_value(rowx=rownum, colx=0), sh.cell_value(rowx=rownum, colx=1))
w.record(sh.cell_value(rowx=rownum, colx=2),sh.cell_value(rowx=rownum, colx=3))
print ‘Reading Record: ‘+ str(rownum)

else:
print ‘Complete…’

readRow()

w.save(‘shapenull’)
message = ‘Job Complete’
server = smtplib.SMTP( ‘smtp.gmail.com’, 587 )
server.starttls()
server.login( ‘YOUREMAIL@gmail.com’, YOUR PASSWD’ )
server.sendmail( ‘YOUR PHONE #@mms.att.net’, ‘TO PHONE #@mms.att.net’, message )

The code opens the excel file formatted as: Lat, Long, Type, Address. Creates a point shapfile with fields for type and address.  Create a function that loops through the excel sheet. The Long, Lat, Type and Address are always in the same column so we only need to replace the row number of our cell and we do that by using the rownum variable in the loop.

While running, it will print out the current rownumber then print Complete.. when done. Lastly, It will send a text message saying that it has finished.

Simple.  Less than 25 lines of code. Set this to run as a task in Windows or as a Cron Job.

Python, Point Shapefile and Qgis

23 Oct

I have been getting data in a CSV and bringing it to Qgis using ‘Import Delimited Text.’  I could probably write a Python script in Qgis to do this automatically, but I only recently started reading the Qgis Python Cookbook. I have some experience with a Python Library for shapefiles already, so I will show how to create a point shapefile using this library.

Download Shapefile.py. All my maps are in WGS84, making this very easy. Open Notepad or your IDE and write the following:

import shapefile
w = shapefile.Writer(shapefile.POINT)
w.point(-106.505325, 35.069018)
w.point(-106.509378, 35.067741)
w.point(-106.527414, 35.108816)
w.field(‘type’)
w.field(‘street’)
w.record(‘res burg’,’central’)
w.record(‘auto burg’,’menaul’)
w.record(‘auto theft’,’eubank’)
w.save(‘point’)

I have three points in Albuquerque, with two fields: type and street. Running this file will create a shapfile without a PRJ. That’s alright, open the shapefile in Qgis. You should be asked for the projection. Choose WGS 84. It will display correctly. Now the points are on the map.

Right click on the shapefile in the table of contents and save as… Make sure the CRS is set to WGS 84.

Now when you open the shapefile you will not need to specify a projection.

Simple, right? Well, I hate hard coding the coordinates. I want to read from a file and everyone seems to send me data in Excel files so I downloaded XLRD and XLWT to read and write Excel files.

Here is how to add two points from an Excel Spreadsheet – you will want to run this as a loop to load all the data instead of hard coding it again. I will leave that up to you. (UPDATE: I can’t leave you hanging. Here is a new post with the code for looping). Here is the code to read the Excel file and write the shapefile.

book = xlrd.open_workbook(“test.xls”)
sh = book.sheet_by_index(0)
w = shapefile.Writer(shapefile.POINT)
w.point(sh.cell_value(rowx=0, colx=0), sh.cell_value(rowx=0, colx=1))
w.point(sh.cell_value(rowx=1, colx=0), sh.cell_value(rowx=1, colx=1))
w.field(‘type’)
w.field(‘street”)
w.record(sh.cell_value(rowx=0, colx=2),sh.cell_value(rowx=0, colx=3))
w.record(sh.cell_value(rowx=1, colx=2),sh.cell_value(rowx=1, colx=3))
w.save(‘fromExcel’)

Is this any easier than importing a CSV and creating the shapefile? No. But what it lets me do is schedule the Python file to run at intervals and update the shapefile even if I am at home sleeping. When graveyard shift comes in, they can see the latest data without me having to create it. It also allows me to add other functionality and python scripts.

I can filter the CSV by type or by location for example. Better yet, when a point in the shapefile exceeds some value, I can send a text to my phone – like this:

import shapefile
import xlrd
import smtplib
book = xlrd.open_workbook(“realtime.xls”)
sh = book.sheet_by_index(0)
w.record(sh.cell_value(rowx=1, colx=2))
w.save(‘writeANDreadANDshapefile’)

x = sh.cell_value(rowx=1, colx=2)
if x > 100:
message = “At Point: “+ str(sh.cell_value(rowx=1, colx=0)) +”,” + str(sh.cell_value(rowx=1, colx=1)) +”Levels equal: “+str(sh.cell_value(rowx=1, colx=2))
server = smtplib.SMTP( “smtp.gmail.com”, 587 )
server.starttls()
server.login( ‘YourAccount@gmail.com’, ‘Your Password’ )
server.sendmail( ‘From Phone Number@mms.att.net’, ‘ToPhoneNumber@mms.att.net’, message )

else:
print ‘Levels are normal’

All the concatenation of Message =”….” will create a string that says: “At Point (-106.4567, 35.3445) Levels Equal: 125” but will fill in the numbers using the actual coordinates and value.

There are programs – like CrimeView – that do this sort of thing automatically. But the way I showed was free and can be modified as you wish. Trying modifying the code of a proprietary software solution….Good Luck and Happy Hacking.

Crystal Reports for Revit Schedules

1 Oct

I have been playing with Crystal Reports and think they are an excellent option for Revit schedules.  With Cystal Reports you can design and write a single report to work on EVERY Revit project. The report handles all the data collection and adjusts itself to fit the data.

I have a fake room schedule in Excel (I exported the schedule and imported to Excel). Opening Crystal Reports, I select a new connection and choose Access. Under Access is the option for Excel 8.

When I select the Excel file, I can choose the sheets. Choose sheet 1.

Now I have a designer screen in Crystal Reports.

In the design view, I added my face to the Report Header and you can see some fields like [Report Title], [Author] and [Page Number]. these are Special Fields that will pull from the report properties. The text in the page header is hard coded and will appear on every page, as will the footer. The magic happens in the Details Section. I will drag fields from my Excel sheet to this section and it will populate the data for me.

You can see on the left, I have all the fields that are in the Excel sheet. I can drag them to the Details and the Header will automatically get the title. There is only one line in this report, but it will duplicate the data values for each record in the schedule.

Crystal reports is very powerful. You can group and sort, filter, and add subtotals, running totals, and grand totals. You can also add formulas that operate on fields.

Here is the finished report.

Here is the PDF: REPORT.PDF  

You can export to many different formats. By saving out any schedules from different projects to the same Excel file, you can run the report on new data just by opening it. (Overwrite WallSchedule.XLS with every new Revit wall schedule. ) The data connection remains and the fields all have the same names.
This post was a bit rushed, but I hope the point is clear: Crystal Reports can create Revit reports that are far more attractive and powerful than Revit can — at the moment. My report is not beautiful, but Google some samples and you will see their power.

This report shows a little more: Better Report.PDF

 

Aoristic Analysis Example

1 Oct

I have started analyzing crime. The data I get has the time of the crime being the time the report was filed. So, on Monday at 8:00 there are a lot of car burglaries — well, reports of car burglary. When the crime is reported and when it happened are two different things and need to be kept separate. But how does someone who is sleeping know when their car was broken in to?

Digging deeper in to the reports, I can get an estimated start and end time that a crime may have occurred between.  Using Aoristic analysis, I can assign weights to the times to come up with a better understanding of when the highest probability times for certain crimes may be. Here is how it works:

In Excel create a sheet with columns for start and end and then one for each hour of the day 00:00 to 23:00.

For each incident note the start and end time, then count the time between them and mark the value of each hour divided by the total hours.

Start     End          …    1:00   2:00    3:00    4:00    5:00   6:00    …..

2:00    6:00                              .25      .25        .25        .25

3:00    4:00                                            1

1:30     6:30                 .166    .166     .166    .166     .166    .166

SUM                               .166     .416    1.416    .416     .416    .166

The probability is the SUM/Number of incidents (in this case 3)

Each time (1:00) is really 1:00-1:59. So if I crime occurred between 1:00 and 6:00- 1,2,3,4 and 5 are selected, 6 is not.

The highest probability for this type of crime to occur would be at 3:00.  The more data you have the better your results. And use common sense when looking at the results. In my studies, the results have made sense. I have noticed Auto Burglary in the late night and early morning. Home burglary has occurred while people were at work from 9:00 to 4:00.  This makes sense and is shown with the data.

Military Time in Excel

1 Oct

I was working with military time in Excel and needed to calculate the hours:minutes:seconds between two times. The answer seemed simple – subtract the end time from the start time (1:30 to 2:00 is 2:00-1:30= 00:30.). This worked except when the start time was at around 23:45 and the end time was the next day at 00:34.  Here is my solution:

Column B (Start Time)                                  Column C (End Time)

23:51                                                                                00:01

=if( C1>B1, C1-B1, (24-B1)+C1) will give you 00:10

What the formula does is determine if it can just subtract the end from the start. If the start time is greater than the end time (the time ends the next day), the formula subtracts the end time from 24 to get the remaining time left in the day then adds the time from the next day. So in the example 24-23:51 = 9 then add 9 + 00:01 = 00:10.

Not exactly rocket science, but it works. A lot of the answers I found online suggest converting to other formats then back to military time and blah blah blah. Too much work.  I need a simple solution and found one. So far it has worked.

RevitDB: Edit your model in Excel

22 Mar

In an earlier post, I showed how to edit a Revit model from MS Access. In this post, I will show how to edit your model using Excel.

Here is the model.

The model is exported to an Access database using the RevitDB Link plugin. For more information on how this is done, see my earlier post. Once the database is created, import an Excel sheet and link it to an external sheet. In this example, I have a link called ‘WallsFromExcel’ to an external sheet called ‘Walls-Excel.’

When the ‘Walls-Excel’ spreadsheet is modified, it is automatically updated in the database because of the direct link.

Add a Query to the database that updates the Walls table from Revit with the spreadsheet. Run the query….

……and update the Revit model with DB Link.