Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- import dataextract as tde
- #module os - to delete files when we need to
- import csv,os,datetime
- import locale
- import os.path
- import time
- locale.setlocale( locale.LC_ALL, 'English_United States.1252' )
- #Step 1: Create the extract file
- # if running the script multiple times
- # Path - name of the extract
- PATH='SuperStoreCSVExtract.tde'
- #if the file exists and is readable then it creates new file
- if os.path.isfile(PATH) and os.access(PATH, os.R_OK):
- # File exists and is readable
- os.remove('SuperStoreCSVExtract.tde')
- tdefile = tde.Extract('SuperStoreCSVExtract.tde')
- else:
- # Either file is missing or is not readable - create new file
- tdefile = tde.Extract('SuperStoreCSVExtract.tde')
- # open csv file
- csvReader = csv.reader(open('SuperStoreCSV.csv','rb'), delimiter=';',quotechar='"')
- #Step 2: Create the tabledef
- tableDef = tde.TableDefinition()
- tableDef.addColumn('Row ID', tde.Type.CHAR_STRING) #0 column in the CSV file
- tableDef.addColumn('Order priority', tde.Type.CHAR_STRING) #1 column in the CSV file
- tableDef.addColumn('Discount', tde.Type.DOUBLE) #2 ...
- tableDef.addColumn('Unit Price', tde.Type.DOUBLE) #3
- tableDef.addColumn('Product Category', tde.Type.CHAR_STRING) #9
- tableDef.addColumn('Order Date', tde.Type.DATE) #18
- tableDef.addColumn('Sales', tde.Type.DOUBLE) #22
- tableDef.addColumn('Profit', tde.Type.DOUBLE) #20
- tableDef.addColumn('Postal Code', tde.Type.INTEGER) #17
- tableDef.addColumn('Customer Name', tde.Type.CHAR_STRING) #6
- #func returns the proper float number
- def floatNum( num ):
- return float(num.replace(",", "."))
- #Step 3: Create the table in the image of the tableDef
- table = tdefile.addTable('Extract',tableDef)
- #Step 4: Loop through the csv, grab all the data, put it into rows
- #and insert the rows in the table
- newrow = tde.Row(tableDef)
- #going to the next row (skip the headers, take the data only
- csvReader.next()
- for line in csvReader:
- # col numer(extract), col number(CSV)
- newrow.setCharString(0, str(line[0]))
- newrow.setCharString(1, str(line[1]))
- newrow.setDouble(2, floatNum((line[2])))
- newrow.setDouble(3, floatNum((line[3])))
- newrow.setCharString(4, str(line[9]))
- # save date in a variable (date)
- date = datetime.datetime.strptime(line[18], '%d.%m.%Y')
- newrow.setDate(5, date.year, date.month, date.day)
- newrow.setDouble(6, floatNum((line[22])))
- newrow.setDouble(7, floatNum((line[20])))
- newrow.setInteger(8, int(line[17]))
- newrow.setCharString(9, str(line[6]))
- table.insert(newrow)
- #Step 5: Close the tde
- tdefile.close()
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement