Python Forum
I'm working onn below code to extract data from excel using python
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?

User has been warned for this post. Reason: new threads unnecessarily, no BBcode
Reply
#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)
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Having trouble setting the X-Ticks for a data set I am working with. Mr_OHagan 1 460 Jan-22-2024, 09:12 PM
Last Post: deanhystad
  Data Sorting and filtering(From an Excel File) PY_ALM 0 1,012 Jan-09-2023, 08:14 PM
Last Post: PY_ALM
  Training a model to identify specific SMS types and extract relevant data? lord_of_cinder 0 955 Oct-10-2022, 04:35 AM
Last Post: lord_of_cinder
  extract and plot data from a txt file usercat123 2 1,208 Apr-20-2022, 06:50 PM
Last Post: usercat123
  Help with poorly formatted excel data armitron121 1 1,694 Jan-13-2022, 07:31 AM
Last Post: paul18fr
  Exporting data frame to excel dyerlee91 0 1,604 Oct-05-2021, 11:34 AM
Last Post: dyerlee91
  [Pandas] Write data to Excel with dot decimals manonB 1 5,774 May-05-2021, 05:28 PM
Last Post: ibreeden
  How to extract data from paragraph using Machine Learning with python? bccsthilina 2 3,007 Jul-27-2020, 07:02 AM
Last Post: hussainmujtaba
  How do I translate this into python code? (excel vlookup) eeps24 6 2,732 May-29-2020, 05:54 PM
Last Post: eeps24
  excel data erase d8a988 1 2,104 Apr-27-2020, 05:43 PM
Last Post: snippsat

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020