Beispiel #1
0
def import_athletes():
    upload_file = flask.request.files['members']
    file_path = "./tmp/bulk_members_upload.xlsx"
    upload_file.save(file_path)

    rows = p.get_records(file_name=file_path, start_row=1)

    # Retrieves header name for the excel file
    name_field = flask.request.form.get('name')
    last_name_field = flask.request.form.get('last_name')
    birth_date_field = flask.request.form.get('birth_date')
    birth_place_field = flask.request.form.get('birth_place')
    fiscal_code_field = flask.request.form.get('fiscal_code')
    address_field = flask.request.form.get('address')
    zip_code_field = flask.request.form.get('zip_code')
    city_field = flask.request.form.get('city')
    province_field = flask.request.form.get('province')
    gender_field = flask.request.form.get('gender')
    phone_field = flask.request.form.get('phone')
    email_field = flask.request.form.get('email')

    members = import_document(Member, rows, name_field, last_name_field,
                              birth_date_field, birth_place_field,
                              fiscal_code_field, address_field, zip_code_field,
                              city_field, province_field, gender_field,
                              phone_field, email_field)

    return flask.jsonify(members)
Beispiel #2
0
def process_vehicle_catalogs_related(request):
    model = request.GET.get('model')
    try:
        records = pyexcel.get_records(file_name=MODEL_OPTIONS[model]['path'])
    except Exception as err:
        return Response({'error': err.args},
                        status=status.HTTP_500_INTERNAL_SERVER_ERROR)
    count = 0
    for item in records:
        try:
            parent_object = MODEL_OPTIONS[model]['parent_model'].objects.get(
                id=item['parent_id'])
        except Brands.DoesNotExist:
            raise Exception('Brand not found')
        data = {
            'name': item['name'],
            MODEL_OPTIONS[model]['parent']: parent_object,
        }
        try:
            children = MODEL_OPTIONS[model]['model'](**data)
        except Exception as err:
            return Response({'error': err.args},
                            status=status.HTTP_500_INTERNAL_SERVER_ERROR)
        children.save()
        count += 1
    return Response({'message': f'{count} elements created'},
                    status=status.HTTP_201_CREATED)
Beispiel #3
0
def upload_employeedata_step5(request):
    req=request.POST
    records = pe.get_records(file_name=req['file_name'])

    chk=insertData(records,req,request.user.user_id,req['file_name'])
    stat=None
    if req['data_type']=='Employee':
        sqlqry="SELECT av_engine.av_trxn_company_employees_load_fn(%s, '%s', '%s','%s')"%(req['comp_id'],req['comp_name'],request.user.user_id,req['upload_id'])
        ret_msg=custom_query(sqlqry)
        if ret_msg=='SUCCESS':
            stat='success'
            AvLogDataUploads.objects.filter(data_upload_id=req['upload_id']).update(no_of_records=chk,status='5 to 6 Master Updates Done',upload_or_rollback='Upload to Rollback')
        else:
            AvLogDataUploads.objects.filter(data_upload_id=req['upload_id']).update(no_of_records=chk,status='5 to 6 Master Updates Error',upload_or_rollback='Upload to Rollback')
            stat='error'
    elif req['data_type']=='Expenses':
        sqlqry="SELECT av_engine.av_trxn_expenses_load_fn(%s, '%s', '%s','%s')"%(req['comp_id'],req['comp_name'],request.user.user_id,req['upload_id'])
        ret_msg=custom_query(sqlqry)
        if ret_msg=='SUCCESS':
            stat='success'
            AvLogDataUploads.objects.filter(data_upload_id=req['upload_id']).update(no_of_records=chk,status='5 to 6 Master Updates Done',upload_or_rollback='Upload to Rollback')
        else:
            stat='error'
            AvLogDataUploads.objects.filter(data_upload_id=req['upload_id']).update(no_of_records=chk,status='5 to 6 Master Updates Error',upload_or_rollback='Upload to Rollback')

    json_data = json.dumps( { 'status':stat,'message':'All records successfully inserted', 'chk':chk, 'file_name':req['file_name'], 'upload_id':req['upload_id']  } )
    utilb.logManage(request.user.company.id,request.user.company.short_name,datetime.now(),'Data Load','Records Inserted','Information','Records Inserted',request.user.user_id,req['upload_id'],'uploadData')
    return HttpResponse( json_data , content_type='application/json')
Beispiel #4
0
def exceltodictionary(dataf, dic={}):
    data = pe.get_records(file_name=dataf)
    for line in data:
        usrname = line['-1']
        dic[usrname]['Category'] = line['']
    return dic
    '''
Beispiel #5
0
def upload_employeedata_step3(request):
    req=request.POST
    records = pe.get_records(file_name=req['file_name'])

    stat=True
    if req['data_type']=='Employee':
        ls=[]
        for record in records:
            if record['Employee ID'] in ls:
                stat=False
            ls.append(record['Employee ID'])

    elif req['data_type']=='Expenses':
        ls=[]
        for record in records:
            if record['Report Line ID'] in ls:
                stat=False
            ls.append(record['Report Line ID'])


    if stat==True:
        status='success'
        msg='All records is unique'
        AvLogDataUploads.objects.filter(data_upload_id=req['upload_id']).update(status='4 of 6 File Formate Check')
        utilb.logManage(request.user.company.id,request.user.company.short_name,datetime.now(),'Data Load','Duplicate Records','Information','Duplicate Records checked',request.user.user_id,req['upload_id'],'uploadData')
    else:
        status='danger'
        msg='Some records are duplicate'
        AvLogDataUploads.objects.filter(data_upload_id=req['upload_id']).update(status='2 of 6 File Formate Check')
        utilb.logManage(request.user.company.id,request.user.company.short_name,datetime.now(),'Data Load','Duplicate Records','Information','Duplicate Records checked failed',request.user.user_id,req['upload_id'],'uploadData')

    json_data = json.dumps( { 'status':status,'message':msg, 'file_name':req['file_name'], 'upload_id':req['upload_id']  } )
    return HttpResponse( json_data , content_type='application/json')
Beispiel #6
0
def process_postcodes(request):
    count = 0
    static_path = os.path.join(BASE_DIR, 'static')
    try:
        state = States.objects.get(name='Ciudad de México')
    except States.DoesNotExist:
        state = States.objects.create(name='Ciudad de México')
    file_path = static_path + '/catalogs/address/ciudad_mexico.xlsx'
    records = pyexcel.get_records(file_name=file_path)
    for item in records:
        try:
            city = Cities.objects.get(name=item['D_mnpio'])
        except Cities.DoesNotExist:
            city = Cities.objects.create(name=item['D_mnpio'], state=state)
        try:
            colony = Colonies.objects.get(name=item['d_asenta'])
        except Colonies.DoesNotExist:
            colony = Colonies.objects.create(name=item['d_asenta'], city=city)
        try:
            postcode = Postcodes.objects.get(postcode=item['d_codigo'],
                                             colony=colony)
        except Postcodes.DoesNotExist:
            postcode = Postcodes.objects.create(postcode=item['d_codigo'],
                                                colony=colony)
        if postcode:
            count += 1
    return JsonResponse({'message': f'{count} objects created'})
Beispiel #7
0
def exceltodictionary(dataf, dic = {}):
	data = pe.get_records(file_name=dataf)
	for line in data:
		usrname = line['-1']
		dic[usrname]['Category'] = line['']
	return dic
	'''
Beispiel #8
0
def record_nets_donated_excel(request):
	if request.method=='POST' and request.FILES['excel_file']:
		myfile = request.FILES['excel_file']
		fs = FileSystemStorage()
		filename = fs.save(myfile.name, myfile)
		uploaded_file_url = fs.url(filename)

		missing_facilities = []

		records = pe.get_records(file_name=settings.BASE_DIR+uploaded_file_url)
		for record in records:
			beneficiary =record['beneficiary']
			invoice_no = record['invoice']
			warehouse = record['warehouse']
			nets_issued = record['nets']
			donor = record['donor']
			date_issued = record['date_issued']
			if Nets_donated.objects.filter(invoice_no=invoice_no).exists():
				continue

			issued = Nets_donated(beneficiary=beneficiary, invoice_no=invoice_no, nets_issued=nets_issued, donor_code=donor, date_issued=date_issued).save()


		messages.success(request, "Success! Nets donated records successfully saved.")
		return redirect('distribution:nets_donated')
	else:
		template = "distribution/excel-donation.html"
		context={}
		return render(request, template, context)
 def test_get_records_from_memory(self):
     data = [["X", "Y", "Z"], [1, 2, 3], [4, 5, 6]]
     content = pe.save_as(dest_file_type="xls", array=data)
     records = pe.get_records(
         file_content=content.getvalue(), file_type="xls"
     )
     eq_(records, [{"X": 1, "Y": 2, "Z": 3}, {"X": 4, "Y": 5, "Z": 6}])
Beispiel #10
0
 def test_get_records_from_file(self):
     data = [["X", "Y", "Z"], [1, 2, 3], [4, 5, 6]]
     sheet = pe.Sheet(data)
     testfile = "testfile.xls"
     sheet.save_as(testfile)
     result = pe.get_records(file_name=testfile)
     eq_(result, [{"X": 1, "Y": 2, "Z": 3}, {"X": 4, "Y": 5, "Z": 6}])
     os.unlink(testfile)
 def test_get_dict_from_file(self):
     data = [["X", "Y", "Z"], [1, 2, 3], [4, 5, 6]]
     sheet = pe.Sheet(data)
     testfile = "testfile.xls"
     sheet.save_as(testfile)
     result = pe.get_records(file_name=testfile)
     assert result == [{"X": 1, "Y": 2, "Z": 3}, {"X": 4, "Y": 5, "Z": 6}]
     os.unlink(testfile)
