Bottom Page

Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
 I'm working onn below code to extract data from excel using python
#1
code
import xlrd
#import mysqldb
import datetime
import pandas as pd


ExcelFileName = "H:\Data.xlsx"
workbook = xlrd.open_workbook(ExcelFileName)
worksheet = workbook.sheet_by_name("Timecard Details")
num_rows = worksheet.nrows
num_cols = worksheet.ncols

for r in range(1,num_rows):
ClientNo =worksheet.cell(r,9).value
data =ClientNo.split('|')
date = worksheet.cell(r, 6).value
dt=xlrd.xldate_as_datetime(date, workbook.datemode)
hours = worksheet.cell(r, 7).value
df = pd.DataFrame({'customer': [data[2]], 'time': [hours], 'Date': [dt], 'Mainetance type': [data[1]],
'Maintenance': [data[0]]})

print(df)


Output: I want the below output to be under one header like all dates under data and all hours under hours.

Date Mainetance type Maintenance customer time
0 2017-10-03 BENCHMARKS MAINTENANCE 7702 0.25
Date Mainetance type Maintenance customer time
0 2017-10-04 TASK DOCUMENTATION MAINTENANCE 2037 1.5
Date Mainetance type Maintenance customer time
0 2017-10-03 BENCHMARK SETTER MAINTENANCE 7809 0.25
Date Mainetance type Maintenance customer time
0 2017-10-05 AS DESIGN MAINTENANCE 1233 2.5
Date Mainetance type Maintenance customer time
0 2017-10-05 AS REPORTS MAINTENANCE 7992 1.5

Hi guys can you please help me on this?
buran wrote Oct-23-2017, 06:24 AM:
Please, don't start new threads unnecessarily. Also, please, use proper tags when post code, traceback, output, etc. See BBcode help for more info.


User has been warned for this post. Reason: new threads unnecessarily, no BBcode
Quote
#2
import xlrd
import pandas as pd
#import mysqldb
import datetime



ExcelFileName = "D:\Oracle_Oct 2 to 6.xlsx"
workbook = xlrd.open_workbook(ExcelFileName)
worksheet = workbook.sheet_by_name("Timecard Details")
num_rows = worksheet.nrows
num_cols = worksheet.ncols



for r in range(1,num_rows):
        ClientNo =worksheet.cell(r,9).value
        data =ClientNo.split('|')
        date = worksheet.cell(r, 6).value
        dt=xlrd.xldate_as_datetime(date, workbook.datemode)
        hours = worksheet.cell(r, 7).value

        if 'MAINTENANCE' in data[0]:
            values=(data[2], '|',hours,'|',dt, '|', data[1],'|',data[0])

            df = pd.DataFrame({'customer': [data[2]],'time':[hours],'Date':[dt],'Mainetance type':[data[1]],'Maintenance':[data[0]] })


            writer = pd.ExcelWriter("D:\Data.xlsx", engine='xlsxwriter')


            df.to_excel(writer, sheet_name=' MAINTENANCE ')

            writer.save()
            print(df)



Quote

Top Page

Possibly Related Threads...
Thread Author Replies Views Last Post
  How do I translate this into python code? (excel vlookup) eeps24 6 219 May-29-2020, 05:54 PM
Last Post: eeps24
  excel data erase d8a988 1 186 Apr-27-2020, 05:43 PM
Last Post: snippsat
  Dropna Subset Not Working In Code eddywinch82 0 320 Mar-05-2020, 03:10 PM
Last Post: eddywinch82
  how to extract financial data from photocopy of document angela1 6 281 Feb-15-2020, 05:50 PM
Last Post: jim2007
  Python read Excel cell data validation anantpatil 0 369 Jan-31-2020, 04:57 PM
Last Post: anantpatil
  How to extract data between two strings SriMekala 2 464 Aug-08-2019, 01:54 PM
Last Post: SriMekala
  Need Help With Filtering Data For Excel Files Using Pandas eddywinch82 9 1,040 Aug-06-2019, 03:44 PM
Last Post: eddywinch82
  Python Package for deploying python code in Excel on machines without python UGuntupalli 2 3,982 Jul-31-2019, 04:05 AM
Last Post: UGuntupalli
  Aligning excel data gat 1 498 Jun-17-2019, 07:05 PM
Last Post: michalmonday
  How to extract different data groups from multiple CSV files using python Rafiz 3 707 Jun-04-2019, 05:20 PM
Last Post: jefsummers

Forum Jump:


Users browsing this thread: 1 Guest(s)