Ryan Robitaille

Facts, Stories, Conveyance: Postmortem “Data Viz Advice” from a famous American street photographer

Ry — Mon, 22 Apr 2013 19:47:28 +0000

I just got back from spending the last week in the beautiful San Francisco Bay area. If you've never been, seriously, go there. As an obnoxious East Coast / New England guy, I thought I had seen it all. Nope.

Anyways, one of the things we did was visit the SF Museum of Modern Art - the building is a wonder in itself, but obviously the museum hosts countless photographs and art installations from many famous (and not so famous) artists. Honestly, I had no expectations. I was on vacation after all - just doing a fun "touristy" activity between rounds of Grey Goose Oysters shooters, eating sea creatures and pints of AnchorSteam.

I had no idea I'd find inspiration that can be DIRECTLY applied to modern data visualization practices. Advice that doesn't get followed as much as it should...

The exhibit - a complete retrospective of the working life and career of Garry Winogrand (1920s - 1984), including many photos that were never published (he left behind 6.5k rolls of undeveloped film when he passed at the age of 56).

Garry was a famous street-documentary photographer mostly known for his NYC shots in the 50s-early 80s. His many striking photos told a story through the faces and bodies of it's living subjects. Short on hard "data" - but miles long on human interest, wonder, and conveyance. Stories of frozen emotions and reactions, but without a full sense of context (aka interpretative art; mystery).

I came upon a particular photo in the "Down in the Bronx" section of the exhibit that caught my eye. It was an assault scene from the 50s. Murder possibly. There was a well dressed man on the ground, you can see only his well-made shoes and an upturned hat on the ground - near a large pool of blood. You can see people rushing around him, a nun leaning in and praying, and a little boy stading in the background looking on with eyes agape. Clearly "something" had happened just moments earlier.

On the display card that accompanied the photo is where I found my connection. It described how Garry always gravitated toward this type of photo journalism instead of blatantly trying to capture the What or Why for the sake of written narration. This quote from him was below it.

"There is nothing more mysterious
as a fact clearly described"

- Garry Winogrand

Boom. I immediately tapped this quote out on my phone.

It seems that in all the academic and ideological viz wars we've seen lately - people sometimes forget the importance of storytelling and conveyance. Showing the results without the story is next to useless.

Our industry is about action and communication, not mystery.

The facts mean nothing if the reader can't understand it, conceptualize it, or (even worse) be intrigued enough to even give a shit about it.

The reader might be a literal "reader" of a magazine, newspaper, or online periodical - but really in our case the "reader" is YOUR FUCKING BOSS and YOUR CUSTOMERS. It's essential that they not only know the facts, but the breadcrumbs that led to such facts. Seeing is understanding. You can't make things actionable if they can't be understood (even more important is self-explanatory interactive visualizations).

In any useful data viz - calculations & insightful analysis is good, facts are good, in-depth data is good, but...

CONTEXT IS GOD.

It's not only about WHAT you are showing, but about the why, how, and the elusive 'what do we do next'. Data is good, geeky math is good, but if it can't be framed in a way that biz or end-user peeps (or the public) can understand it's just shred fodder that you can use in little Suzi's hamster cage.

Getting and interpreting the data is one part - CONVEYING it is another altogether.
(again - not to get into a Pure Math / Science vs Functional "Art" war - but it bodes discussion)

What do you guys think?

The post Facts, Stories, Conveyance: Postmortem “Data Viz Advice” from a famous American street photographer appeared first on Ryan Robitaille.

Going Yard: Using MLB data and Tableau 8 to “re-imagine” Homeruns in PetCo Park – “The Business Case”

Ry — Tue, 02 Apr 2013 01:10:00 +0000

Greetings, it's been awhile since I published anything - my apologies! I've been hella busy this year, but finally have dedicated some time to a mini-project I've been working on for some time. This time, it's a baseball viz / park issue that I can hopefully shed some (theoretical) light on. IN an effort to stop writing massive blog posts - consider this post a intro - more like the "Business Case", the next one will be "Technical HOW, WHY, WHAT".

Anyways, on with the premise...

THE QUESTION:

The San Diego Padres are changing the wall dimensions for their home stadium, PetCo Park, for the 2013 MLB season. In a notoriously large "pitchers park", how might this change affect the amount of Homeruns, the Padres record, and (possibly) ticket sales?

MY APPROACH:

Using freely available data from the MLB Gameday app (via their awesome XML datastore) can we map out the hitcharts for the past 3-5 years (by mapping Gameday x,y coordinates to geocoded coordinates) and see what effect a closer (and lower) wall would have had on THOSE seasons. Changing Outs and Base Hits into HRs (with associated RBIs, if men are on base) If we can try to understand with MIGHT have happened, we can shed some light on what this season might bring.

Tools Used:

Python = Data Harvesting (MLB, Weather, Salaries)
SQL = Data Storage (SQL Server 2012 Azure cloud DB)
Tableau = Data Presentation / Visualization / Analysis
Photoshop = Designing Layout (mock-up wireframes) + 'Design' Sections of the Viz
Total Time Taken = Lots. :)

THE RESULTS:

More Homeruns? Yes, as many as 50-100 MORE per year.
More Padres Wins? No (the visiting team will probably gain MORE HRs).
More Ticket Sales? Possibly (HRs make for a more 'exciting' game to watch, and may be a bigger draw for fans than wins - but the jury is out on that).

Also, notice that attendance seems to be declining regardless of wins AND plummeting average ticket prices...

Take this with a grain of salt, Baseball is a game that can change on a dime. I'm only making estimations based on that data available.

But don't take my word for it - check out the viz in all it's glory by clicking on the image below.

"Taming PetCo Park". Click for full interactive version.

Gird your loins. Next time, I'll have all the gory details - Python, SQL scripts and all....

The post Going Yard: Using MLB data and Tableau 8 to “re-imagine” Homeruns in PetCo Park – “The Business Case” appeared first on Ryan Robitaille.

The Anatomy of a Tableau 8 Dashboard (or a “floating tile grid autopsy”)

Ry — Fri, 28 Dec 2012 00:58:21 +0000

With the release of Tableau 8 just around the corner, and the Tableau 8 Public servers in a (very) limited release beta - I figured that it was good a time as any to see what I could come up with using (a couple) of the new features. Specifically the "floating tile" option for dashboard creation (one that I'm particularly jazzed about, as you'll see)...

"Floating Tiles" = A Game Changer

Previously in 7 (and earlier) you were restricted to putting dashboard "parts" (for lack of a better term - think worksheets, text, and images) into a editable "grid" pattern. While this allowed some cool designs if you wanted to get creative - you could really only go so far. Now since that has been lifted (with the added inclusion of allowing overlapping dashboard parts) we are going to see some really cool designs.

I wanted to visually illustrate how the new dashboard layout rules radically change the dash construction and design in Tableau 8 - so I started up Photoshop to give this particular viz a "grid autopsy". Anyone who is familiar with Tableau in any way should be able to see quickly what we've got going on here.

This dashboard viz is literally made up of 21 separate Tableau worksheets, plus several static images and static text boxes.

Blue - Tableau Worksheet
Red - Static Image
Green - Static Text

Also, the ability to move tiles pixel by pixel and size them pixel makes it possible without contemplating murder.

For the record, SOME of this would have been somewhat possible with 7, but it would have been a total trauma to keep things aligned properly - and generally not recommended for anyone who wants continued grip on their sanity.

NOTE: Yes, I know that the "trending" green up triangle should be a worksheet with a shape calculation - but I got really lazy

UPDATE: I'm also quite fond of the "date range" text worksheet. It's not groundbreaking or anything, but having a calculated field dynamically show some absolute filter ranges means that when filtering you can have text updated to "re-represent" what the user is looking at WITHOUT counting on the filter dialogs themselves...

Ex.

To be continued... :) Really looking forward to what everyone else comes up with too!

The post The Anatomy of a Tableau 8 Dashboard (or a “floating tile grid autopsy”) appeared first on Ryan Robitaille.

Quick & Dirty Address Geocoding and Formatting with Google Maps API

Ry — Tue, 11 Dec 2012 00:19:32 +0000

Howdy. Cheers to all ya'll down there in internet land. I got in a conversation earlier today regarding geocoding addresses in data-sets - it's a pretty common thing, and I've done it numerous times for a WIDE variety of data-points (Bigfoot, Sex Offenders, Concert Venues, etc.), so I figured hell, I'll clean it up and offer it to the Google gods. Maybe someone will find it useful.

"Why aren't we flying? Because getting there is half the fun. You know that."

The script is pretty simple, but it usually gets the job done with minimal modification. I tend to use a small dimension table for geo lat / long data - because generally the coordinates of an address won't change, so there is no reason to RE-Geocode an address (in most cases).

In this example, I'm using a SQL Server connection - but the query usage are so basic that any other could be used just by loading a different module and changing the connection syntax a bit (SQLite, Oracle, MYSQL, etc).

You'll need to download and install this simple Google Maps API wrapper first.

from geocode.google import GoogleGeocoderClient
import pymssql # or MySQLdb or etc.

# sql server example
db = pymssql.connect(host='localhost', user='sa', password='trolololo')
cursor = db.cursor()

geocoder = GoogleGeocoderClient(False)

# your source SQL in this example I'm expect at least 2 fields, the first a unique id for that address (for updating), the next a string of the address
cursor.execute("select 12341 as myid, '101 s presidents st baltimore MD USA' as address ") # the hotel I wrote this in
addy_queue = cursor.fetchall()

for curraddy in addy_queue:

addy_id = str(curraddy[0]) # your unique id
addy = str(curraddy[1]) # your location

print '****************************ADDY IN*********************************'
print ' (' + str(addy_id) + ') ' + addy

try:
result = geocoder.geocode(addy)
# result.is_success() # nice little boolean if you need it

googaddy = result.get_formatted_address().replace("'","")

coord = result.get_location()
latitude = coord[0]
longitude = coord[1]
location_type = result.get_location_type() # just for fun

print '****************************ADDY OUT*********************************'
print str(latitude) + ', ' + str(longitude) + ' (' + location_type + ')'
print googaddy # Google Maps formatted address
print ' '

# this is where you're updating the geo coords BACK to your data, or inserting them in a sep table
cursor.execute("delete from homes.dbo.my_geo where id = '" +str(addy_id)+"' ")
cursor.execute("insert into homes.dbo.my_geo (lat, long, id, clean_address) values ('"+str(latitude)+"', '"+str(longitude)+"', '" +str(addy_id)+"', '"+str(googaddy)+"' ) ")
db.commit()

except:
pass
# in case of error: do something (optional) I'm sure you're going to have some bad addys, just address them later, or flag them here

#time.sleep(.2) # just in case you dont want to hammer Google's servers, but I say - fuck 'em

FYI - Last I heard, the Google API limits geocoding to 2,500 requests within 24 hours, after that you'll just get error responses.

Feel free to give me a shout!

The post Quick & Dirty Address Geocoding and Formatting with Google Maps API appeared first on Ryan Robitaille.

Build Tableau Data Extracts out of CSV Files? More Python TDE API madness!

Ry — Fri, 07 Dec 2012 19:10:17 +0000

So here we have the 3rd in a series about using Tableau 8's 'Data Extract' API to automatically create TDE files from various data sources without using the desktop client. This time we're focusing on good ole' Comma Separated files.

Much like my SQL Server script, this one tries to guess field names and field data types in order to produce as good of an extract as possible - I've also added a little command line progress bar for your FILE WATCHING ENJOYMENT.

You know, in case you're into shit like that.

Plus, I report on file sizes and row counts! OMG! You ALSO have the ability to run MULTIPLE files in a directory - in case you have a bunch that need to be crunched into their own TDE files - that's right, Christmas come early! (You're welcome)

