Sunday, September 29, 2013

Tableau : Convert ESRI shapes into Tableau Format

Requirement is to convert data in ESRI Map shapes into Tableau format.

Tools Required
  1. Quantum GIS -
  2. Python 
  3. Download the below Python Code -
  4. import csv
    import re
    import sys
    #def convertESRI2TableauFormat( p_inputfile, p_outputfile):
    l_outfileName = 'c:/ESRI_suburb_tab_format.csv'  # make changes here..
    l_ESRIFileName ='c:/ESRI_suburb.csv'             # make changes here..
    outfile = open( l_outfileName, 'w', newline='')
    csvWriter = csv.writer( outfile, delimiter=',', quotechar='"', quoting=csv.QUOTE_MINIMAL)
    with open( l_ESRIFileName, 'r') as csvfile:
     csvR = csv.reader( csvfile, delimiter=',', quotechar='"')
     for row in csvR:
        rdx += 1           
        rowValues = []
        rowValues.append(row[1]);# ssc_code
        rowValues.append(row[2]);# ssc_name
        rowValues.append(row[4]);# state_code
        rowValues.append(row[5]);# confidence
        rowValues.append(row[6].strip());# AREA_SQKM
        rowValues.append('');# place holder for lat
        rowValues.append('');# place holder for long
        rowValues.append('');# place holder for polygon
        rowValues.append('');# place holder for point
        if ( rdx==1): # first row add header col names
           csvWriter.writerow( rowValues);
           for col in row:
               ci += 1
               if (ci==1): # first col has the polygon values.
                   polygonArr = []
                   polygonArr = re.findall('(\((?:-?\d+\.\d+\s-?\d+\.\d+,?)+\))', col)
                   polyx =0
                   for px in polygonArr:
                       polyx += 1
                       LatLonStrArr = []
                       LatLonStrArr = re.findall('(-?\d+\.\d+\s-?\d+\.\d+)', px)
                       if( len( LatLonStrArr) != 0 ):
                           pointx  = 0
                           pointxx = 0
                           for lx in LatLonStrArr:
                               pointx += 1
                               resArr = re.findall( '(-?\d+\.\d+)', lx)
                               #print('>>>> lx='+lx + "/"+ str(len(resArr)))
                               rowValues[5] = resArr[1] # lat
                               rowValues[6] = resArr[0] #long
                               rowValues[7] = polyx
                               if( (pointx ==1) | (pointx == len(LatLonStrArr))| (len(LatLonStrArr)<=10)| (pointx % 10 == 0) ):
                                  pointxx += 1
                                  rowValues[8] = pointxx
                                  csvWriter.writerow( rowValues);
        #print('ROW========================='+ str(rdx))
        #for rx in rowValues:
        #   print('val=' + str(rx))
        #if (rdx >=100):
For the sake of this exercise I downloaded the census data from ABS website ( free registration required).

Step to convert ESRI data into Tableau Format

  1. Download your ESRI data. and it will look like this 
  2. Open Quantum GIS
  3. From the top menu -> new project
  4. From the top menu -> Layer -> "Add Vector Layer" ( in this select the .shp file downloaded in step 1) 
  5. Now you will be able to see the From maps and Shapes from the <filename>.shp file
  6. Right-click and select "save as"
  7. Make sure you save as with the Layer Option as GEOMETRY=AS_WKT 
  8. Now you have exported the ESRI  shp file into a CSV
  9. Use the Python code from above, and make the changes to the 2 variables 
    1. l_outfileName
    2. l_ESRIFileName
  10. Run the above py code- you will get the CSV file readable by Tableau
  11. If you dont know how to use that CSV in Tableau - Look into this Blog - Tableau Maps - Custom shapes / polygon - Basics


Jura said...

Hi Venkat,

I follow your step by step but the outfile generated a empty csv file.

Jura said...

Thank you for sharing this kind of tool with us...


Unknown said...

Q1) R u getting an empty file from python script Or from the GIS tol(step-7)?

Q2) Please give me more details to help u.

tanooj said...

Hi Venkatachalam Veerabagu Subramanian ,Thank for you great support,i am looking for the same solution , but i am facing problem with python script.Its throwing some errors while executing.
Please let me out of this scenario,also i can attach you qis generated csv file

Unknown said...

Sorry, i was away from my blog for a while. Let me know if you still need help.

Unknown said...

The code you posted on the github page doesn't work and is different from the one you have on your webpage. when using your script this error is consistently displayed.
“OverflowError: Python int too large to convert to C long”

Lalita said...

Hi Venkat,

This seems like a cool tool, But I get error whenever I try to run the code. Would appreciate if you could help.

Unknown said...

Im been a while and i dont have a tableau instance with me to test it.. anyway if you tell me what kind of error you get, I will see what I can do for you.

Unknown said...

I read your blog.I thought it was great.. Hope you have a great day. God bless.


likitha said...

I like your blog, I read this blog please update more content on hacking,
Tableau Online Training

siva sreedhar said...

I like your post very much. It is very useful for my research. I hope you can share more info about this. Keep posting
Workday Integration training

Workday Studio training
Tableau online training
Tableau Server training
Tableau Advanced training
ETL Testing Training