Beispiel #12
0
def upload_employeedata_step5(request):
    req = request.POST
    records = pe.get_records(file_name=req['file_name'])

    chk = insertData(records, req, request.user.user_id, req['file_name'])
    stat = None
    if req['data_type'] == 'Employee':
        sqlqry = "SELECT av_engine.av_trxn_company_employees_load_fn(%s, '%s', '%s','%s')" % (
            req['comp_id'], req['comp_name'], request.user.user_id,
            req['upload_id'])
        ret_msg = custom_query(sqlqry)
        if ret_msg == 'SUCCESS':
            stat = 'success'
            AvLogDataUploads.objects.filter(
                data_upload_id=req['upload_id']).update(
                    no_of_records=chk,
                    status='5 to 6 Master Updates Done',
                    upload_or_rollback='Upload to Rollback')
        else:
            AvLogDataUploads.objects.filter(
                data_upload_id=req['upload_id']).update(
                    no_of_records=chk,
                    status='5 to 6 Master Updates Error',
                    upload_or_rollback='Upload to Rollback')
            stat = 'error'
    elif req['data_type'] == 'Expenses':
        sqlqry = "SELECT av_engine.av_trxn_expenses_load_fn(%s, '%s', '%s','%s')" % (
            req['comp_id'], req['comp_name'], request.user.user_id,
            req['upload_id'])
        ret_msg = custom_query(sqlqry)
        if ret_msg == 'SUCCESS':
            stat = 'success'
            AvLogDataUploads.objects.filter(
                data_upload_id=req['upload_id']).update(
                    no_of_records=chk,
                    status='5 to 6 Master Updates Done',
                    upload_or_rollback='Upload to Rollback')
        else:
            stat = 'error'
            AvLogDataUploads.objects.filter(
                data_upload_id=req['upload_id']).update(
                    no_of_records=chk,
                    status='5 to 6 Master Updates Error',
                    upload_or_rollback='Upload to Rollback')

    json_data = json.dumps({
        'status': stat,
        'message': 'All records successfully inserted',
        'chk': chk,
        'file_name': req['file_name'],
        'upload_id': req['upload_id']
    })
    utilb.logManage(request.user.company.id, request.user.company.short_name,
                    datetime.now(), 'Data Load', 'Records Inserted',
                    'Information', 'Records Inserted', request.user.user_id,
                    req['upload_id'], 'uploadData')
    return HttpResponse(json_data, content_type='application/json')
Beispiel #13
0
def record_nets_distributed_excel(request):
	if request.method=='POST' and request.FILES['excel_file']:
		myfile = request.FILES['excel_file']
		fs = FileSystemStorage()
		filename = fs.save(myfile.name, myfile)
		uploaded_file_url = fs.url(filename)

		missing_facilities = []

		records = pe.get_records(file_name=settings.BASE_DIR+uploaded_file_url)
		for record in records:
			if Facilities.objects.filter(mfl_code=record['facility']).exists():
				facility = get_object_or_404(Facilities, mfl_code=record['facility'])
				dist_month = record['dist_month']
				dist_year = record['dist_year']
				anc_nets = int(check_for_blank_cells(record['anc_nets']))
				cwc_nets = int(check_for_blank_cells(record['cwc_nets']))
				others_nets = int(check_for_blank_cells(record['others_nets']))

				total_nets = anc_nets + cwc_nets + others_nets
				bal_cf = int(check_for_blank_cells(record['bal_cf']))

				# if confirm_nets_issuance(total_nets, facility.net_balance, facility.system_net_balance):
				if Distribution_report.objects.filter(facility=facility, dist_month=dist_month, dist_year=dist_year).exists():
					continue
				else:
					distribution = Distribution_report(
							facility=facility,
							dist_month=dist_month,
							dist_year=dist_year,
							cwc_nets=cwc_nets,
							anc_nets=anc_nets,
							others_nets=others_nets,
							total_nets=total_nets,
							bal_cf=bal_cf
							).save()

					facility.net_balance = bal_cf
					facility.system_net_balance = facility.system_net_balance - total_nets
					facility.save()

					# else:
					# 	messages.error(request, "Error! Nets issued for {}, exceed the remaining balance!".format(facility))
					# 	return redirect("distribution:record_distribution")

			else:
				missing_facilities.append(record['facility'])
				continue

		messages.success(request, "Success! Nets distribution reports successfully recorded.")
		request.session['missing_facilities'] = missing_facilities
		return redirect('distribution:nets_distributed')

	else:
		template = "distribution/excel-distribution.html"
		context={}
		return render(request, template, context)
Beispiel #14
0
def students(request):
    if request.method == 'POST':
        form = AddStudentsForm(request.POST, request.FILES)
        if form.is_valid():
            file = handle_uploaded_file(request.FILES['file'])

            records = pe.get_records(file_name=file)
            i = 0

            repeated = []
            students = map(lambda  s:int(s.admission_no), Students.objects.all())
            print(students)
            for record in records:

                admission_no = record['admissionno']

                if admission_no not in students:

                    login = Login()
                    username = record['firstname'].lower() + "_" + str(record['admissionno'])
                    password = helpers.generateRandomPassword()
                    login.username = username
                    login.password = password
                    login.type = 'student'
                    login.status = '1'
                    login.save()

                    tomail = record['email']
                    subject = 'Codepad Student account created'
                    message = 'Your codepad student account has been created. Please note the following credentials - Username : '******', Password : '******'admissionno']
                    student.firstname = record['firstname']
                    student.lastname = record['lastname']
                    student.address = record['address']
                    student.contact = record['contact']
                    student.email = record['email']
                    student.department = record['department']
                    student.semester = record['semester']
                    student.save()
                    i += 1

                else:
                    repeated.append(admission_no)

            request.session['alert'] = str(i) + ' Student accounts created'

    else:
        # request.session['alert'] = None
        form = AddStudentsForm()

    data = Students.objects.all()
    return render(request, 'Faculty/students.html', {'form': form, 'students': data})
Beispiel #15
0
def uploadfj():
    file = request.files['file']
    # app.logger.info(file.filename)
    if file:
        filename = file.filename
        app.logger.info(filename)
        data_xml = pyexcel.get_records(file_type="xlsx", file_content=file)
        # app.logger.info(data_xml)
        result = exec_stream_fj(data_xml)
        return json.dumps(result)
Beispiel #16
0
def getDependencies(rally):
    import pyexcel as pe
    import pyexcel.ext.xlsx

    records = pe.get_records(file_name="RallyUpload-DependenciestoCA-v2.xlsx")
    for record in records:
        print record['RALLY ID'] + " - " + record['Successor']
        addDependenciesToRally(rally, record['RALLY ID'], record['Successor'])

    return ()
Beispiel #17
0
 def get_users(self):
     '''Abre un archivo .xlsx y obtiene las dos columnas (usuario y fecha) obtiene los datos de before during y after del evento'''
     data = excel.get_records(file_name=sys.argv[1])
     users = {}
     for record in data:
         event = record['Date']
         usname = record['Screenname']
         if not users.has_key(usname):
             users.update({usname: event})
     return users
Beispiel #18
0
def main(argv):
    # TODO consider using argparse
    if len(argv) < 4:
        print(
            "Four parameters are mandatory: input file, picture directory, first name and last name",
            file=sys.stderr)
        return
    t = pe.get_records(file_name=argv[0])
    ft = FamilyTreeMapping(t, argv[1])
    ft.printGedcom(argv[2], argv[3])
Beispiel #19
0
 def test_get_records_from_records(self):
     data = [
         {"X": 1, "Y": 2, "Z": 3},
         {"X": 4, "Y": 5, "Z": 6}
     ]
     result = pe.get_records(records=data)
     eq_(result, [
         {"X": 1, "Y": 2, "Z": 3},
         {"X": 4, "Y": 5, "Z": 6}
     ])
 def test_get_records_from_records(self):
     data = [
         {"X": 1, "Y": 2, "Z": 3},
         {"X": 4, "Y": 5, "Z": 6}
     ]
     result = pe.get_records(records=data)
     assert result == [
         {"X": 1, "Y": 2, "Z": 3},
         {"X": 4, "Y": 5, "Z": 6}
     ]
Beispiel #21
0
def loadExcelInDict():
    import pyexcel
    from pyexcel._compact import OrderedDict
    file = 'C:\\Users\\r103co62\\Desktop\\Template.xlsx'
    # Get your data in an ordered dictionary of lists
    my_dict = pyexcel.get_dict(file_name=file, name_columns_by_row=0)
    # Get your data in a dictionary of 2D arrays
    book_dict = pyexcel.get_book_dict(file_name=file)
    # Retrieve the records of the file
    records = pyexcel.get_records(file_name=file)