I used some census data files for testing (as well as Metallica, Bigfoot, and UFO data - but that's a given) as you can see below. I gotta, say processing CSV files is decent speedy - even though I haven't tried to process any HUGE files yet (2GB+).

Bit of explanation about the script parameters:

cvsfilenamemask = '.csv' It's basically an "ends with" string - so you can use something like '.csv' to process ALL CSV files in a directory, or simply change it to something like 'input.csv' to ONLY process that file from that dir.
sourcedir = 'C:\\Python27\\' Fairly obvious - this is the folder that the script will look in for any files matching 'csvfilenamemask' above. You can also use UNC file paths like ''\\\\ryrobesxps\d$\\'. Remember to double up your slashes accordingly!
targetdir = 'C:\\Python27\\' Same as above, except this it the target dir that the resulting TDE file will be written to.
csvdelimiter = ',' You should know this one.
csvquotechar = '"' AND this one, but who knows.
rowoutput = False Set to 'True' to have all the row and column info echoed into the command line window - it's good for debugging data and script errors but it slows things down like 10X (literally). Who knew that printing out thousands of things would be so expensive! ;)

Here's a sample output from some tests I did earlier

C:\Python27>python CSV_to_TDE.py

###########################################################################
Now working on cbp10co.csv (2,155,390) -> cbp10co.tde (59,871 rows per =)
###########################################################################
[ = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = ]
(2,155,390 rows)

###########################################################################
Now working on cbp10st.csv (456,410) -> cbp10st.tde (12,678 rows per =)
###########################################################################
[ = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = ]
(456,410 rows)

###########################################################################
Now working on PlayerStats.csv (23) -> PlayerStats.tde (10 rows per =)
###########################################################################
[ = = ]
(23 rows)

###########################################################################
Now working on simple.csv (32) -> simple.tde (10 rows per =)
###########################################################################
[ = = = ]
(31 rows)

cbp10co.csv 383 seconds 2,155,390 rows. TDE file is 172.9MB (source was 42.2MB)
cbp10st.csv 431 seconds 456,410 rows. TDE file is 114.6MB (source was 43.5MB)
PlayerStats.csv 4 seconds 23 rows. TDE file is 3.5KB (source was 57.4KB)
simple.csv 1 seconds 31 rows. TDE file is 659.0bytes (source was 34.4KB)

TOTAL RUN 820 seconds 2,611,854 rows - 287.5MB of text into 85.8MB of data sex!

C:\Python27>

Sexy, right? Indeed. Ok, here it is, it's a whopper, so get that Cntrl-C hand ready...

Wh3r3's th3 b33f? Oh, nevermind.

Note: if you have less than 20 rows or so, my "fancy" header checking could get confused. Why you would have a file like that, I have no idea - just sayin'.

# ryan robitialle (12/6/2012)
# creating Tableau Data Extracts via CSV files

import csv, os, time
from datetime import datetime
import dataextract as tde #saves some typing, cause i'm a lazy fucker

################ PARAMETERS FOR YOU, CODE MONKEY! ##########################
cvsfilenamemask = 's.csv' # can be explicit 'thisfile.csv' for one file - or open '.csv' for all that match
sourcedir = 'C:\\Python27\\' # need to double up the \\s | windows shares use like this '\\\\ryrobesxps\d$\' etc
targetdir = 'C:\\Python27\\' # can't be a share or UNC path
csvdelimiter = ',' # obvious!
csvquotechar = '"' # obvious!
rowoutput = False # useful for debugging data errors / slows shit down a lot however
################ PARAMETERS FOR YOU, CODE MONKEY! ##########################

# Note: if you have less than a few thousand rows, the progress bar will be a bit fucked looking.

fileperf = dict() # for saving each files execution times

# since the CSV module imports all fields as strings regardless of what they are..
def datatyper(n): # force some data types to figure shit out
try: # kind of lame.... BUT IT WORKS
x = int(n)
return int(n)
except:
try:
x = float(n)
return float(n)
except:
try:
date_object = datetime.strptime(n, '%m/%d/%Y')
return date_object
except:
try:
date_object = datetime.strptime(n, '%Y-%m-%d')
return date_object
except:
if n == 'NULL': # just in case, don't want any literal NULLs in there
return None
elif len(n) > 0:
return str(n)
else: # no need to return an empty string, let's NULL that shit out
return None
# end ugly data types function

def showhumanfilesize(num):
for x in ['bytes','KB','MB','GB']:
if num < 1024.0:
return "%3.1f%s" % (num, x)
num /= 1024.0
return "%3.1f%s" % (num, 'TB')

def intWithCommas(x):
if type(x) not in [type(0), type(0L)]:
raise TypeError("Parameter must be an integer.")
if x < 0:
return '-' + intWithCommas(-x)
result = ''
while x >= 1000:
x, r = divmod(x, 1000)
result = ",%03d%s" % (r, result)
return "%d%s" % (x, result)

def file_lines(fname):
with open(fname) as f:
for i, l in enumerate(f):
pass
return i + 1

#print ' '
#print '[ Note: Each . = ' +str(dotsevery)+ ' rows processed ]'

os.chdir(sourcedir)
for csvfilename in os.listdir("."):
if csvfilename.endswith(cvsfilenamemask):
tdefilename = csvfilename.split('.')[0]+'.tde'
linez = file_lines(sourcedir + csvfilename)
if linez > 36:
dotsevery = linez/36
else:
dotsevery = 10
print ' '
print '###########################################################################'
print ' Now working on ' + csvfilename + ' ('+str(intWithCommas(linez))+') -> ' + tdefilename + ' (' + str(intWithCommas(dotsevery)) + ' rows per =)'
print '###########################################################################'
#print dotsevery
time.sleep(5) # so you can read it.

# BEGIN MULTI FILE LOOP
start_time = time.time() # simple timing for test purposes

try:
# taking a sample of the file
csvfile = open(csvfilename, 'rb')
toplines = csvfile.readlines()
filebuffer = '' # empty string
for i in range(dotsevery):
filebuffer = filebuffer + toplines[i]
hasheader = csv.Sniffer().has_header(filebuffer) # csvfile.read() / filebuffer
except:
hasheader = False

# ok lets go
csvfile.seek(0) # YOU WILL DO, WHAT I SAY, WHEN I SAY! BACK TO THE FRONT!
csvreader = csv.DictReader(csvfile, delimiter=csvdelimiter, quotechar=csvquotechar)

dfields = []
dtypes = []

if hasheader == True:
for f in csvreader.fieldnames:
dfields.append(f)

else: # WTF? No header? JERK.
fieldnum = 0
#print 'If you don\'t have a header, how the fuck will you recognize the fields in Tableau?'
for f in csvreader.fieldnames:
dfields.append('field'+str(fieldnum))
fieldnum = fieldnum + 1
csvreader = csv.DictReader(csvfile, delimiter=csvdelimiter, quotechar=csvquotechar, fieldnames=dfields)
# we have to make our own field names

for row in csvreader:
for i in dfields:
dtypes.append(str(type(datatyper(row[i]))))
break # got shit, we're out

csvfile.seek(0) # BACK TO THE FRONT! (AGAIN!)

os.chdir(targetdir)
try: # Just in case the file exists already, we don't want to bomb out
tdefile = tde.Extract(tdefilename) # in CWD
except:
os.system('del ' + targetdir + tdefilename)
os.system('del DataExtract.log') #might as well erase this bitch too
tdefile = tde.Extract(targetdir + tdefilename)

# ok lets build the table definition in TDE with our list of names and types first
# replacing literals with TDE datatype integers, etc
tableDef = tde.TableDefinition() #create a new table def

numfields = len(dfields)
#print numfields

if rowoutput == True:
print '*** field names list ***' # debug
for t in range(numfields):
fieldtypeo = dtypes[t].replace(","").replace("'>","").replace(","").replace('NoneType','str').replace('uuid.UUID','str')
fieldname = dfields[t]
fieldtype = str(fieldtypeo).replace("str","15").replace("datetime.datetime","13").replace("int","7").replace("decimal.Decimal","10").replace("float","10").replace("uuid.UUID","15").replace("bool","11")
if rowoutput == True:
print fieldname + ' (looks like ' + fieldtypeo +', TDE datatype ' + fieldtype + ')' # debug
try:
tableDef.addColumn(fieldname, int(fieldtype)) # if we pass a non-int to fieldtype, it'll fail
except:
tableDef.addColumn(fieldname, 15) # if we get a weird type we don't recognize, just make it a string
if rowoutput == True:
print '***'
time.sleep(5) # wait 5 seconds so you can actually read shit!

# ok, lets print out the table def we just made, for shits and giggles
if rowoutput == True:
print '################## TDE table definition created ######################'
for c in range(0,tableDef.getColumnCount()):
print 'Column: ' + str(tableDef.getColumnName(c)) + ' Type: ' + str(tableDef.getColumnType(c))
time.sleep(5) # wait 5 seconds so you can actually read shit!

# ok lets add the new def as a table in the extract
tabletran = tdefile.addTable("Extract",tableDef)

# time to start pumping rows!

rowsinserted = 1

# if we have a header, we don't want to try and process it
if hasheader == True:
csvreader.next()
print '[',
for row in csvreader:

if rowoutput == True: # row deets, else just '.'
print '************** INSERTING ROW NUMBER: ' + str(rowsinserted) + '**************' # debug output
else: # only print dot every 50 records
if (rowsinserted%dotsevery) == 0:
print '=',

columnposition = 0
newrow = tde.Row(tableDef)

for t in range(numfields):
fieldtype = dtypes[t].replace(","").replace("'>","").replace(","").replace('NoneType','str').replace('uuid.UUID','str')
fieldname = dfields[t]

if rowoutput == True: # column deets
print str(columnposition) + ' ' + fieldname + ': ' + str(row[fieldname]) + ' (' + str(fieldtype).split('.')[0] + ')' # debug output

if fieldtype == 'str':
if row[fieldname] != None: # we don't want no None!
newrow.setCharString(columnposition, str(row[fieldname]))
else:
newrow.setNull(columnposition) # ok, put that None here

if fieldtype == 'int':
if row[fieldname] != None:
newrow.setInteger(columnposition, datatyper(row[fieldname]))
else:
newrow.setNull(columnposition)

if fieldtype == 'bool':
if row[fieldname] != None:
newrow.setBoolean(columnposition, datatyper(row[fieldname]))
else:
newrow.setNull(columnposition)

if fieldtype == 'decimal.Decimal':
if row[fieldname] != None:
newrow.setDouble(columnposition, datatyper(row[fieldname]))
else:
newrow.setNull(columnposition)

if fieldtype == 'datetime.datetime': # sexy datetime splitting
if row[fieldname] != None:
strippeddate = str(datatyper(row[fieldname])).split('.')[0] # just in case we get microseconds (not all datetime uses them)
timechunks = time.strptime(str(strippeddate), "%Y-%m-%d %H:%M:%S") # chunky style!
newrow.setDateTime(columnposition, timechunks[0], timechunks[1], timechunks[2], timechunks[3], timechunks[4], timechunks[5], 0000)
else:
newrow.setNull(columnposition)

columnposition = columnposition + 1 # we gots to know what column number we're working on!
tabletran.insert(newrow) # finally insert buffered row into TDE 'table'
newrow.close()
rowsinserted = rowsinserted + 1

# ok let's write out that file and get back to making dinner
tdefile.close()
csvfile.close()
print '] '
print '('+str(intWithCommas(rowsinserted))+' rows)' # to clear out the row on command line

plist = []

# timing purposes for debugging / optimizing / FUN! This is FUN, Lars.
timetaken = time.time() - start_time
plist.append(timetaken)
plist.append(rowsinserted)
plist.append(os.path.getsize(sourcedir + csvfilename))
plist.append(os.path.getsize(sourcedir + tdefilename))
fileperf[str(csvfilename)] = plist

# just for our "result time"
totaltime = 0
totalrecords = 0
totalpresize = 0
totalpostsize = 0

print ' '

for p in fileperf:
print p + ' ' + str(intWithCommas(int(fileperf[p][0]))) + ' seconds processed ' + str(intWithCommas(fileperf[p][1])) + ' records. Resulting TDE file is ' + str(showhumanfilesize(fileperf[p][3])) + ' (source was ' + str(showhumanfilesize(fileperf[p][2])) + ')'
totaltime = fileperf[p][0] + totaltime
totalrecords = fileperf[p][1] + totalrecords
totalpresize = fileperf[p][2] + totalpresize
totalpostsize = fileperf[p][3] + totalpostsize

if len(fileperf) > 1:
print ' '
print 'TOTAL RUN ' + str(intWithCommas(int(totaltime))) + ' seconds processed ' + str(intWithCommas(totalrecords)) + ' records - crunched ' + str(showhumanfilesize(totalpresize)) + ' of text into ' + str(showhumanfilesize(totalpostsize)) + ' of binary sexiness'

Issues? Let me know below and I can fix them! Maybe.

The post Build Tableau Data Extracts out of CSV Files? More Python TDE API madness! appeared first on Ryan Robitaille.

SQL Server Query to Tableau Data Extract LIKE A BOSS – Some more TDE API fun with Python & Tableau 8

Ry — Wed, 05 Dec 2012 01:20:30 +0000

Coming off the excitement of my last post about writing a simple bare-bones python usage of Tableau's brand-new Data Extracts API from version 8.0 - I figured that it was time to build on that. Let's take a step forward and get a little more complicated - AND a little more useful.

Oh, noes!

I'm encapsulated in a proprietary binary data file OF EMOTION!

Save me, Cringer!

Wait. This could be MORE useful then my last post?

You mean your business doesn't run on a billion rows of dummy shit data? Weird.

Anyways, this time we're going to extract data from SQL Server with a single SQL query and populate a new TDE file.

This script has 3 main features:

Can run any query you want (even 'select * from' queries)
You don't have to pre-define the field names
You don't have to pre-define the data types

I got you, shawty. I GOT YOU.

All of which we had to explicitly state in the last script (even as a 'proof of concept'), and as you can imagine - with a wide result set, defining every little thing and then inserting every little thing can be a huge pain in the ass (not to mention ugly). With my approach, it's literally plug and play - err, paste and play. You can even run queries like "select * from " it's not even necessary to use the field names (assuming they are all unique, that is - more on that later) - and yes, I mentioned that twice for EMPHASIS!

Ye gods! No scripting out my lame_ass_field_names? Huzzah!

Basically, the script works like this:

Connect to SQL Server
Execute your SQL query
Look at the field names and first row data
Have Python try to guess what data types they are (based on that first row)
Map those data types and field names to a TDE schema definition (TableDefinition())
Loop through the entire result set, calling each field by name and type so the correct insert method is used (setDouble, setInteger, setNull, setDateTime, setBoolean, etc)
Do a little headbanging as you watch the text fly by!
Close the file (write the TDE to disk)
Close the SQL connection
Wipe hands on pants

Booya. Done.

And you didn't have to do a damn thing except give it a server connection and a SQL query. Now you can open that fresh TDE file and have yourself a little data party. { Pants optional }

Requires: Python 2.7.X+, the DataExtracts module, and pymssql module (pre-compiled binaries here). Oh, and it would help to have a SQL Server and Tableau Desktop as well. ;)

Here it is! Cut, Paste, and give it a try, gosh darnint!

UPDATE: Added var (rowoutput = True / False) for turning off the debug row / column output - good for looking into errors but slowed the script down like 10x

# ryan robitaille 12/6/2012
# simple Tableau Data Extract creation from a single microsoft sql server sql statement

import dataextract as tde # saves some typing, cause i'm a lazy fucker
import os, time, pymssql # for file manipulation, script timing (not necc), database access!

###################### FOR YOUR PARAMETERS, SON! ######################
tdefilename = 'ufo_datas.tde'
sql = "select * from UFOdata.dbo.Sightings" # whatever
sqlserverhost = 'localhost'
sqlusername = 'sa'
sqlpassword = 'passy'
sqldatabase = 'UFOdata'
rowoutput = False # for DEBUGGING data errors / slows shit down 10X however
###################### FOR YOUR PARAMETERS, SON! ######################

dotsevery = 75

start_time = time.time() # simple timing for test purposes

mssql_db = pymssql.connect(host=sqlserverhost, user=sqlusername, password=sqlpassword, database=sqldatabase, as_dict=True) # as_dict very important
mssql_cursor = mssql_db.cursor()
mssql_cursor.execute(sql)

print ' '
print '[ Note: Each . = ' +str(dotsevery)+ ' rows processed ]'

fieldnameslist = [] # define our empty list

#go through the first row to TRY to set fieldnames and datatypes
for row in mssql_cursor:
itemz = len(row.keys())/2 # because the dict rowset includes BOTH number keys and fieldname keys
for k in row.keys():
fieldnameslist.append(str(k) + '|' + str(type(row[k])).replace(","").replace("'>","").replace(","").replace('NoneType','str').replace('uuid.UUID','str') )
break # after the first row, we SHOULD have a decent idea of the datatypes
# ^ a bit inelegant, but it gets the job done

fieldnameslist.sort() # sort them out so the integer keys are first (we're gonna whack em)
del fieldnameslist[0:itemz] # remove first x amount of keys (should be all integers instead of dict literals)

try: # Just in case the file exists already, we don't want to bomb out
tdefile = tde.Extract(tdefilename) # in CWD
except:
os.system('del '+tdefilename)
os.system('del DataExtract.log') #might as well erase this bitch too
tdefile = tde.Extract(tdefilename)

# ok lets build the table definition in TDE with our list of names and types first
# replacing literals with TDE datatype integers, etc
tableDef = tde.TableDefinition() #create a new table def

if rowoutput == True:
print '*** field names list ***' # debug
for t in fieldnameslist:
fieldtype = t.split('|')[1]
fieldname = t.split('|')[0]
fieldtype = str(fieldtype).replace("str","15").replace("datetime.datetime","13").replace("int","7").replace("decimal.Decimal","10").replace("float","10").replace("uuid.UUID","15").replace("bool","11")
if rowoutput == True:
print fieldname + ' (looks like ' + t.split('|')[1] +', TDE datatype ' + fieldtype + ')' # debug
try:
tableDef.addColumn(fieldname, int(fieldtype)) # if we pass a non-int to fieldtype, it'll fail
except:
tableDef.addColumn(fieldname, 15) # if we get a weird type we don't recognize, just make it a string
if rowoutput == True:
print '***'
time.sleep(5) # wait 5 seconds so you can actually read shit!

if rowoutput == True:
# ok, lets print out the table def we just made, for shits and giggles
print '################## TDE table definition created ######################'
for c in range(0,tableDef.getColumnCount()):
print 'Column: ' + str(tableDef.getColumnName(c)) + ' Type: ' + str(tableDef.getColumnType(c))
time.sleep(5) # wait 5 seconds so you can actually read shit!

# ok lets add the new def as a table in the extract
tabletran = tdefile.addTable("Extract",tableDef)
# why table NEEDS to be called 'Extract' is beyond me

rowsinserted = 1 # we need to count stuff, dude! Robots start at 0, I START AT 1!

# ok, for each row in the result set, we iterate through all the fields and insert based on datatype
for row in mssql_cursor:
if rowoutput == True:
print '************** INSERTING ROW NUMBER: ' + str(rowsinserted) + '**************' # debug output
else: # only print dot every 50 records
if (rowsinserted%dotsevery) == 0:
print '.',

columnposition = 0
newrow = tde.Row(tableDef)
for t in fieldnameslist:
fieldtype = t.split('|')[1]
fieldname = t.split('|')[0]

if rowoutput == True:
print str(columnposition) + ' ' + fieldname + ': ' + str(row[fieldname]) + ' (' + str(fieldtype).split('.')[0] + ')' # debug output

if fieldtype == 'str':
if row[fieldname] != None: # we don't want no None!
newrow.setCharString(columnposition, str(row[fieldname]))
else:
newrow.setNull(columnposition) # ok, put that None here

if fieldtype == 'int':
if row[fieldname] != None:
newrow.setInteger(columnposition, row[fieldname])
else:
newrow.setNull(columnposition)

if fieldtype == 'bool':
if row[fieldname] != None:
newrow.setBoolean(columnposition, row[fieldname])
else:
newrow.setNull(columnposition)

if fieldtype == 'decimal.Decimal':
if row[fieldname] != None:
newrow.setDouble(columnposition, row[fieldname])
else:
newrow.setNull(columnposition)

if fieldtype == 'datetime.datetime': # sexy datetime splitting
if row[fieldname] != None:
strippeddate = str(row[fieldname]).split('.')[0] # just in case we get microseconds (not all datetime uses them)
timechunks = time.strptime(str(strippeddate), "%Y-%m-%d %H:%M:%S") # chunky style!
newrow.setDateTime(columnposition, timechunks[0], timechunks[1], timechunks[2], timechunks[3], timechunks[4], timechunks[5], 0000)
else:
newrow.setNull(columnposition)

columnposition = columnposition + 1 # we gots to know what column number we're working on!
tabletran.insert(newrow) # finally insert buffered row into TDE 'table'
newrow.close()
rowsinserted = rowsinserted + 1

# ok let's write out that file and get back to making dinner
tdefile.close()
mssql_db.close()

# timing purposes for debugging / optimizing / FUN! This is FUN, Lars.
timetaken = time.time() - start_time
print str(rowsinserted) + ' rows inserted in ' + str(timetaken) + ' seconds'
print ' (' + str(rowsinserted/timetaken) + ' rows per second)'
# woo, let's have a drink!

I threw this together in a few hours, and will probably be adding to it, fixing it down the line. It has very little exception handling and is only around 130 lines; including my asinine comments and whitespace lines. Parts of it are a bit 'inelegant', lots of list building and string mangling - but it works. Quite well actually.

Love to get some feedback so I can make it better. I tested it on many different tables I had laying around, including that crazy MS AdventureWorks2008R2 Data Warehouse DB which uses a lot of strange data types and calculated fields. All worked splendidly.

Current Known Issues:

1) I'm only looking at the first row for data type guessing. I really should be taking a much larger sample of the data.

2) If you do a bunch of joins, don't explicitly define the field names in the query, and have duplicate field names in the result set - the file will miss some fields, or even bomb out completely.

i.e. - Don't do a:

select a.*, b.*, c.* from table1 a, table2 b, table3 c 
            where a.id = b.id and a.id = c.id

But you wouldn't do that fucking nonsense anyways - right? ;)

I'm also thinking of making a version of this with command line parameters instead of file editing.

i.e. Python TDE_from_mssql.py 
--file=belly.tde 
--server=servername 
--username=sa 
--database=db45 
--sql="select * from bellybutton"

What's next for TDE file population posts? Web scraping, MySQL, Oracle, flat files? Might as well hit 'em all.

The post SQL Server Query to Tableau Data Extract LIKE A BOSS – Some more TDE API fun with Python & Tableau 8 appeared first on Ryan Robitaille.

Building Tableau Data Extract files with Python in Tableau 8 – Sample Usage

Ry — Mon, 03 Dec 2012 02:11:16 +0000

See also: Tableau Data Extracts from SQL Server or from CSV files
If you were learning (and / or partying) with us at the Tableau Customer Conference 2012 in Beautiful San Diego (where me and 6 other individuals were also crowned 'Tableau Zen Masters', ahem) - then you've probably been playing around with the Tableau 8 Beta over the past few weeks. Tableau 8 brings a whole load of new features and performance improvements, but it also includes 2 new APIs for us tinkering / hack-y peeps.

One of these is the 'Tableau Data Extract API' (also called the 'TDE API' in acronym land, where I have a beach house). TDE will allow us to programatically create Data Extracts for Tableau Desktop and Server using our favorite data-munching language. That is, assuming that your favorite language is Python, C, C++, or Java (barf). This is going to be huge, and while the libraries seem to be in their infancy - I fully expect for them to evolve and add features as we move along.

Python is wicked flexible and ideal for data-centric workflow / runstreams (in my humble opinion, at least). It's my weapon of choice. In essence, these libraries give us one more output format for our scripts / programs...

A "NATIVE Tableau Data Extract".

What IS a Tableau Data Extract you ask?

You know, those super fast binary .tde files that Tableau creates behind the scenes to mitigate your shitty datasource performance? Yes, THOSE.

If you're a fan of this blog you know that I revel in pulling down bizarre datasources from various places and throwing them in Tableau for making my own brand of 'data visualization moonshine'. Usually, I need to write them out to CSV files, Excel files, or a local database first. But with TDE I can write them directly to a usable Tableau file in the same script, saving a step and enabling a whole bunch of other integrations - most of which I haven't even thought of yet.

First things first. Since we're talking about beta stuff, I can't actually tell you where to GET IT. But I can do everything else from there. Besides if you attended TCC12, you probably already have access to it - otherwise, hold out until Q1 2013 - it'll be SO worth it.

It comes in 2 Python versions - 32-bit and 64-bit (both v8-beta2 as of this writing) - for these examples I'm using the 32-bit versions, since most of my Python modules are 32-bit for now.

Installation is straightforward, the old 'python setup.py install' in the module directory. You'll also see that it also comes with a number of Tableau Server DLLs and a tdeserver.exe in the BIN directory (so, sorry folks - Windows only) - but don't worry about the PATH of those files, they'll all get installed in Python's site-packages directory upon installation.

For the most basic and simple usage - there are no other required modules, just the standard ones. I'm using Python 2.7.3 currently.

I'm going to step through creating a sample file (as of this writing, sample usage files and documentation are not included in the packages) but, I'll show more advanced uses in the future, as I expand on the possibilities (custom functions, integration with other data-handy python modules, revamping old scripts, etc).

Simple Usage of TDE Python Module

(a decent starting point, but with no exception handling except built-in with the module)

Annotated anywhere possible, including code comments - full script below.

import dataextract as tde #saves some typing
import os, time # for file manipulation / script timing (not necc)

start_time = time.time() # simple timing for test purposes

try: # Just for testing purposes and re-running
tdefile = tde.Extract('test.tde') #in CWD
except:
os.system('del test.tde')
os.system('del DataExtract.log')
tdefile = tde.Extract('test.tde')

tableDef = tde.TableDefinition() #create a new table def

As you can see I'm simply importing the 'dataextract' module plus a few others. The only reason I included the try/except block is because during testing I was running this bitch over and over again - and it got really annoying to delete the last created file.

tdefile = tde.Extract('test.tde') names my TDE file and creates 'tdefile' for later usage
Then tableDef = tde.TableDefinition() gets me ready to define my file 'schema' (for lack of a better word)

# using integers for now because the literal defs are not in the python module code
tableDef.addColumn("field_type7", 7) #INTEGER
tableDef.addColumn("field_type10", 10) #DOUBLE
tableDef.addColumn("field_type11", 11) #BOOLEAN
tableDef.addColumn("field_type12", 12) #DATE
tableDef.addColumn("field_type13", 13) #DATETIME
tableDef.addColumn("field_type14", 14) #DURATION
tableDef.addColumn("field_type15", 15) #CHAR_STRING
tableDef.addColumn("field_type16", 16) #UNICODE_STRING

As you can see, I'm defining my field name and field data type here.

I couldn't find any place to define the data-types with literal words (like the C and C++ examples show), for now I'm going to stick with the numbering. Besides, 'addColumn' requires an integer for the datatype anyways. Thankfully there are only 8 data types.

# ok, lets print out the table def, just for shits and giggles
for c in range(0,tableDef.getColumnCount()):
print 'Column: ' + str(tableDef.getColumnName(c)) + ' Type: ' + str(tableDef.getColumnType(c))

This is unnecessary, but you may find it useful. Basically, after we define our table layout (in tableDef) we echo it out on the screen. I like to add stuff like this so I have something to look at while it crunches away. So shoot me.

In my case, the output looks like this:

Column: field_type7 Type: 7
Column: field_type10 Type: 10
Column: field_type11 Type: 11
Column: field_type12 Type: 12
Column: field_type13 Type: 13
Column: field_type14 Type: 14
Column: field_type15 Type: 15
Column: field_type16 Type: 16

# lets add the new def as a table in the extract
tabletran = tdefile.addTable("Extract",tableDef)
# why table NEEDS to be called 'Extract' is beyond me

rowsinserted = 1

Ok, now that we've defined the table schema (tableDef) - we need to create that 'table' in our TDE file we defined. As of now, you are (seemingly) required to name the table "Extract", not that it really matters at this time.

"rowsinserted" is just so I can count all the rows and echo the number out at the end.

# let's create a bunch of rows! wheeeeee!
for i in range(1,1000000):

newrow = tde.Row(tableDef)
#newrow.setNull(0) #column
newrow.setInteger(0,i) #column, value
newrow.setDouble(1,i*1.4) #column, value
newrow.setBoolean(2,1) #column, value (1/0)
newrow.setDate(3, 2012, 12, 2) #column, y,m,d
newrow.setDateTime(4, 2012, 12, 2, 18, 25, 55, 0000) #column, y,m,d,h,m,s,frac
newrow.setDuration(5, 6, 18, 25, 55, 0000) #column, d,h,m,s,frac
newrow.setCharString(6, 'Character String') #column, 'value'
newrow.setString(7, 'Unicode String!') #column, 'value'
rowsinserted = rowsinserted + 1 #count this row!

tabletran.insert(newrow) #insert row into TDE table
newrow.close()

tdefile.close()

Here is my main "insert" loops. Let's go step by step (the 'for i in range(x,x)' is just to run this block multiple times and generate some row depth for sample purposes).

Define our "newrow" as (our previously defined schema) tableDef

Insert each field in our new row - based on the different datatype we defined it as (for validation purposes) Note for each particular datatype we have to use a different format after defining the column number (based on the order they exist in the schema)

Once done, we insert that defined row (schema and data) into our file object

Close that row object since it will be re-opened next row (not sure if this is required, just keeping it clean)

Finally, when all rows are inserted - tdefile.close() writes it all to disk

timetaken = time.time() - start_time #just for timing and fun
print str(rowsinserted) + ' rows inserted in ' + str(timetaken) + ' seconds'
print ' (' + str(rowsinserted/timetaken) + ' rows per second)'
#booya!

Just printing some info about the execution - not needed - BUT IT'S FUN!

1000000 rows inserted in 34.0269999504 seconds
    (29388.4268803 rows per second)

Here's the whole shebang, Cut-n-Paste Away!

# ryan robitaille 12/2/2012
# really simple TDE creation test

import dataextract as tde #saves some typing
import os, time # for file manipulation / script timing (not necc)

start_time = time.time() # simple timing for test purposes

try: # Just for testing purposes and re-running
tdefile = tde.Extract('test.tde') #in CWD
except:
os.system('del test.tde')
os.system('del DataExtract.log')
tdefile = tde.Extract('test.tde')

tableDef = tde.TableDefinition() #create a new table def

# using integers for now because the literal defs are not in the python module code
tableDef.addColumn("field_type7", 7) #INTEGER
tableDef.addColumn("field_type10", 10) #DOUBLE
tableDef.addColumn("field_type11", 11) #BOOLEAN
tableDef.addColumn("field_type12", 12) #DATE
tableDef.addColumn("field_type13", 13) #DATETIME
tableDef.addColumn("field_type14", 14) #DURATION
tableDef.addColumn("field_type15", 15) #CHAR_STRING
tableDef.addColumn("field_type16", 16) #UNICODE_STRING

# ok, lets print out the table def, just for shits and giggles
for c in range(0,tableDef.getColumnCount()):
print 'Column: ' + str(tableDef.getColumnName(c)) + ' Type: ' + str(tableDef.getColumnType(c))

# lets add the new def as a table in the extract
tabletran = tdefile.addTable("Extract",tableDef)
# why table NEEDS to be called 'Extract' is beyond me

rowsinserted = 1

# let's create a bunch of rows! wheeeeee!
for i in range(1,1000000):

newrow = tde.Row(tableDef)
#newrow.setNull(0) #column
newrow.setInteger(0,i) #column, value
newrow.setDouble(1,i*1.4) #column, value
newrow.setBoolean(2,1) #column, value (1/0)
newrow.setDate(3, 2012, 12, 2) #column, y,m,d
newrow.setDateTime(4, 2012, 12, 2, 18, 25, 55, 0000) #column, y,m,d,h,m,s,frac
newrow.setDuration(5, 6, 18, 25, 55, 0000) #column, d,h,m,s,frac
newrow.setCharString(6, 'Character String') #column, 'value'
newrow.setString(7, 'Unicode String!') #column, 'value'
rowsinserted = rowsinserted + 1 #count this row!

tabletran.insert(newrow) #insert row into TDE table
newrow.close()

tdefile.close()

timetaken = time.time() - start_time #just for timing and fun
print str(rowsinserted) + ' rows inserted in ' + str(timetaken) + ' seconds'
print ' (' + str(rowsinserted/timetaken) + ' rows per second)'

What's next? Building TDEs from web screen-scraping as well as using the MessyTables python module for turning ugly CSV and XLS into sexified TDEs (hopefully). Stay tuned - this topic is somewhat of a moving target...

UPDATE: Changed the code loop to a million rows.. instead of a BILLION. Oops. For the record, on my desktop, generating a billion rows took 9.5 hours - approx 33,924 rows per second (using 64-bit Python and Tableau libs)

Want more? Here is a follow-up post creating TDE files from SQL Server queries!

The post Building Tableau Data Extract files with Python in Tableau 8 – Sample Usage appeared first on Ryan Robitaille.

Face Harvesting: Download your Facebook data for External Reporting and Visualization

Ry — Wed, 17 Oct 2012 00:11:34 +0000

I'm always on the lookout for cool new data feeds that I can mess with - as you can clearly see from some of the other posts on this site.

Usually it involves some scripting, screen-scraping, system fiddling, and general madness - basically meaning that some people can get it to work, while others cannot. This bothers me, so THIS little "data harvester"... well, this time it's all automatic, baby!

Anyways. where was I? Oh yeah, that's right - we're talking about the good ole' Book of Faces again...

Facebook.

One of the great white whales of the data world.

Thankfully instead of harpoon, we have their nifty Open Graph API.

I previously posted about pulling data from the Facebook Open Graph - but that was just an unauthenticated search on posts in the "public stream", this is completely different...

No need to cut and paste scripts this time. Basically, I made a small web app that (once properly authenticated and given permission) will download a decent portion of your Facebook records in MySQL tables and/or CSV files for you to do whatever you want with. Not only your personal data (friends, comments, posts, etc), but also "Page" data (insights, posts, likes, etc) for any Pages that you might be a administrator of. For a random user with no "Fan Pages" (like me), it's just a cool way to bring up your data and check it out (maybe in Tableau, hint, hint), but for someone who runs an actual Fan Page, it can be a great deal more interesting.

Hell, you could even massage this data, crank it into something like SPSS or R, and generate something really interesting.

Here's whats going to happen.

You will click the button below
You will be re-directed to log into Facebook
You will have to give the 'Data Harvester' app permission to see your FB data
You will be re-directed back to this site
A live counter will begin to show records pulled from key FB "tables"
A big-ass button will appear with a link to the "data download" page when it's done

Pulling Data!
It's DONE!
OMG MySQL!
OMG CSV!

We're off to the races, pullin' data!

Finally!

Here's a shitty (and incomplete) diagram to give you an idea of what kind of data we will have access to.

The actual data pull could take a long ass time - especially if you have several 'Fan Pages' with associated Insight Data. I'm looking into notification methods that don't require staying on the page for the download link.

Get Face Data NOW!

FYI, your data automagically gets deleted after 24 hours.

I'm sure that there are tons of problems and bugs, so please don't be shy to let me know how it works for you! Cheers!

The post Face Harvesting: Download your Facebook data for External Reporting and Visualization appeared first on Ryan Robitaille.

Turning Guitar Tabs into structured Data. Silicon crunching Metal.

Ry — Tue, 07 Aug 2012 23:46:43 +0000

I'm always looking for data from unlikely sources, especially sources that are close to my own bizarre (and predictable) interests. Anyone who grew up learning (or still plays) guitar at one point or another has probably used tablature at one time or another.

From the old Cherry Hill tab books of our favorite albums and artists, to the internet age where tens of thousands of plain-text format tabs because available all over the place (of varying levels of correctness at times).

I've been playing off and on for the past 21 years - more "on" lately than any time in recent memory.

So, I had an idea. What if I were to script out a way to crunch through an old school tab text file, split it up in a usable / logical way, and insert it all into a database so I can analyze and display it later via different methods. What would the data tell me?

What if I could compile 100s of these files in this way?
What could I do with the data once it was transformed?
Would anyone even give a shit?

Well, I've taken the first step - both in the CRUNCHING side and on the VISUALIZING side.

First, the end product - we can save the technical shit for AFTER. As you can see from the shot above. I took a (mostly correct) guitar tab for Metallica's Enter Sandman from ultimate-guitar.com - crunched it, note-for-note, into a database table and built a "analytic report" (using the term very loosely) out of it using Tableau Public.

Link: Learning Enter Sandman with Visual Analytics

Now, on to the "how"...

If you look at my source tab, it's not that straightforward for computer reading with all the literal "3x", "12x" etc. I had to streamline it for line-by-line (and col-by-col) machine reading. You can see the original tab I started with here.

My "machine-friendly streamlined" version

?Metallica|Enter Sandman|The Black Album|http://tabs.ultimate-guitar.com/m/metallica/enter_sandman_tab.htm

+01 Intro:
#|
1|------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2|------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3|------------------------------------------------------------------------------------------------------------------------------------------------------------------------
4|------------5---------------------------5---------------------------5---------------------------5---------------------------5---------------------------5---------------
5|--------7---------------7-----------7---------------7-----------7---------------7-----------7---------------7-----------7---------------7-----------7---------------7---
6|----0-----------6---5-----------0-----------6---5-----------0-----------6---5-----------0-----------6---5-----------0-----------6---5-----------0-----------6---5-------

#|
1|------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2|------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3|------------------------------------------------------------------------------------------------------------------------------------------------------------------------
4|------------5---------------------------5---------------------------5---------------------------5---------------------------5---------------------------5---------------
5|--------7---------------7-----------7---------------7-----------7---------------7-----------7---------------7-----------7---------------7-----------7---------------7---
6|----0-----------6---5-----------0-----------6---5-----------0-----------6---5-----------0-----------6---5-----------0-----------6---5-----------0-----------6---5-------

+02 Rhythm Fill 1:
#| PM------------------- sl PM----------------------- sl PM----------------------- sl PM----------------------- sl PM--- PM------- P PM--- PM------- P PM--- PM------- P PM--- PM------- P
1|----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2|----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3|----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
4|----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
5|--2---------------------------/7------------------------------/7--------------------------------/7---------------------------------/7-----------7------------------3\2--------7------------------3\2--------7------------------3\2--------7------------------3\2
6|--0---0---0---0---0---0---0--------0---0---0---0---0---0---0---------0---0---0---0---0---0---0----------0---0---0---0---0---0---0-------0---0------6---0---0---0---1\00---0------6---0---0---0---1\00---0------6---0---0---0---1\00---0------6---0---0---0---1\0

#|PM--- PM--- P PM--- PM--- P PM--- PM--- P PM--- PM--- P PM--- PM--- P PM--- PM--- P PM--- PM--- P PM--- PM--- P
1|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
4|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
5|--------7-------------------3\2-----------7-------------------3\2-----------7-------------------3\2-----------7-------------------3\2-----------7-------------------3\2-----------7-------------------3\2-----------7-------------------3\2-----------7-------------------3\2---
6|0---0-------6---5---0---0---1\0---0---0-------6---5---0---0---1\0---0---0-------6---5---0---0---1\0---0---0-------6---5---0---0---1\0---0---0-------6---5---0---0---1\0---0---0-------6---5---0---0---1\0---0---0-------6---5---0---0---1\0---0---0-------6---5---0---0---1\0---

+03 Rhythm Fill 2:
#| sl V sl V sl V PM------------------- sl V sl V sl V PM-------------------
1|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
4|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
5|--2---2----/7-------------2---2----/7-------------2---2----/7--------------5-------5-------4---5---4--------2---2----/7-------------2---2----/7-------------2---2----/7--------------5-------5-------4---5---4------
6|--0---0---------6---5-----0---0---------6---5-----0---0---------6---5------3---0---3---0---2---3---2--------0---0---------6---5-----0---0---------6---5-----0---0---------6---5------3---0---3---0---2---3---2------

+04 Rhythm Fill 3:
#| PM------------------- P PM------------------------ P PM----------------------- PM------------
1|--------------------------------------------------------------------------------------------------------------------------------------------------------
2|--------------------------------------------------------------------------------------------------------------------------------------------------------
3|--------------------------------------------------------------------------------------------------------------------------------------------------------
4|--------------------------------------------------------------------------------------------------------------------------------------------------------
5|--2----------------------------3\2-------------------------------3\2-----------------------------------------5-------5-------4---5---4------------------
6|--0---0---0---0---0---0---0----1\0---0---0---0---0---0---0---0---1\0----------0---0---0---0---0---0---0------3---0---3---0---2---3---2------------------

#| PM------------------- P PM------------------------ P PM----------------------- PM------------
1|--------------------------------------------------------------------------------------------------------------------------------------------------------
2|--------------------------------------------------------------------------------------------------------------------------------------------------------
3|--------------------------------------------------------------------------------------------------------------------------------------------------------
4|--------------------------------------------------------------------------------------------------------------------------------------------------------
5|--2----------------------------3\2-------------------------------3\2-----------------------------------------5-------5-------4---5---4------------------
6|--0---0---0---0---0---0---0----1\0---0---0---0---0---0---0---0---1\0----------0---0---0---0---0---0---0------3---0---3---0---2---3---2------------------

+05 Bridge:
#| PM---------------------------
1|------------------------------------------------------------------------------------------------------------------------
2|------------------------------------------------------------------------------------------------------------------------
3|---------2---------------------2---------------------2------------------------------------------------------------------
4|-----4---------------------4---------------------4----------------------------------------------------------------------
5|-------------3---2-----------------3---2-----------------3---2-------4---4---4---4---4---2---3---4----------------------
6|--2---------------------2---------------------2----------------------2---2---2---2---2---0---1---2----------------------

+06 Chorus Fill:
#| PM------------
1|----------------------------------------------------------------------------
2|----------------------------------------------------------------------------
3|----------------------------------------------------------------2-----------
4|------5---4---------5---4---------5---4---------5---2---5---2-------2-------
5|--4---3---2-----4---3---2-----4---3---2----2--------------------------------
6|--2-------------2-------------2------------0--------------------------------

#| PM----------- PM-----------
1|----------------------------------------------------------------------------
2|----------------------------------------------------------------------------
3|------------------------------------2---------------------------------------
4|------5---4---------5---2---5---2-------2-----------------------------------
5|--4---3---2-----2----------------------------5-------5-------4---5---5------
6|--2-------------0----------------------------3---0---3---0---2---3---3------

+07 Rhythm Fill 2:
#| sl V sl V sl V PM------------------- sl V sl V sl V PM-------------------
1|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
4|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
5|--2---2----/7-------------2---2----/7-------------2---2----/7--------------5-------5-------4---5---4--------2---2----/7-------------2---2----/7-------------2---2----/7--------------5-------5-------4---5---4------
6|--0---0---------6---5-----0---0---------6---5-----0---0---------6---5------3---0---3---0---2---3---2--------0---0---------6---5-----0---0---------6---5-----0---0---------6---5------3---0---3---0---2---3---2------

+08 Rhythm Fill 3:
#| PM------------------- P PM------------------------ P PM----------------------- PM------------
1|--------------------------------------------------------------------------------------------------------------------------------------------------------
2|--------------------------------------------------------------------------------------------------------------------------------------------------------
3|--------------------------------------------------------------------------------------------------------------------------------------------------------
4|--------------------------------------------------------------------------------------------------------------------------------------------------------
5|--2----------------------------3\2-------------------------------3\2-----------------------------------------5-------5-------4---5---4------------------
6|--0---0---0---0---0---0---0----1\0---0---0---0---0---0---0---0---1\0----------0---0---0---0---0---0---0------3---0---3---0---2---3---2------------------

#| PM------------------- P PM------------------------ P PM----------------------- PM------------
1|--------------------------------------------------------------------------------------------------------------------------------------------------------
2|--------------------------------------------------------------------------------------------------------------------------------------------------------
3|--------------------------------------------------------------------------------------------------------------------------------------------------------
4|--------------------------------------------------------------------------------------------------------------------------------------------------------
5|--2----------------------------3\2-------------------------------3\2-----------------------------------------5-------5-------4---5---4------------------
6|--0---0---0---0---0---0---0----1\0---0---0---0---0---0---0---0---1\0----------0---0---0---0---0---0---0------3---0---3---0---2---3---2------------------

+09 Bridge:
#| PM---------------------------
1|------------------------------------------------------------------------------------------------------------------------
2|------------------------------------------------------------------------------------------------------------------------
3|---------2---------------------2---------------------2------------------------------------------------------------------
4|-----4---------------------4---------------------4----------------------------------------------------------------------
5|-------------3---2-----------------3---2-----------------3---2-------4---4---4---4---4---2---3---4----------------------
6|--2---------------------2---------------------2----------------------2---2---2---2---2---0---1---2----------------------

+10 Chorus Fill:
#| PM------------
1|----------------------------------------------------------------------------
2|----------------------------------------------------------------------------
3|----------------------------------------------------------------2-----------
4|------5---4---------5---4---------5---4---------5---2---5---2-------2-------
5|--4---3---2-----4---3---2-----4---3---2----2--------------------------------
6|--2-------------2-------------2------------0--------------------------------

#| PM----------- PM-----------
1|----------------------------------------------------------------------------
2|----------------------------------------------------------------------------
3|------------------------------------2---------------------------------------
4|------5---4---------5---2---5---2-------2-----------------------------------
5|--4---3---2-----2----------------------------5-------5-------4---5---5------
6|--2-------------0----------------------------3---0---3---0---2---3---3------

+11 Solo
#|
1|-------------------------------------------------------------
2|-15-15-15-12----12---------------12-12----15b17-15b17-(15)\--
3|-------------14-----14b16---(14)-------14--------------------
4|-------------------------------------------------------------
5|-------------------------------------------------------------
6|-------------------------------------------------------------

#|
1|--------------------------------------------------
2|--------------------------------------------------
3|-12/14-14-14-14\12-12-12-12/14-14-14-14\12-12-12--
4|--------------------------------------------------
5|--------------------------------------------------
6|--------------------------------------------------

#|
1|------------------------------------------
2|------------------------------------------
3|-14p12------------------------------------
4|-------14--14p12--------------------------
5|-----------------14--14p12\10-------------
6|------------------------------12-~-(12)\--

#|
1|-------12-12-12-------12-12-12-------12-12-12-------12-12-12--
2|-15b17----------15b17----------15b17----------15b17-----------
3|--------------------------------------------------------------
4|--------------------------------------------------------------
5|--------------------------------------------------------------
6|--------------------------------------------------------------

#|
1|-------12-12-12h15p12-12h15p12-12h15p12-12-15-12-12-14-14--
2|-15b17-----------------------------------------------------
3|-----------------------------------------------------------
4|-----------------------------------------------------------
5|-----------------------------------------------------------
6|-----------------------------------------------------------

#|
1|-17p14----17p14----17p14-19p15----19p15----19p15--
2|-------15-------15-------------17-------17--------
3|--------------------------------------------------
4|--------------------------------------------------
5|--------------------------------------------------
6|--------------------------------------------------

#|
1|-21p17----21p17----21p17----22b24-(22)\-------------------------
2|-------17-------17-------17-------------9b10---(9)---9b10---9---
3|----------------------------------------9b11---(9)---9b11---9---
4|----------------------------------------------------------------
5|----------------------------------------------------------------
6|----------------------------------------------------------------

#|
1|-------------------------------------------------------------------
2|-9b10--9b10-----------------10---(10)----10-8-7---8\7---7----------
3|-9b11--9b11--7--9--------7----------------------9-----9---9-7---7--
4|--------------------7h9---------------------------------------9----
5|-------------------------------------------------------------------
6|-------------------------------------------------------------------

#|
1|------------------------------------------------------------------
2|------------------------------------------------------------------
3|-9p7---------------------------16b18-14-----14-16b18--14------14--
4|-----9p7---7-9-7------------------------16----------------16------
5|---------9-------9-8-7--------------------------------------------
6|-----------------------10-7\--------------------------------------

#|
1|------------------------------------
2|------------------------------------
3|-16b18-14----14-16b18-14--------14--
4|----------16-------------16--16-----
5|------------------------------------
6|------------------------------------

#|
1|-----------------------------------------------------------------------
2|-------17-------17------------17---------------------------------------
3|-16b18----16b18----16b18---------16b18---(16)-\1/-\1/-\1/-\1/-\1/-\1/--
4|-----------------------------------------------------------------------
5|----------------------------------------------accentuate-w/bar---------
6|-----------------------------------------------------------------------

#|
1|-21p17----21p17----21p17----21p17----21p17----21p17-----
2|-------17-------17-------17-------17-------17-------17--
3|--------------------------------------------------------
4|--------------------------------------------------------
5|--------------------------------------------------------
6|--------------------------------------------------------

#|
1|-22b24--22-22-22b24--22-22-22b24-~-~-(22)\--
2|--------------------------------------------
3|--------------------------------------------
4|--------------------------------------------
5|--------------------------------------------
6|--------------------------------------------

#|
1|--------------------------------------------------------
2|-10p8p7-------------------------------------------------
3|--------9--9p7------------------------------------------
4|---------------9--9p7-5--7b9---5------------------------
5|----------------------------------7---(7)-~-~-~----5h7--
6|------------------------------------------w/bar---------

#|
1|------------------------------------------------------
2|------------------------------------------------------
3|------------------------------------------------------
4|------------------------------------------------------
5|-4---(4)-~-~-~-~-----0----2--\1/-\1/-\1/-\1/-\1/-\1/--
6|------------------------------------------------------

+12 Prayer Intro:
#|
1|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
4|------------5---------------------------5---------------------------5---------------------------5---------------------------5---------------------------5---------------------------5---------------------------5---------------
5|--------7---------------7-----------7---------------7-----------7---------------7-----------7---------------7-----------7---------------7-----------7---------------7-----------7---------------7-----------7---------------7---
6|----0-----------6---5-----------0-----------6---5-----------0-----------6---5-----------0-----------6---5-----------0-----------6---5-----------0-----------6---5-----------0-----------6---5-----------0-----------6---5-------

+13 Bridge:
#| PM--------------------------- PM---------------------------
1|------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2|------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3|---------2---------------------2---------------------2----------------------2----------------------2---------------------2----------------------------------------------------------------
4|-----4---------------------4---------------------4----------------------4----------------------4---------------------4--------------------------------------------------------------------
5|-------------3---2-----------------3---2-----------------3---2------------------3---2------------------3---2-----------------3---2-----4---4---4---4---4---2---3---4----------------------
6|--2---------------------2---------------------2----------------------2----------------------2---------------------2--------------------2---2---2---2---2---0---1---2----------------------

+14 Chorus Fill:
#| PM------------ PM------------
1|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3|----------------------------------------------------------------2--------------------------------------------------------------------------------------------------------------2------
4|------5---4---------5---4---------5---4---------5---2---5---2-------2--------5---4-----------------------------------5---4---------5---4---------5---4---------5---2---5---2-------2--
5|--4---3---2-----4---3---2-----4---3---2----2-----------------------------4---3---2-----2-------------------------4---3---2-----4---3---2-----4---3---2----2---------------------------
6|--2-------------2-------------2------------0-----------------------------2-------------0-------------------------2-------------2-------------2------------0---------------------------

#| PM----------- PM-----------
1|----------------------------------------------------------------------------
2|----------------------------------------------------------------------------
3|------------------------------------2---------------------------------------
4|------5---4---------5---2---5---2-------2-----------------------------------
5|--4---3---2-----2----------------------------5-------5-------4---5---5--2---
6|--2-------------0----------------------------3---0---3---0---2---3---3--0---

+15 Rhythm Fill 2:
#| sl V sl V BOOOM! sl V sl V sl V sl V sl V PM-------------------
1|----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2|----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3|----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
4|----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
5|--2---2----/7-------------2---2----/7-------------2---2----/7-------------2---2----/7-------------2---2----/7-------------2---2----/7-------------2---2----/7--------------5-------5-------4---5---4------
6|--0---0---------6---5-----0---0---------6---5-----0---0---------6---5-----0---0---------6---5-----0---0---------6---5-----0---0---------6---5-----0---0---------6---5------3---0---3---0---2---3---2------

+16 Outro:
#| PM PM--- P PM---------------------- P PM---------------------- P PM---------------------- P
1|--------------------------------------------------------------------------------------------------------------------------------------------
2|--------------------------------------------------------------------------------------------------------------------------------------------
3|--------------------------------------------------------------------------------------------------------------------------------------------
4|--------------------------------------------------------------------------------------------------------------------------------------------
5|--2-------7-------------------3\2-----------7------------------3\2-----------7------------------3\2-----------7------------------3\2--------
6|--0---0-------6---5---0---0---1\0---0---0------6---5---0---0---1\0---0---0------6---5---0---0---1\0---0---0------6---5---0---0---1\0--------

#| PM--- PM------- P PM--- PM------- P PM--- PM------- P PM--- PM------- P PM--------------------------- PM--------------------------- PM--------------------------- PM---------------------------
1|----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2|----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3|----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
4|----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
5|----------7------------------3\2-------------7------------------3\2-------------7------------------3\2-------------7------------------3\2-------------7-------------------------------7-------------------------------7-------------------------------7---------------------------
6|--0---0------6---0---0---0---1\0-----0---0------6---0---0---0---1\0-----0---0------6---0---0---0---1\0-----0---0------6---0---0---0---1\0-----0---0-------0---0---0---0---0---0---0-------0---0---0---0---0---0---0-------0---0---0---0---0---0---0-------0---0---0---0---0-------

#| PM-------------------------------
1|----------------------------------------------------------------------------
2|----------------------------------------------------------------------------
3|----------------------------------------------------------------------------
4|----------------------------------------------------------------------------
5|----------------------------------------------------------------------------
6|--0---0---0---0---0---0---0---0---0-----------------------------------------

Note that I repeated sections where it was needed and evened out all the whitespace in regards to PalmMuting, slide, etc notations.

I also created a tagging system for both what "part" we were on as well as the sequence of that part (really important for re-ordering data in a viz, especially when you have repeating "parts" that may or may not be EXACTLY the same). Which made things slightly more complicated than I anticipated, but not much.

The cool thing about sequencing LINEAR rhythm parts (no multi-part overlapping yet, including solos) is that you can build Gantt charts out of it - and they actually make sense from a arrangement perspective.

My Python way of converting this into a table was to use multiple dictionaries and be very conscience of column number and "line type" so they can iterated back through at the end and have the same orientation, string-wise. There are probably better ways to do this (ones that have more fault-tolerance as far as whitespace goes, etc), but this was the fastest way I could think of - and it only came out to around ~130 lines (and is already fast as hell).

import requests, pprint, json, pymssql, psutil, urllib
import time, os, string, sys, time
import pymssql

mssql_db = pymssql.connect(host='localhost:1450', user='sa', password='****', database='FretLogic')
mssql_cursor = mssql_db.cursor()

pp = pprint.PrettyPrinter(indent=3)

# For converting WELL FORMATTED (FULL) tablature to MSSQL Server Tables for later analysis...
f = open('sandmantab-modded.txt')

line = f.readline()

PMfretlogic = dict()
E1fretlogic = dict()
B2fretlogic = dict()
G3fretlogic = dict()
D4fretlogic = dict()
A5fretlogic = dict()
E6fretlogic = dict()
PRTfretlogic = dict()
POfretlogic = dict()

part_type = ' '

nummPM = 0
nummE1 = 0
nummB2 = 0
nummG3 = 0
nummD4 = 0
nummA5 = 0
nummE6 = 0

for lineno, linestr in enumerate(f):
print "line number: " + str(lineno) + ": " + linestr.rstrip()
rowtype = linestr[0] #what string, what type of data
print rowtype

if rowtype == '+':
part_type = str(linestr.rstrip()).replace('+','').replace(':','')
part_order = str(part_type[0])+str(part_type[1])
print part_order
part_type = part_type[2:]
print part_type

if rowtype == '?': #grab song data from ?string|shit
##Artist|Song|Album|link
artist_data = str(linestr.rstrip()).replace('?','')
artist = artist_data.split('|', 1)[0]
song = artist_data.split('|', 2)[1]
album = artist_data.split('|', 3)[2]
links = artist_data.split('|', 4)[3]
#print part_type

for colno, linecol in enumerate(linestr):
#print "line number: " + str(lineno) + " col number: " + str(colno) + ": " + linecol.rstrip()

if rowtype == '#': #'PALM MUTER line'
if linecol <> '|':
PMfretlogic[nummPM] = str(linecol).replace("\n",'')
PRTfretlogic[nummPM] = str(part_type)
POfretlogic[nummPM] = str(part_order)
nummPM = nummPM + 1
elif rowtype == '1': #'1E'
if linecol <> '|':
E1fretlogic[nummE1] = str(linecol).replace("\n",'')
PRTfretlogic[nummE1] = str(part_type)
POfretlogic[nummE1] = str(part_order)
nummE1 = nummE1 + 1
elif rowtype == '2': #'2B'
if linecol <> '|':
B2fretlogic[nummB2] = str(linecol).replace("\n",'')
PRTfretlogic[nummB2] = str(part_type)
POfretlogic[nummB2] = str(part_order)
nummB2 = nummB2 + 1
elif rowtype == '3': #'3G'
if linecol <> '|':
G3fretlogic[nummG3] = str(linecol).replace("\n",'')
PRTfretlogic[nummG3] = str(part_type)
POfretlogic[nummG3] = str(part_order)
nummG3 = nummG3 + 1
elif rowtype == '4': #'4D'
if linecol <> '|':
D4fretlogic[nummD4] = str(linecol).replace("\n",'')
PRTfretlogic[nummD4] = str(part_type)
POfretlogic[nummD4] = str(part_order)
nummD4 = nummD4 + 1
elif rowtype == '5': #'5A'
if linecol <> '|':
A5fretlogic[nummA5] = str(linecol).replace("\n",'')
PRTfretlogic[nummA5] = str(part_type)
POfretlogic[nummA5] = str(part_order)
nummA5 = nummA5 + 1
elif rowtype == '6': #'6E'
if linecol <> '|':
E6fretlogic[nummE6] = str(linecol).replace("\n",'')
PRTfretlogic[nummE6] = str(part_type)
POfretlogic[nummE6] = str(part_order)
nummE6 = nummE6 + 1

#pp.pprint(B2fretlogic)
print 'cols ' + str(nummPM) + ' ' + str(nummE1) + ' ' + str(nummB2) + ' ' + str(nummG3) + ' ' + str(nummD4) + ' ' + str(nummA5) + ' ' + str(nummE6)
print 'lines ' + str(lineno)

for i in range(nummE1):
try:

if str(E1fretlogic[i]) == '1' and str(B2fretlogic[i]) == '2' and str(G3fretlogic[i]) == '3' and str(D4fretlogic[i]) == '4' and str(A5fretlogic[i]) == '5' and str(E6fretlogic[i]) == '6':
pass
elif str(E1fretlogic[i]) == '':
pass
else:
print str(i) + '-' +str(artist) + ' ' + str(song) + ' ' + str(album) + ' ' + str(links) + ' ' +str(PRTfretlogic[i]) + ' ' + str(PMfretlogic[i]) + ' ' + str(E1fretlogic[i]) + ' ' + str(B2fretlogic[i]) + ' ' + str(G3fretlogic[i]) + ' ' + str(D4fretlogic[i]) + ' ' + str(A5fretlogic[i]) + ' ' + str(E6fretlogic[i])
mssql_cursor.execute("""insert into FretLogic.dbo.TabData (PickId, Test, Artist, Song, Album, TabLink, Part, PartOrder, PM, E1, B2, G3, D4, A5, E6) values ('""" + str(i) + """', GETDATE(), '""" + str(artist) + """', '""" + str(song) + """', '""" + str(album) + """', '""" + str(links) + """', '""" +str(PRTfretlogic[i]) + """', '""" +str(POfretlogic[i]) + """', '""" + str(PMfretlogic[i]) + """', '""" + str(E1fretlogic[i]) + """', '""" + str(B2fretlogic[i]) + """', '""" + str(G3fretlogic[i]) + """', '""" + str(D4fretlogic[i]) + """', '""" + str(A5fretlogic[i]) + """', '""" + str(E6fretlogic[i]) + """' )""")

except:
print 'cols ' + str(nummPM) + ' ' + str(nummE1) + ' ' + str(nummB2) + ' ' + str(nummG3) + ' ' + str(nummD4) + ' ' + str(nummA5) + ' ' + str(nummE6)
print 'lines ' + str(lineno)
print sys.exc_info()[0] #debug for fuckups
print sys.exc_info()[1] #debug for fuckups

print artist
print song
print album

mssql_cursor.execute("update TabData set Vert = (E1 + CHAR(13) + B2 + CHAR(13) + G3 + CHAR(13) + D4 + CHAR(13) + A5 + CHAR(13) + E6)")

mssql_db.commit()
f.close()
#Let's drink!

On my TO DO List:

Actual note lookup (i.e. being able to name chords, E5, and single notes etc)
much less human intervention required on the source files
Lyrics as new line in tab reading?
Some type of 1:1 time references, some permitting

Regardless, I'm def going to "viz out" a few more song's off 'The Black Album' just for the hell of it. I think I can make some usability improvements over this one.

The post Turning Guitar Tabs into structured Data. Silicon crunching Metal. appeared first on Ryan Robitaille.

Python Snippet: Sending HTML email with an attachment via Google Apps SMTP (or Gmail)

Ry — Thu, 26 Jul 2012 22:14:42 +0000

This is just one of those things that I had to do in a project (as part of a much bigger system), and wish I had it all laid out for me like this when I was writing it.

I broke it down into simple input variables that need to be filled out, and then let 'er rip.

You supply the account credentials, to address, from address, subject, HTML body (will be converted to plain as well, in case the email reader cannot support HTML for whatever reason), etc and everything else gets taken care of.

Should be easy to take the whole thing and throw it into a loop from a DB resultset, or something of that nature.

I initially wrote it to be used as part of a system that sent out different versions of "welcome" emails, as well as automated follow-up emails based on customer data and bunch of other shit. Fun times!

You're WELCOME!

import smtplib, os, re, sys, glob, string, datetime
from email.MIMEMultipart import MIMEMultipart
from email.MIMEBase import MIMEBase
from email.MIMEText import MIMEText
from email import Encoders

#attachmentname = 'C:\\python27\\email.log' #path to an attachment, if you wish

username = 'someguy@mygoogleapps-or-gmail.com'
password = 'superSECRETdinoCLUBpasswords!!'

fromaddr = 'Some Guy ' #must be a vaild 'from' addy in your GApps account
toaddr = 'someoneelse@eelsandheels.com'
replyto = fromaddr #unless you want a different reply-to

msgsubject = 'This is for sexy email subjektz!'

htmlmsgtext = """This is my message body in HTML

Yup. Yup. Yup.

Done!"""

#ok, here goes nothing
try:

msgtext = htmlmsgtext.replace('','').replace('','').replace('
',"\r").replace('
',"\r").replace('
',"\r").replace('','')
msgtext = re.sub('<.*?>','',msgtext)

#pain the ass mimey stuff
msg = MIMEMultipart()
msg.preamble = 'This is a multi-part message in MIME format.\n'
msg.epilogue = ''

body = MIMEMultipart('alternative')
body.attach(MIMEText(msgtext))
body.attach(MIMEText(htmlmsgtext, 'html'))
msg.attach(body)

if 'attachmentname' in globals(): #DO WE HAZ ATTACHMENT?
f = attachmentname
part = MIMEBase('application', "octet-stream")
part.set_payload( open(f,"rb").read() )
Encoders.encode_base64(part)
part.add_header('Content-Disposition', 'attachment; filename="%s"' % os.path.basename(f))
msg.attach(part)

msg.add_header('From', fromaddr)
msg.add_header('To', toaddr)
##msg.add_header('Cc', ccaddy) #doesn't work apparently
##msg.add_header('Bcc', bccaddy) #doesn't work apparently
msg.add_header('Subject', msgsubject)
msg.add_header('Reply-To', replyto)

# The actual email sendy bits
server = smtplib.SMTP('smtp.gmail.com:587')
server.set_debuglevel(True) #commenting this out, changing to False will make the script give NO output at all upon successful completion
server.starttls()
server.login(username,password)
server.sendmail(msg['From'], [msg['To']], msg.as_string())

server.quit() #bye bye

except:
print ('Email NOT sent to %s successfully. %s ERR: %s %s %s ', str(toaddr), 'tete', str(sys.exc_info()[0]), str(sys.exc_info()[1]), str(sys.exc_info()[2]) )
#just in case

Sample Output

send: 'ehlo [0.0.0.0]\r\n'
reply: '250-mx.google.com at your service, [67.248.53.114]\r\n'
reply: '250-SIZE 35882577\r\n'
reply: '250-8BITMIME\r\n'
reply: '250-STARTTLS\r\n'
reply: '250 ENHANCEDSTATUSCODES\r\n'
reply: retcode (250); Msg: mx.google.com at your service, [67.248.53.114]
SIZE 35882577
8BITMIME
STARTTLS
ENHANCEDSTATUSCODES
send: 'STARTTLS\r\n'
reply: '220 2.0.0 Ready to start TLS\r\n'
reply: retcode (220); Msg: 2.0.0 Ready to start TLS
send: 'ehlo [0.0.0.0]\r\n'
reply: '250-mx.google.com at your service, [67.248.53.114]\r\n'
reply: '250-SIZE 35882577\r\n'
reply: '250-8BITMIME\r\n'
reply: '250-AUTH LOGIN PLAIN XOAUTH\r\n'
reply: '250 ENHANCEDSTATUSCODES\r\n'
reply: retcode (250); Msg: mx.google.com at your service, [67.248.53.114]
SIZE 35882577
8BITMIME
AUTH LOGIN PLAIN XOAUTH
ENHANCEDSTATUSCODES

reply: '235 2.7.0 Accepted\r\n'
reply: retcode (235); Msg: 2.7.0 Accepted
send: 'mail FROM: size=878\r\n'
reply: '250 2.1.0 OK bo5sm543779qab.1\r\n'
reply: retcode (250); Msg: 2.1.0 OK bo5sm543779qab.1
send: 'rcpt TO:\r\n'
reply: '250 2.1.5 OK bo5sm543779qab.1\r\n'
reply: retcode (250); Msg: 2.1.5 OK bo5sm543779qab.1
send: 'data\r\n'
reply: '354 Go ahead bo5sm543779qab.1\r\n'
reply: retcode (354); Msg: Go ahead bo5sm543779qab.1
data: (354, 'Go ahead bo5sm543779qab.1')
send: 'Content-Type: multipart/mixed; boundary="===============0988883067=="\r\nMIME-Version: 1.0\r\nFrom: Some Guy \r\nTo: emailpeep@whatever.com\r\nSubject: This is for sexy email su
bjektz!\r\nReply-To: Some Guy \r\n\r\nThis is a multi-part message in MIME format.\r\n\r\n--===============0988883067==\r\nContent-Type: multipart/alternative; boundary="===============13
70445963=="\r\nMIME-Version: 1.0\r\n\r\n--===============1370445963==\r\nContent-Type: text/plain; charset="us-ascii"\r\nMIME-Version: 1.0\r\nContent-Transfer-Encoding: 7bit\r\n\r\nThis is my message body in HTML\r
\n\t\t\t\tYup. Yup. Yup.\r\n\t\t\t\tDone!\r\n--===============1370445963==\r\nContent-Type: text/html; charset="us-ascii"\r\nMIME-Version: 1.0\r\nContent-Transfer-Encoding: 7bit\r\n\r\nThis is my message body in HT
ML
\r\n\t\t\t\tYup. Yup. Yup.
\r\n\t\t\t\tDone!\r\n--===============1370445963==--\r\n--===============0988883067==--\r\n.\r\n'
reply: '250 2.0.0 OK 1343338799 bo5sm543779qab.1\r\n'
reply: retcode (250); Msg: 2.0.0 OK 1343338799 bo5sm543779qab.1
data: (250, '2.0.0 OK 1343338799 bo5sm543779qab.1')
send: 'quit\r\n'
reply: '221 2.0.0 closing connection bo5sm543779qab.1\r\n'
reply: retcode (221); Msg: 2.0.0 closing connection bo5sm543779qab.1

The post Python Snippet: Sending HTML email with an attachment via Google Apps SMTP (or Gmail) appeared first on Ryan Robitaille.