Advertisement
staniTo

Tableau Extract from CSV

Oct 22nd, 2014
5,385
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Python 2.57 KB | None | 0 0
  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()
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement