Getting row counts in all excel file by sheet names

The scenario:
You have a number of excel files (xls). In each excel file there various sheets. Each sheet has a header and a number of lines following it. You want an output of the format (csv):
filename1, sheet name 1, row count , sheet name 2, row count, sheet name 3, row count
filename2, sheet name 1, row count , sheet name 2, row count, sheet name 3, row count

Well here is a script that does just that!
You will need python and dexutils both downloadable from : google code
make a folder (i will call it SCRIPT) and make a file getsheetrowcounts.py containing the following data :

import dex
from dex import xlrd

decrementoneforheader = True

files = dex.glob('./input/*.xls')

book = {}  # book['filename']=[]

for file in files:
    wb = xlrd.open_workbook(file)
    file = dex.GetFilenameWithoutExt(file)
    sheets = wb.sheet_names()
   
   for sheetname in sheets:
        sh = wb.sheet_by_name(sheetname)
        rowcount = sh.nrows
       
       try:
            book[file]
        except:
            book[file]=[]
        book[file].append(sheetname)
        if (decrementoneforheader):
            rowcount = rowcount-1
        book[file].append(str(rowcount))
   
  
# now we have the data
outlines = []
for bookname in book.keys():   
   line = bookname
   rows = book[bookname]
    for column in rows:
        line = line + "," + column
   
   outlines.append(line)
   
dex.writelines('output.csv',outlines)

Next make a folder "input" inside of SCRIPT and place all the excel files in this folder. Finally run getsheetrowcounts.py and you will get the desired output.csv

DEXUTILS: A python utility library I wish I had in EVERY language

I have been working with python for 2 years now. It is by far the most general purpose language and productive programming language I have experienced to date.

The first thing that any professional programmer should get for python is an AWESOME IDE. I HIGHLY recommend wingware's Python IDE. It truly is worth the money and comes for all flavors of operating systems. For those using windows and wanting something free software I recommend using mmm-experts python scripters. Both have an amazing feature of clicking (while holding the ctrl key) to navigate to a function. So I basically have a folder put in the Environment variable PYTHONPATH and in that I place a folder by the name dex. Now in my code I do import dex and I can type function name. If any is not working I can modify it there and then in my script.

I do a lot of general purpose programming and over the time dex (aka dexutils ) has expanded exponentially. So without further ado I present you dexutils.

It comes with a simple distutils installer (for windows users an installer is also always present

For quick programming hacks I dont want to waste time opening / reading and then closing a file. So I made a quick function about it:

import dex
lines = dex.readlines('filename')

For saving a lines to a file you can do :

import dex
lines = ['1','2','3','4','5']
dex.writelines('temp.txt',lines)


This makes using python faster than writing a macro in vba anyday!
There a LOTS of functions in it. Some quite advanced. Almost all of them come with __doc__ strings so if you are using wingware or pyscripter you get cool help information.
Here a a couple more:
Check if a string can be made an integer:
from dex import stringutils
print stringutils.IsInt('123')

Splitting a CSV string: (will print ['1', '2', '3', 'stuff', '5'])
from dex import stringutils
print stringutils.SplitCSV('1,2,3,"stuff",5')

Print the current scripts filename :
from dex import stringutils
print stringutils.GetFilename(__file__)

How about reversing a string : '123' -> '321'
from dex import stringutils
print stringutils.ReverseStr('123')

There are various modules e.g stringutils , fileutils, arrutils. Just read the docs :) I am sure you will find it helpful. Also any cool library I found highly useful I have made a part of the code. e.g. xlrd (more on that later).

The link (again) : dexutils

Enjoy!

Using SQL Server Analysis Services with Sybase ASE 15

Sybase 15 ships with ADO.net and OLEdb drivers. These come as a part of the Sybase PC client download. Here I describe how you can use Sybase with SQL Server analysis services (SSAS). The following is the software I am using :

• sybase_pcclient_ASE_15.0.2.zip
  
• SQL Server 2008 Analysis services

While installing the PCClient making sure that you select the data access modules. (These were checked by default in my installation but it never hurts to check). Now start the Data Source Administrator:


Click on Add, Fill out the details, And test to see it is working:



Finally click ok and then close the application.

Also (not neccessary but I like to do):
{

• check cursors
• Trasactions tab: Server initiated transactions .... as well as ..... XA Protocol
• Finally set the database name in the connection (Sybase data source administrator) as well as in the Data source(BI Dev studio)

}

Congrats. The difficult part is done :)

Now in BIDS add a new datasource. Click on new to add a data connection. In the connection manager select Sybase OLEDB as your provider as shown:


Fill out the form particularly the Server name as you did in the data source administrator (OSS in my case).



Click on Test connection and you are all set.

Enjoy!

I will try and post about the performance I am getting from my cube some time later on.

Update: For SSIS in OLE DB data source you will need to set "AlwaysUseDefaulCodePage" Custom Property of the OLE DB Source control to True. You can read more here . Also use SQL Queries for data retrieval.

Update2:For SSIS running under SQL Server agent (for scheduling) in case it fails due to "Could not obtain connection" or "Invalid port number" it may be because of one of following resons:

• the drivers may not be accesible to the user that is used to run the SQL Server agent (the service property : log on as). Login as that user. Reinstall the client tools (do a full install) of sybase ASE.
• The package has ProtectionLevel property (SSIS property) as "EncryptAllWithUserKey". This makes connections unavailable to anyone but the developer. Use "EncryptSensitiveWithPassword" which makes it accessible to any domain users that have the password you will be asked to set.
• and Finally: Run all packages as sa (SQL Server agent property). Running them as other users is a complicated process that you can check out AFTER your package has started to work in schedule :)
  

Enjoy!

Setting up an Oracle an PHP development environment

There are a number of tutorials on setting up php and mysql. And there are awesome projects e.g. WAMP and XAMPP to get you started quickly.

I use Nusphere's PHP ed as my programming tool. It is simply the best php environment (at least about an year back ... when I researched into the options available).

There is also an article explaining the process on OTN:
http://www.oracle.com/technology/pub/notes/technote_php_instant.html

However as per my observation I did not need to replace the php_oci8.dll . In fact doing so cause my php code to break.

Heres what I did to get it running:

• Install Nusphere's PHP ed.
  
• Modify php.ini (c:\Program Files\NuSphere\PhpED\php5\php.ini) that is formed uncommenting the line : extension=php_oci8.dll
• Extract the instant client (I tried : instantclient-basiclite-win32-10.2.0.5.zip from http://www.oracle.com/technetwork/topics/winsoft-085727.html ) to c:\instantclient_10_2
• Put c:\instantclient_10_2 in your PATH
• If you are going to use TNSNAMES.ora you can put it in c:\instantclient_10_2 and add a system environment variable TNS_ADMIN and SQL_PATH pointing both to c:\instantclient_10_2

That is it. You are all set.

Say you have an entry in tnsnames.ora as :


RX =
  (DESCRIPTION = (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 150.236.167.30)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = 69333)
    )
  )



Just put the IP .. e.g HOST=150.236.161.1
You can now do a simple connect via:

$conn = @OCILogon($user,$password , "rx") or die ("Database Connection Error in file...");

WPF / Winforms Mapping control

I have been looking for a WPF mapping control since as long as I have learnt about WPF.

Finally an awesome find:

http://greatmaps.codeplex.com/

It has multiple data source selections including google maps and open street map so you will not be left hanging. It support offline caching of map images via sql lite. This is just WAY too cool.

It supports custom markers as well.

BTW: I found this right after I have decided that WPF is just not ready for prime time yet :) at least not for me........... I love it and a I love blend .... but winforms just performs better on my client systems. At least for now. Also, the programmer made a winform version of the control as well :)

Update: Feb8 2010
I moved to WPF because it is the future. I have found it to be EXTREMELY fun to work with ( I love a pretty interface ). I have been using this control for 6 months now and it is HIGHLY stable ... and easily customizable too.
Also here is another way to do it (but I wouldn't go there since I dislike interop) :
http://code.google.com/apis/maps/articles/flashmapinwpf.html

Also check out :

http://offlinemaps.codeplex.com/

It allows download of google map tiles amongst other cool stuff.

As usual Enjoy!