daily pastebin goal
58%
SHARE
TWEET

Tableau Extract from CSV

staniTo Oct 22nd, 2014 (edited) 4,906 Never
Upgrade to PRO!
ENDING IN00days00hours00mins00secs
  1. import dataextract as tde
  2. #module os - to delete files when we need to
  3. import csv,os,datetime
  4. import locale
  5. import os.path
  6. import time
  7. locale.setlocale( locale.LC_ALL, 'English_United States.1252' )
  8.  
  9. #Step 1: Create the extract file
  10.  
  11. # if running the script multiple times
  12. # Path - name of the extract
  13. PATH='SuperStoreCSVExtract.tde'
  14.  
  15. #if the file exists and is readable then it creates new file
  16. if os.path.isfile(PATH) and os.access(PATH, os.R_OK):
  17.    # File exists and is readable
  18.     os.remove('SuperStoreCSVExtract.tde')
  19.     tdefile = tde.Extract('SuperStoreCSVExtract.tde')
  20.  
  21. else:
  22.    # Either file is missing or is not readable - create new file
  23.     tdefile = tde.Extract('SuperStoreCSVExtract.tde')
  24.  
  25.  
  26. # open csv file
  27. csvReader = csv.reader(open('SuperStoreCSV.csv','rb'), delimiter=';',quotechar='"')
  28.  
  29. #Step 2: Create the tabledef
  30. tableDef = tde.TableDefinition()
  31. tableDef.addColumn('Row ID', tde.Type.CHAR_STRING) #0 column in the CSV file
  32. tableDef.addColumn('Order priority', tde.Type.CHAR_STRING) #1 column in the CSV file
  33. tableDef.addColumn('Discount', tde.Type.DOUBLE) #2 ...
  34. tableDef.addColumn('Unit Price', tde.Type.DOUBLE) #3
  35. tableDef.addColumn('Product Category', tde.Type.CHAR_STRING) #9
  36. tableDef.addColumn('Order Date', tde.Type.DATE) #18
  37. tableDef.addColumn('Sales', tde.Type.DOUBLE) #22
  38. tableDef.addColumn('Profit', tde.Type.DOUBLE) #20
  39. tableDef.addColumn('Postal Code', tde.Type.INTEGER) #17
  40. tableDef.addColumn('Customer Name', tde.Type.CHAR_STRING) #6
  41.  
  42. #func returns the proper float number
  43. def floatNum( num ):
  44.    return float(num.replace(",", "."))
  45.  
  46.  
  47. #Step 3: Create the table in the image of the tableDef
  48. table = tdefile.addTable('Extract',tableDef)
  49.  
  50. #Step 4: Loop through the csv, grab all the data, put it into rows
  51. #and insert the rows in the table
  52. newrow = tde.Row(tableDef)
  53. #going to the next row (skip the headers, take the data only
  54. csvReader.next()
  55. for line in csvReader:
  56.     #           col numer(extract), col number(CSV)
  57.     newrow.setCharString(0, str(line[0]))
  58.     newrow.setCharString(1, str(line[1]))
  59.     newrow.setDouble(2, floatNum((line[2])))
  60.     newrow.setDouble(3, floatNum((line[3])))
  61.     newrow.setCharString(4, str(line[9]))
  62.     # save date in a variable (date)
  63.     date = datetime.datetime.strptime(line[18], '%d.%m.%Y')
  64.     newrow.setDate(5, date.year, date.month, date.day)
  65.     newrow.setDouble(6, floatNum((line[22])))
  66.     newrow.setDouble(7, floatNum((line[20])))
  67.     newrow.setInteger(8, int(line[17]))
  68.     newrow.setCharString(9, str(line[6]))
  69.     table.insert(newrow)
  70.  
  71. #Step 5: Close the tde
  72. tdefile.close()
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top