Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: Read .xlsx and text files in a loop using openpyxl
Top Forums Programming Read .xlsx and text files in a loop using openpyxl Post 302998474 by nans on Friday 2nd of June 2017 10:41:35 AM
Old 06-02-2017
Read .xlsx and text files in a loop using openpyxl
I have a list of .xlsx files (names 1.xlsx, 2.xlsx etc) in a directory, on which I need to insert data from its corresponding text file (named 1.txt, 2.txt etc) on the second worksheet named 'Filtered' and save it. The code I am trying is

Code:
#!/usr/bin/python

import os
from openpyxl.reader.excel import load_workbook
from openpyxl.drawing.image import Image
import csv

directoryPath = r'/REPORTS/'
os.chdir(directoryPath)
folder_list = os.listdir(directoryPath)
for folders, sub_folders, file in os.walk(directoryPath):  
    for name in file:
        if name.endswith(".xlsx"):
            filename = os.path.join(folders, name)
            wb = load_workbook(filename, data_only=True)
            ws = wb.get_sheet_by_name('Filtered')

##here is where the text files are stored
            directory = os.path.join("/REPORTS/temp", "path")
            for root,dirs,files in os.walk(directory):
               for file in files:
                    if file.endswith(".txt"):
                        f = open(textfile, 'r')
                        reader = csv.reader(f, delimiter='\t')
                        for row in reader:
                           ws.append(row)
                           wb.save(filename)

Although the code goes through all excel files, it is not going through the text files to write its corresponding result onto the 2nd worksheet of each excel file. Any suggestions to edit the code ?
 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

help with a 'while read' loop to change the names of files

Hi, I am new to scripting, so any help on this would be much appreciated. I am trying to rename a bunch of files, taking the names sequentially from a list read in another file... # ls oldnames file_1 file_2 file_3 # cat names red yellow green I want the files to take on the... (6 Replies)
Discussion started by: starsky
6 Replies

2. Shell Programming and Scripting

Read and edit multiple files using a while loop

Hi all, I would like to simply read a file which lists a number of pathnames and files, then search and replace key strings using a few vi commands: :1,$s/search_str/replace_str/g<return> but I am not sure how to automate the <return> of these vis commands when I am putting this in a... (8 Replies)
Discussion started by: cyberfrog
8 Replies

3. Shell Programming and Scripting

Text to xlsx conversion

Hi Guys, The code snippet below is working fine with xls 2003 but the same is not working in xlsx 2007. Can someone please help. #Convert .txt file format to .xls format awk 'BEGIN{ FS="|" cols=8 print "<HTML><style type=\"text/css\">td{mso-number-format:\"\@\"}</style><BODY><TABLE... (1 Reply)
Discussion started by: icefish
1 Replies

4. Fedora

How to read a text file and assign the values in the same to a variable in loop

Hi, I have a text file with multiple lines, each having data in the below format <DOB>,<ADDRESS> I have to write a script which reads each line in the text file in loop, assign the values to these variables and do some further processing in it. Using the following code prints the... (1 Reply)
Discussion started by: manishab00
1 Replies

5. UNIX for Advanced & Expert Users

How to read a text file and assign the values in the same to a variable in loop

Hi, I have a text file with multiple lines, each having data in the below format <DOB>,<ADDRESS> I have to write a script which reads each line in the text file in loop, assign the values to these variables and do some further processing in it. Using the following code prints the values... (12 Replies)
Discussion started by: manishab00
12 Replies

6. Shell Programming and Scripting

Perl :Is it possible to read the excel 2007 sheet on unix machine using spredsheet::xlsx module

I have an Excel 2007 excel sheet on windows machine and using Spreadsheet::XLSX I had written a script to read the excel sheet and was successful. My requirement is I need to generate another excel sheet from the old excel 2007 sheet on unix machine. Now is it possible to read the excel... (2 Replies)
Discussion started by: giridhar276
2 Replies

7. Shell Programming and Scripting

For loop inside awk to read and print contents of files

Hello, I have a set of files Xfile0001 - Xfile0021, and the content of this files (one at a time) needs to be printed between some line (lines start with word "Generated") that I am extracting from another file called file7.txt and all the output goes into output.txt. First I tried creating a for... (5 Replies)
Discussion started by: jaldo0805
5 Replies

8. Shell Programming and Scripting

Read n lines from a text files getting n from within the text file

I dont even have a sample script cause I dont know where to start from. My data lookes like this > sat#16 #data: 15 site:UNZA baseline: 205.9151 0.008 -165.2465 35.8109 40.6685 21.9148 121.1446 26.4629 -18.4976 33.8722 0.017 -165.2243 48.2201 40.6908 ... (8 Replies)
Discussion started by: malandisa
8 Replies

9. Shell Programming and Scripting

Read text between regexps and write into files based on a field in the text

Hi, I have a huge file that has data something like shown below: huge_file.txt start regexp Name=Name1 Title=Analyst Address=Address1 Department=Finance end regexp some text some text start regexp Name=Name2 Title=Controller Address=Address2 Department=Finance end regexp (7 Replies)
Discussion started by: r3d3
7 Replies

10. Shell Programming and Scripting

Read loop from two files using descriptors

What I would like to do is read each line in the atdinfile: A sample atdinfile would look like this: 651 652 653 654 655 656 657 658 659 660 661 664 665 666 667 668 (5 Replies)
Discussion started by: woodson2
5 Replies

LEARN ABOUT BSD

go-packages

GO-PACKAGES(7)						 Miscellaneous Information Manual					    GO-PACKAGES(7)

NAME

       go - tool for managing Go source code

DESCRIPTION

       Many commands apply to a set of packages:

	     go action [packages]

       Usually, [packages] is a list of import paths.

       An  import path that is a rooted path or that begins with a . or .. element is interpreted as a file system path and denotes the package in
       that directory.

       Otherwise, the import path P denotes the package found in the directory DIR/src/P for some DIR listed in the  GOPATH  environment  variable
       (see 'go help gopath').

       If no import paths are given, the action applies to the package in the current directory.

       The  special  import path "all" expands to all package directories found in all the GOPATH trees.  For example, 'go list all' lists all the
       packages on the local system.

       The special import path "std" is like all but expands to just the packages in the standard Go library.

       An import path is a pattern if it includes one or more "..." wildcards, each of which can match any string, including the empty string  and
       strings	containing slashes.  Such a pattern expands to all package directories found in the GOPATH trees with names matching the patterns.
       As a special case, x/... matches x as well as x's subdirectories.  For example, net/... expands to net and packages in its subdirectories.

       An import path can also name a package to be downloaded from a remote repository.  Run 'go help remote' for details.

       Every package in a program must have a unique import path.  By convention, this is arranged by starting each path with a unique prefix that
       belongs	to  you.   For example, paths used internally at Google all begin with 'google', and paths denoting remote repositories begin with
       the path to the code, such as 'code.google.com/p/project'.

       As a special case, if the package list is a list of .go files from a single directory, the command is applied to a single synthesized pack-
       age made up of exactly those files, ignoring any build constraints in those files and ignoring any other files in the directory.

AUTHOR

       This manual page was written by Michael Stapelberg <stapelberg@debian.org>, for the Debian project (and may be used by others).

								    2012-05-13							    GO-PACKAGES(7)
All times are GMT -4. The time now is 07:29 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy