def questionsUpload():
survey = request.args.get('survey')
company = request.args.get('company')
#file = request.files['file']
file = 'C:\\Users\\bhara\\Desktop\\workspace\\questions.xlsx'
df = pd.read_excel(file)
#df.pop('category_id')
list = df['category_name'].unique()
cn = 1
print(df.columns)
for i in list:
df.loc[df['category_name'] == i, 'category_id'] = cn
cn += 1
df.category_id = df.category_id.astype(int)
#print(df)
#Getting timestamp
backup = bl.getTimeStamp()
#code pending for taking backup of this json
jFile = 'C:/Users/bhara/Desktop/workspace/db.json'
jsonFile = 'C:/Users/bhara/Desktop/workspace/db_{}.json'.format(backup)
copyfile(jFile, jsonFile)
#Parsing and divding into sector chunks of data from excel
df_R1 = df[df['sector'] == 'R1']
df_R2 = df[df['sector'] == 'R2']
df_R3 = df[df['sector'] == 'R3']
df_R4 = df[df['sector'] == 'R4']
#initialize Mongo DB details or can be taken from a config file in future
host = 'localhost'
database = 'BKBASE'
collection = 'questions'
user = '******'
pwd = 'Blackpearl'
#update the db.json file with related info
bl.parseDF(df_R1, df_R2, df_R3, df_R4, jsonFile, survey, company)
#push json to mongo
bl.pushMongoDB(host, database, collection, jsonFile, user, pwd, survey,
company)
#function to clean the workspace and close all handlers and files
#bl.cleanup()
print(df)
return 'Upload Done -- replace this with web page'
def usersUpload():
survey = request.args.get('survey')
company = request.args.get('company')
file = request.files['file']
#file = xlrd.open_workbook("/apps/surveyapp_python/user_details.xlsx")
df = pd.read_excel(file)
# Getting timestamp
backup = bl.getTimeStamp()
# code pending for taking backup of this json
jFile = './users.json'
jsonFile = './users_{}.json'.format(backup)
copyfile(jFile, jsonFile)
lFile = './login.json'
ljsonFile = './login_{}.json'.format(backup)
copyfile(lFile, ljsonFile)
# Parsing and divding into sector chunks of data from excel
#df_username = df[df['username']]
# initialize Mongo DB details or can be taken from a config file in future
host = 'localhost'
database = 'Surveyapp'
collection = 'userdetails'
user = '******'
pwd = 'Blackpearl'
# update the db.json file with related info
bl.parseDFusers(df, jsonFile, ljsonFile, survey, company)
# push json to mongo
bl.pushMongoDB(host, database, collection, jsonFile, user, pwd, survey,
company)
# function to clean the workspace and close all handlers and files
# bl.cleanup()
return 'Upload Done -- replace this with web page'
def questionsUpload():
survey = request.args.get('survey')
company = request.args.get('company')
file = request.files['file']
#file = xlrd.open_workbook("C:/Users/headway/PycharmProjects/untitled/questions.xlsx")
df = pd.read_excel(file)
n = 1
df1, n = bl.parse(df, 'R1', 'Physical', n)
df2, n = bl.parse(df, 'R1', 'Organizational', n)
df3, n = bl.parse(df, 'R1', 'Technical', n)
df4, n = bl.parse(df, 'R2', 'Physical', n)
df5, n = bl.parse(df, 'R2', 'Organizational', n)
df6, n = bl.parse(df, 'R2', 'Technical', n)
df7, n = bl.parse(df, 'R3', 'Physical', n)
df8, n = bl.parse(df, 'R3', 'Organizational', n)
df9, n = bl.parse(df, 'R3', 'Technical', n)
df10, n = bl.parse(df, 'R4', 'Physical', n)
df11, n = bl.parse(df, 'R4', 'Organizational', n)
df12, n = bl.parse(df, 'R4', 'Technical', n)
df = pd.concat(
[df1, df2, df3, df4, df5, df6, df7, df8, df9, df10, df11, df12])
print(df1[['sector', 'subsector', 'category_id', 'category_name', 'qid']])
print(df2[['sector', 'subsector', 'category_id', 'category_name', 'qid']])
print(df3[['sector', 'subsector', 'category_id', 'category_name', 'qid']])
print(df4[['sector', 'subsector', 'category_id', 'category_name', 'qid']])
print(df5[['sector', 'subsector', 'category_id', 'category_name', 'qid']])
print(df6[['sector', 'subsector', 'category_id', 'category_name', 'qid']])
print(df7[['sector', 'subsector', 'category_id', 'category_name', 'qid']])
print(df8[['sector', 'subsector', 'category_id', 'category_name', 'qid']])
print(df9[['sector', 'subsector', 'category_id', 'category_name', 'qid']])
print(df10[['sector', 'subsector', 'category_id', 'category_name', 'qid']])
print(df11[['sector', 'subsector', 'category_id', 'category_name', 'qid']])
print(df12[['sector', 'subsector', 'category_id', 'category_name', 'qid']])
print("MAINFRAME")
print(df[['sector', 'subsector', 'category_name', 'category_id', 'qid']])
fileName = 'dataframe_BK.xlsx'
wb = Workbook()
sheet1 = wb.add_sheet('Sheet 1')
wb.save(fileName)
writer = ExcelWriter(fileName)
df.to_excel(writer, 'Sheet1', index=False)
writer.save()
#df.pop('category_id')
#print(df)
#Getting timestamp
backup = bl.getTimeStamp()
#code pending for taking backup of this json
jFile = './db.json'
jsonFile = './db_{}.json'.format(backup)
copyfile(jFile, jsonFile)
#Parsing and divding into sector chunks of data from excel
df_R1 = df[df['sector'] == 'R1']
df_R2 = df[df['sector'] == 'R2']
df_R3 = df[df['sector'] == 'R3']
df_R4 = df[df['sector'] == 'R4']
#initialize Mongo DB details or can be taken from a config file in future
host = 'localhost'
database = 'Surveyapp'
collection = 'questions'
user = '******'
pwd = 'Blackpearl'
#update the db.json file with related info
bl.parseDF(df_R1, df_R2, df_R3, df_R4, jsonFile, survey, company)
#push json to mongo
bl.pushMongoDB(host, database, collection, jsonFile, user, pwd, survey,
company)
#function to clean the workspace and close all handlers and files
#bl.cleanup()
return 'Upload Done -- replace this with web page'
copyfile(jFile, jsonFile)
# Parsing excel data
#xl = pd.read_excel(path+'/'+filename)
#xl = pd.read_excel(filename)
#Parsing and divding into sector chunks of data from excel
df_R1 = xl[xl['sector'] == 'R1']
df_R2 = xl[xl['sector'] == 'R2']
df_R3 = xl[xl['sector'] == 'R3']
df_R4 = xl[xl['sector'] == 'R4']
print(df_R4)
#initialize Mongo DB details or can be taken from a config file in future
host = 'localhost'
database = 'BKBASE'
collection = 'questions'
user = '******'
pwd = 'Blackpearl'
#update the db.json file with related info
bl.parseDF(df_R1, df_R2, df_R3, df_R4, jsonFile, survey, company)
#push json to mongo
bl.pushMongoDB(host, database, collection, jsonFile, user, pwd, survey,
company)
#function to clean the workspace and close all handlers and files
#bl.cleanup()