Parse report into SQL db?

[GoogleCodeExporter]

Hi, 

This isn't an issue inasmuch as a general question.

I'm looking to (as the subject implies) take the data from the downloaded
report and load that into an SQL database.

I was just about to start writing a perl script to do the legwork but
figured it'd be worth inquiring to see if anyone has already written such a
script, or if there's a better way to do it than via perl script.

Any insight would be great.

If no such script or tool exists I'll be sure to post a link to my script
once complete.

Original issue reported on code.google.com by admiral....@gmail.com on 23 Mar 2010 at 9:40

[GoogleCodeExporter]

I don't know of any scripts.

Original comment by ki...@thecave.com on 24 Mar 2010 at 1:30

• Added labels: Priority-Low, Type-Other
• Removed labels: Priority-Medium, Type-Defect

[GoogleCodeExporter]

Check out my script pyTunesConnect, it was designed to use this script to 
download the reports and will then dump them into a SQL database.  It also has 
a web based view layer built in that has some cool java charts. 
http://code.google.com/p/pytunesconnect/

Original comment by jonathan...@gmail.com on 6 Oct 2010 at 4:11

[GoogleCodeExporter]

I have modified the original script to save the ITC data to a MySQL database 
instead of saving the file. Here's how:

add this with the other imports:
  import MySQLdb

then look for this in your script (towards the bottom)
  downloadFile = open(filename, 'w')
  downloadFile.write(filebuffer)
  downloadFile.close()

and replace it with this:

# we do not want to save the file. We save the data to our DB instead
                #downloadFile = open(filename, 'w')
                #downloadFile.write(filebuffer)
                #downloadFile.close()

                #connect to database
                #ip or hostname of the mysql database server
                dbhost = "dbconnection"
                #username to use when connecting to db
                dbuser = "myusername"
                #password to use when connecting to db
                dbpass = "mypassword"
                #dbname to connect to
                dbname = "mydbname"
                #dbtable to store the data
                dbtable = "mytablename"    
                db = MySQLdb.connect(host=dbhost, user=dbuser, passwd=dbpass, db=dbname)
                cursor = db.cursor()

                #open the TSV file downloaded by appdailysales.py
                report = filebuffer

                counter = 0

                if options.verbose == True:
                    print "Reading ITC file and saving to DB..."

                for line in re.split('\n',report):
                    if(counter > 0 and line != ''): #skip the first line of headers. Also, make sure the line contains data
                        #while there are still lines to be read in the file split the line and pull out the values
                        split = re.split('\t',line)
                        vendorid = split[2]
                        title = split[4]
                        title = title.replace("'",'')
                        appversion = split[5]
                        producttype = split[6]
                        units = split[7]
                        royaltyprice = split[8]
                        begindate = split[9]
                        beginSplit = re.split('/',begindate)
                        begindate = beginSplit[2]+'-'+beginSplit[0]+'-'+beginSplit[1]
                        enddate = split[10]
                        endSplit = re.split('/',enddate)
                        enddate = endSplit[2]+'-'+endSplit[0]+'-'+endSplit[1]
                        customercurrency = split[11]
                        countrycode = split[12]
                        royaltycurrency = split[13]
                        appleid = split[14]
                        customerprice = split[15]
                        promocode = split[16]

                        #build the insert query
                        query = "INSERT INTO " + dbtable + " (vendorid, title, appversion, type, units, royaltyprice, begindate, enddate, customercurrency, countrycode, royaltycurrency, appleid, customerprice, promocode)"+" VALUES ('"+vendorid+"', '"+title+"', '"+appversion+"', '"+producttype+"', '"+units+"', '"+royaltyprice+"', '"+begindate+"', '"+enddate+"', '"+customercurrency+"', '"+countrycode+"', '"+royaltycurrency+"', '"+appleid+"', '"+customerprice+"','"+promocode+"')"
                        cursor.execute(query)
                        db.commit()

                    counter = counter + 1

                if options.verbose == True:
                    print "\nDaily sales imported to DB successfully!"
                    print str(counter-2)+" new records saved."

Make sure you enter the correct database credentials.

Original comment by kjant...@gmail.com on 22 Oct 2010 at 5:15

[GoogleCodeExporter]

I've noticed that the script above brings up some errors sometimes as the last 
section the date (year) has lots of spaces at the end thus creating a MySQL 
error

------------------------------------------
To fix this replace
------------------------------------------
begindate = split[9]
beginSplit = re.split('/',begindate)
begindate = beginSplit[2]+'-'+beginSplit[0]+'-'+beginSplit[1]
enddate = split[10]
endSplit = re.split('/',enddate)
enddate = endSplit[2]+'-'+endSplit[0]+'-'+endSplit[1]
------------------------------------------
With this
------------------------------------------
begindate = split[9]
beginSplit = re.split('/',begindate)
year = beginSplit[2]
month = beginSplit[0]
day = beginSplit[1]
begindate = year[:4]+'-'+month+'-'+day
begindate = split[9]
enddate = split[10]
endSplit = re.split('/',enddate)
year = endSplit[2]
month = endSplit[0]
day =endSplit[1]
enddate = year[:4]+'-'+month+'-'+day
------------------------------------------

Original comment by ad...@vmlweb.co.uk on 27 Oct 2010 at 12:39

[GoogleCodeExporter]

Hey,

At RPNi we've done exactly as the OP has described with a reporter.

We use a launcher.py (set to run on a cron job) to launch AppDailySales.py (for 
dates a week back for any dates without reports - sometimes reports don't come 
in at the same time), drop its output to a text file, search that text file for 
what file it drops into (this was before they added the format option), then 
run a reporter.py on the dropped file. The reporter is pretty much what you'd 
expect - a huge mess of code that does parsing up to the neck and then drops 
formatted data into a SQL db using MySQLdb (which is a bit of a pain to get 
working right, but once works, works well).

The down side to writing a parser is that Apple is fickle and changes things 
from time to time. Also, it's kind of tricky to get a parser written such that 
you take into account issues like foreign currencies (since at that point you 
have to do estimates - Apple doesn't translate the foreign capital into your 
native currency until an actual payout, or something like that). It makes it 
kinda fun though to try and write a reporter that tracks from what countries 
things are being bought from though. Also, adjustments show up for Apps that 
get returned (and actually winds up costing you money because Apple retains 
their 30% regardless if the user returns it).

So, is it possible? Absolutely, but there are all sorts of issues. 
AppDailySales is just really one part of the equation - the other part would 
need to be the reporter as I've detailed... But of course, then you start 
venturing into the world of "should AppDailySales be managing an entire App 
built for App devs to handle the full range of App Store related sales tracking 
tasks?" which yeah... Not like we're paying AppDailySales.

Original comment by jonathan...@gmail.com on 3 Nov 2010 at 7:07

[GoogleCodeExporter]

Jonathan wrote:
> But of course, then you start venturing into the world of "should 
AppDailySales be managing 
> an entire App built for App devs to handle the full range of App Store 
related sales tracking 
> tasks?" which yeah... Not like we're paying AppDailySales.

I wish I had the time to do a full fledged app.  I started AppDailySales out of 
a personal need, to automatically download daily reports. This way I don't have 
to worry about missing a report when I'm away. But boy, could I use more 
reporting. And I would like to incorporate my App Store sales with my desktop 
sales so I can see a more complete picture for my company's sells.

Ah the life of an indie dev...never enough time to do everything I want to do.

-KIRBY

Original comment by kirbyt.w...@gmail.com on 3 Nov 2010 at 12:58