0

Read excel file and extract the details into a new .txt file using openpyxl

I am new to openpyxl, just started yesterday. I need to extract the data from the excel file from my local storage that excel file have 500+ rows and 50+ columns. I want to extract some specific cells or columns or rows into the .txt and .csv file.

I cannot find where is the mistake what to add in this code

from openpyxl import *
import os    

path = 'F:\\mis'
files = [i for i in os.listdir(path) if i.endswith('.xlsx')]

for f in files:
     wb = load_workbook(os.path.join(path, f))
     for row in wb['newxl.xlsx'].rows:
         with open(row[2].value+'.txt', 'w') as outfile:
              outfile.write(row[0].value)```

2 Answers2

0

UPDATED ANSWER

import openpyxl
import csv
      
roster = openpyxl.load_workbook('roster.xlsx')
sheet = roster.active
col = csv.writer(open("new_roster.csv",'w',newline=""))

st_id = int(input("Enter student ID: "))
for row in sheet.rows:
    if row[0].value == st_id:
        col.writerow([cell.value for cell in row])
print("File created!")

UPDATE 2

This is how you can get the values from a specific column:

import openpyxl
roster = openpyxl.load_workbook('roster.xlsx')  
sheet = roster.active
col = sheet['B']  

for x in range(len(col)): 
    print(col[x].value)

UPDATE 3

Return specific values from column:

import openpyxl

roster = openpyxl.load_workbook('roster.xlsx')  
sheet = roster.active

col = sheet['B']  # <-- change the column according to your file
val = input("Enter value: ")
for c in range(len(col)): 
    if val == col[c].value:
        print(f'Found {col[c].value}!')
Robin Sage
  • 969
  • 1
  • 8
  • 24
  • I want to extract the details from existing xlsx file from local storage –  Jan 03 '22 at 11:42
  • 1
    I think that's what I did. I created a sample xlsx file and extracted some data. Or maybe I didn't quite understand the question. – Robin Sage Jan 03 '22 at 11:53
  • partially i got the answer –  Jan 04 '22 at 05:17
  • but i need to append these into .txt and csv file –  Jan 04 '22 at 05:18
  • 1
    Just updated the answer. Lemme know if that works for ya. – Robin Sage Jan 04 '22 at 05:44
  • it appends all the data from the excel file but i need to extract some specific cells and rows using column name . –  Jan 04 '22 at 05:58
  • 1
    Sorry. I forgot. Updated it again... – Robin Sage Jan 04 '22 at 06:16
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/240702/discussion-between-mohanraj-m-and-robin-sage). –  Jan 04 '22 at 06:59
  • i just need some specific cells from the row using column name. that's my requirement –  Jan 04 '22 at 08:01
  • Just updated the answer. If you find it useful, please upvote it and check it as answered. Thanks! – Robin Sage Jan 04 '22 at 10:37
  • It prints all the row values of the given column of the excel sheet. –  Jan 04 '22 at 10:56
  • It prints all the row values of the given column of the excel sheet. but what my need is i want to extract some some specific fields' for example, If we have an excel sheet with 5 columns(id, name, roll_number, dob, blood_group) and 5 rows (imagine any 5 dummy data's in these field). here i need id no 3 ' s name and dob how to get those the user is need to choose the option of what bassed on the search (for ex) with the column 2nd named name 4th rows dob and id we need to get –  Jan 04 '22 at 11:15
  • Updated answer. – Robin Sage Jan 04 '22 at 11:17
  • Nothing is printed in this update just getting input. i need not only one columns value i need some specific column values for each row. if you are free can we have a call i'll explain well for you –  Jan 04 '22 at 11:24
  • Check the update. You need to change the column based on the file you have there. It should work fine. You have all the code you need to get it to work. If you find the answer I provided satisfactory, please upvote and check it as answered. – Robin Sage Jan 04 '22 at 12:42
0
import pandas as pd
df=pd.read_excel("F:\\mis\\CASHE.xlsx")
cus_id=input("Please enter your customer id:")
cashe=['customer_id', 'customer_name', 'login_email_id', 'login_source', 'New_date', 'customer_status_name']

if cus_id == '':
    df1=df[cashe]
else:
    df1=df[cashe].loc[df['customer_id']==int(cus_id)]

df1.to_csv('Cashe1.csv')
print("csv printed with values")
Shunya
  • 2,344
  • 4
  • 16
  • 28