Beispiel #22
0
def importjifang(mysql, datafile):
    records = pyexcel.get_records(file_name=datafile)
    rkeys = {}
    for k in records[0].keys():
        rkeys[k] = GetKey(k)

    name = datafile.split("_")[0]
    tbname = p.get_pinyin(name, "")
    tbname = "jt_" + tbname

    # records = pyexcel.get_dict(file_name="逻辑站点_2020-07-18.xlsx")

    # s = collections.Counter([v.strip() for v in records["逻辑站点名称"]])

    # print([k for k, v in s.items() if v > 1])
    # print(keys)
    with mysql.cursor() as cursor:
        cursor.execute("TRUNCATE TABLE %s;" % tbname)
    mysql.commit()
    index = 1
    with mysql.cursor() as cursor:
        for i, r in enumerate(records):
            if i < 4:
                continue

            keys = []
            values = []
            for k, v in r.items():
                if v:
                    keys.append(rkeys[k])
                    values.append(v)
            keysname = ", ".join(keys)
            valuesdata = ", ".join(["'" + v.strip() + "'" for v in values])
            sql = f"""INSERT INTO {tbname} ({keysname})
            VALUES
            ({valuesdata}) ;"""
            # print(sql)
            # with mysql.cursor() as cursor:
            try:
                cursor.execute(sql)
            except Exception as e:
                print(e)
                print(sql)

            if index % 10000 == 0:
                try:
                    mysql.commit()
                    print(index)
                except Exception as e:
                    print(e)
                    print(sql)

            index += 1

    mysql.commit()
Beispiel #23
0
    def _get_records(self):
        """
        Open the records as a list.
        If you use iget then it returns a generator and we can't
        easily save the state.
        pe.iget_records(file_name=self.SOURCE)

        :return:
        """
        records = pe.get_records(file_name=self.SOURCE)
        return records
Beispiel #24
0
 def test_get_records_from_array(self):
     data = [
         ["X", "Y", "Z"],
         [1, 2, 3],
         [4, 5, 6]
     ]
     result = pe.get_records(array=data)
     eq_(result, [
         {"X": 1, "Y": 2, "Z": 3},
         {"X": 4, "Y": 5, "Z": 6}
     ])
Beispiel #25
0
 def test_get_records_from_dict(self):
     data = {
         "X": [1, 4],
         "Y": [2, 5],
         "Z": [3, 6]
     }
     result = pe.get_records(adict=data)
     eq_(result, [
         {"X": 1, "Y": 2, "Z": 3},
         {"X": 4, "Y": 5, "Z": 6}
     ])
 def test_get_records_from_dict(self):
     data = {
         "X": [1, 4],
         "Y": [2, 5],
         "Z": [3, 6]
     }
     result = pe.get_records(adict=data)
     assert result == [
         {"X": 1, "Y": 2, "Z": 3},
         {"X": 4, "Y": 5, "Z": 6}
     ]
 def test_get_records_from_array(self):
     data = [
         ["X", "Y", "Z"],
         [1, 2, 3],
         [4, 5, 6]
     ]
     result = pe.get_records(array=data)
     assert result == [
         {"X": 1, "Y": 2, "Z": 3},
         {"X": 4, "Y": 5, "Z": 6}
     ]
Beispiel #28
0
def init_connection():
    vg_data = pyexcel.get_records(file_name='vgsales.csv')

    client = pymysql.connect(host='localhost',
                             port=3306,
                             user='******',
                             password='******',
                             cursorclass=pymysql.cursors.DictCursor)

    board = client.cursor()
    return [vg_data, client, board]
Beispiel #29
0
def handle_excel():
    records = pe.get_records(file_name="example.xlsx")
    for record in records:
        print("%s 剩余应收款为 %d" % (record['销售名称'], record['剩余应收款']))
        if int(record['剩余应收款']) > 0:
            print("正在将本条单独拆分出来...")
        elif str.find(record['销售名称'], '\/'):
            # if '\/' in record['销售名称']:
            print("有两个销售")
        else:
            print("剩余应收款为0,不做操作...")
        print("表格拆分完毕,即将进入下一步,发送邮件")
Beispiel #30
0
def test_issue_95_preserve_order_in_get_orders():
    test_data = [['a', 'b', 'c'], ['1', '2', '3'], ['4', '5', '6']]

    records = pe.get_records(array=test_data)
    result = []
    for record in records:
        for key, value in record.items():
            result.append([key, value])

    expected = [['a', '1'], ['b', '2'], ['c', '3'], ['a', '4'], ['b', '5'],
                ['c', '6']]
    eq_(result, expected)
 def test_get_records_from_memory(self):
     data = [
         ["X", "Y", "Z"],
         [1, 2, 3],
         [4, 5, 6]
     ]
     content = pe.save_as(dest_file_type="xls", array=data)
     records = pe.get_records(file_content=content.getvalue(), file_type="xls")
     assert records == [
         {"X": 1, "Y": 2, "Z": 3},
         {"X": 4, "Y": 5, "Z": 6}
     ]
Beispiel #32
0
def process_vehicle_catalogs(request):
    model = request.GET.get('model')
    try:
        records = pyexcel.get_records(file_name=MODEL_OPTIONS[model]['path'])
    except Exception as err:
        return Response({'error': err.args},
                        status=status.HTTP_500_INTERNAL_SERVER_ERROR)
    count = 0
    for item in records:
        MODEL_OPTIONS[model]['model'].objects.create(name=item['name'])
        count += 1
    return Response({'message': f'{count} objects created'},
                    status=status.HTTP_201_CREATED)
Beispiel #33
0
def get_records_as_list_with_dicts(file_type, content, header,
                                   excel_header_fields):
    records = pyexcel.get_records(file_type=file_type, file_content=content)
    records_list = []
    excel_header_fields = [field.lower() for field in excel_header_fields]
    for record in records:
        row = {}
        for header_field in header:
            if header_field.lower() in excel_header_fields:
                row[header_field] = record[header_field]
        records_list.append(row)
    #print(f"AKHI: {records_list}")
    return records_list
Beispiel #34
0
def upload_employeedata_step2(request):
    req=request.POST
    records = pe.get_records(file_name=req['file_name'])

    if ('Employee ID' in records[0] and req['data_type']=='Employee') or  ('Report ID' in records[0] and req['data_type']=='Expenses'):
        json_data = json.dumps( { 'status':'success','message':'File formate is ok.', 'file_name':req['file_name'], 'upload_id':req['upload_id']  } )
        AvLogDataUploads.objects.filter(data_upload_id=req['upload_id']).update(status='2 of 6 File Formate Check')
        utilb.logManage(request.user.company.id,request.user.company.short_name,datetime.now(),'Data Load','Records Processed','Information','Checked file formate',request.user.user_id,req['upload_id'],'uploadData')
    else:
        json_data = json.dumps( { 'status':'danger','message':'File formate is not valid.', 'file_name':req['file_name'], 'upload_id':req['upload_id']  } )
        AvLogDataUploads.objects.filter(data_upload_id=req['upload_id']).update(status='1 of 6 File Formate Check')
        utilb.logManage(request.user.company.id,request.user.company.short_name,datetime.now(),'Data Load','Records Processed','Information','Checked file formate failed',request.user.user_id,req['upload_id'],'uploadData')
    return HttpResponse( json_data , content_type='application/json')
Beispiel #35
0
    def get_records(self, **keywords):
        """Get a list of records from the file

        :param sheet_name: For an excel book, there could be multiple
                           sheets. If it is left unspecified, the
                           sheet at index 0 is loaded. For 'csv',
                           'tsv' file, *sheet_name* should be None anyway.
        :param keywords: additional key words
        :returns: A list of records
        """
        params = self.get_params(**keywords)
        if 'name_columns_by_row' not in params:
            params['name_columns_by_row'] = 0
        return pe.get_records(**params)
Beispiel #36
0
    def _method_pyexcel(self, out_sep=DEFAULT_OUT_SEP,
            line_terminator=DEFAULT_LINE_TERMINATOR, sheet_name=0,
            *args, **kwargs):
        """Do the conversion :term:`XLSX` -> :term:`CSV` using pyexcel library"""
        import pyexcel

        with open(self.outfile, "w") as out_stream:
            writer = csv.writer(out_stream, delimiter=out_sep, lineterminator=line_terminator)
            first_row = True
            for row in pyexcel.get_records(file_name=self.infile):
                if first_row:
                    writer.writerow([k for k, v in row.items()])
                    first_row = False
                writer.writerow([v for k, v in row.items()])
Beispiel #37
0
    def get_records(self, **keywords):
        """Get a list of records from the file

        :param sheet_name: For an excel book, there could be multiple
                           sheets. If it is left unspecified, the
                           sheet at index 0 is loaded. For 'csv',
                           'tsv' file, *sheet_name* should be None anyway.
        :param keywords: additional key words
        :returns: A list of records
        """
        params = self.get_params(**keywords)
        if 'name_columns_by_row' not in params:
            params['name_columns_by_row'] = 0
        return pe.get_records(**params)
Beispiel #38
0
def upload_employeedata_step3(request):
    req = request.POST
    records = pe.get_records(file_name=req['file_name'])

    stat = True
    if req['data_type'] == 'Employee':
        ls = []
        for record in records:
            if record['Employee ID'] in ls:
                stat = False
            ls.append(record['Employee ID'])

    elif req['data_type'] == 'Expenses':
        ls = []
        for record in records:
            if record['Report Line ID'] in ls:
                stat = False
            ls.append(record['Report Line ID'])

    if stat == True:
        status = 'success'
        msg = 'All records is unique'
        AvLogDataUploads.objects.filter(
            data_upload_id=req['upload_id']).update(
                status='4 of 6 File Formate Check')
        utilb.logManage(request.user.company.id,
                        request.user.company.short_name, datetime.now(),
                        'Data Load', 'Duplicate Records', 'Information',
                        'Duplicate Records checked', request.user.user_id,
                        req['upload_id'], 'uploadData')
    else:
        status = 'danger'
        msg = 'Some records are duplicate'
        AvLogDataUploads.objects.filter(
            data_upload_id=req['upload_id']).update(
                status='2 of 6 File Formate Check')
        utilb.logManage(request.user.company.id,
                        request.user.company.short_name, datetime.now(),
                        'Data Load', 'Duplicate Records', 'Information',
                        'Duplicate Records checked failed',
                        request.user.user_id, req['upload_id'], 'uploadData')

    json_data = json.dumps({
        'status': status,
        'message': msg,
        'file_name': req['file_name'],
        'upload_id': req['upload_id']
    })
    return HttpResponse(json_data, content_type='application/json')
Beispiel #39
0
def init_connection():
    vg_data = pyexcel.get_records(file_name='vgsales.csv')

    client = pymysql.connect(
        host='localhost',
        port=3306,
        user='******',
        password='******',
        cursorclass=pymysql.cursors.
        DictCursor  # turn data type into dictionary/ list of dictionaries
    )

    board = client.cursor()

    return [vg_data, board]
Beispiel #40
0
 def get_data(self):
     '''Abre un archivo .xlsx y obtiene las dos columnas (usuario y fecha) obtiene los datos de before during y after del evento'''
     data = excel.get_records(file_name=sys.argv[1])
     users, dates = ({} for i in range(2))
     metadata = {}
     tweets = {}
     for record in data:
         hashtag = record['Hashtag']
         if hashtag != 'none' and hashtag != '' and '#' in hashtag:
             if not tweets.has_key(hashtag):
                 print hashtag
                 t = self.get_tweets(hashtag)
                 tweets.update({hashtag: t})
         else:
             pass
     print tweets
Beispiel #41
0
def test_issue_95_preserve_order_in_get_orders():
    test_data = [["a", "b", "c"], ["1", "2", "3"], ["4", "5", "6"]]

    records = p.get_records(array=test_data)
    result = []
    for record in records:
        for key, value in record.items():
            result.append([key, value])

    expected = [
        ["a", "1"],
        ["b", "2"],
        ["c", "3"],
        ["a", "4"],
        ["b", "5"],
        ["c", "6"],
    ]
    eq_(result, expected)
Beispiel #42
0
def upload_employeedata_step6(request):
    req=request.POST
    records = pe.get_records(file_name=req['file_name'])

    if req['data_type']=='Employee':
        AvLogDataUploads.objects.filter(data_upload_id=req['upload_id']).update(status='6 of 6 Audvantage Done')
        msg='6 to 6 Audvantage Done'
    elif req['data_type']=='Expenses':
        sqlqry="SELECT av_engine.av_check_exp_engine_fn(%s, '%s', '%s','%s')"%(req['comp_id'],req['comp_name'],request.user.user_id,req['upload_id'])
        ret_msg=custom_query(sqlqry)
        if ret_msg=='SUCCESS':
            msg='6 to 6 Audvantage Done'
            AvLogDataUploads.objects.filter(data_upload_id=req['upload_id']).update(status='6 of 6 Audvantage Done')
        else:
            AvLogDataUploads.objects.filter(data_upload_id=req['upload_id']).update(status='6 of 6 Audvantage Error')
            msg='6 to 6 Audvantage Error'

    json_data = json.dumps( { 'status':'success','message':msg, 'file_name':req['file_name'], 'upload_id':req['upload_id'] , 'logData': getLogData(request.user.company.id) } )
    utilb.logManage(request.user.company.id,request.user.company.short_name,datetime.now(),'Data Load','Load End','Information','Load End',request.user.user_id,req['upload_id'],'uploadData')
    return HttpResponse( json_data , content_type='application/json')
Beispiel #43
0
def uploadEmployeeData(request):
    json_data=None
    DataType=AvMasterUploadDataType.objects.order_by('data_type')
    DataType_json = serializers.serialize('json', DataType )
    DataType_json_list = json.loads( DataType_json )
    cursor = connection.cursor()
    
    rolePermisn=utilb.userRolePermission(request.user.company.id,request.user.user_id)
    compData=''

    if 'View All Companies' in rolePermisn:
        compData=AvMasterCompany.objects.values('id','short_name').order_by('short_name')
    elif 'View Company' in rolePermisn:
        compData=AvMasterCompany.objects.values('id','short_name').filter(id=request.user.company.id).order_by('short_name')

    if request.method == "POST":
        data=request.FILES['file']
        path = default_storage.save('tmp/import.xls', ContentFile(data.read()))
        tmp_file = os.path.join(settings.MEDIA_ROOT, path)
        records = pe.get_records(file_name=tmp_file)
        req=request.POST

        cursor.execute("SELECT nextval('av_log_data_upload_id_seq')")
        upload_id=cursor.fetchone()
        cursor.close()
        uploadID=int(''.join(map(str,upload_id)))

        c=AvLogDataUploads(data_upload_id=uploadID,company_id=request.user.company.id,company_name=request.user.company.short_name,data_type=req['data_type'],uploaded_by=request.user.user_id,uploaded_date=datetime.now(),no_of_records='0',status='1 of 6 - File Upload',upload_or_rollback='File Upload')
        c.save()

        json_data = json.dumps( { 'status':'success','message':'File uploaded successfully.', 'file_name':tmp_file, 'upload_id':uploadID ,'logData': getLogData(request.user.company.id) } )
        utilb.logManage(request.user.company.id,request.user.company.short_name,datetime.now(),'Data Load','Load Start','Information','File Uploaded',request.user.user_id,uploadID,'uploadData')

    else:
        json_data = json.dumps( { 'DataType':DataType_json_list, 'compData':list(compData) ,'UserCompId':request.user.company.id, 'logData': getLogData(request.user.company.id), 'rolePermission':utilb.userRolePermission(request.user.company.id,request.user.user_id)} )

    return HttpResponse( json_data , content_type='application/json')
Beispiel #44
0
##from linecache import*
##x=open('filex.txt')
##a=[]
##c=[]
##a=x.readlines()
##for i in range(len(a)):
##    c.append(a[i].split(' '))
##d=[]
##for i in range (len(c)):
##    d.append(int(c[i][1])*int(c[i][2]))
##    print(c[i][0]+' get '+ str(d[i])+' this month')
##
##import webbrowser
##import time
##from turtle import*
##def getinternet(i):
##    lit=['https://www.facebook.com','https://www.youtube.com/','http://www.tutorialspoint.com/python/time_sleep.htm']
##    if i=='1':
##        webbrowser.open(lit[0])
##    elif i=='2':
##        webbrowser.open(lit[1])
##    else:
##        webbrowser.open(lit[2])
##i=input('number plz:')
##getinternet(i)
import pyexcel as pe
import pyexcel.ext.xls
record=pe.get_records(file_name='xxx.xls')
for record in records:
    print("%s is aged at %d" %(record['Name'],record['Age']))
Beispiel #45
0

import pyexcel as pe
import pyexcel.ext.xls
import pyexcel.ext.xlsx

records = pe.get_records(file_name="/Users/cyberprism/Desktop/CSE_S4.xlsx")
for record in records:
    print(record['firstname'], record['lastname'])
Beispiel #46
0
import datetime
import ujson
import pyexcel as pe
import pyexcel.ext.xlsx


client = pymongo.MongoClient()
today = datetime.date.today()
today = today.strftime("%Y%m%d")
# creat database and collection
db1 = client['ACAHack']
col = 'formulary_' + today


#open file
records = pe.get_records(file_name="a.xlsx")
counter = 0
for record in records:
    url = record["URL Submitted"]
    if ".json" in url:

        response = urllib.urlopen(url)
        data = json.loads(response.read())

        for url2 in data["formulary_urls"]:
            print url2
            response2 = urllib.urlopen(url2)
            print type(response2)
            dic = ujson.loads(response2.read())
            for item in dic:
                try:
 def test_get_records(self):
     pe.get_records("something")
 def test_get_records_from_sql(self):
     records = pe.get_records(session=Session(), table=Signature)
     assert records == [{"X": 1, "Y": 2, "Z": 3}, {"X": 4, "Y": 5, "Z": 6}]
 def test_get_records(self):
     expected = pe.get_records("something")
     assert expected == None
Beispiel #50
0
import smtplib
import pyexcel as pe
import pyexcel.ext.xlsx

def send_email(message, rev):
    username = "******"
    password = "******"
    email_helper = smtplib.SMTP('smtp.gmail.com:587')
    email_helper.starttls()
    email_helper.login(username,password)
    email_helper.sendmail("*****@*****.**", rev,message)
    email_helper.quit()
records = pe.get_records(file_name="info.xlsx")
for record in records:
    n = record['Name']
send_email("Subject :"+ n +"oi\nTran Thanh Tu",record['Email'])


# class calculateDistance(city):
#     hanoi = {"name" : "Ha Noi", "longt: 50, "lat" : 75}
#     daiduong = {"name": "Hai Duong", "longt: 25, "lat" : 10}
#     def __init__(self, name, longt, lat ):
#         self.name = name
#         self.longt = longt
#         self.lat = lat
#     def distance(self):
# class City:
#     def __init__(self, name, longt, lat):
#         self.name = name
#         self.longt = longt
#         self.lat = lat
Beispiel #51
0
import sys
reload(sys)
sys.setdefaultencoding('utf-8')
import pyexcel as pe
import pyexcel.ext.xlsx
records = pe.get_records(file_name="yahoo.xlsx")
c=0
#f = open("tickers.txt", "w")
for record in records:
	print ("%s::%s" %(record['Ticker'].encode('utf-8'),record['Name'].encode('utf-8')))
	#f.write("%s::%s\n" % (record['Ticker'], record['Name']))

#f.close()