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
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 ?
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)
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)
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)
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)
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)
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)
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)
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)
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)
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 DEBIAN
spreadsheet::xlsx
Spreadsheet::XLSX(3pm) User Contributed Perl Documentation Spreadsheet::XLSX(3pm)NAME
Spreadsheet::XLSX - Perl extension for reading MS Excel 2007 files;
SYNOPSIS
use Text::Iconv;
my $converter = Text::Iconv -> new ("utf-8", "windows-1251");
# Text::Iconv is not really required.
# This can be any object with the convert method. Or nothing.
use Spreadsheet::XLSX;
my $excel = Spreadsheet::XLSX -> new ('test.xlsx', $converter);
foreach my $sheet (@{$excel -> {Worksheet}}) {
printf("Sheet: %s
", $sheet->{Name});
$sheet -> {MaxRow} ||= $sheet -> {MinRow};
foreach my $row ($sheet -> {MinRow} .. $sheet -> {MaxRow}) {
$sheet -> {MaxCol} ||= $sheet -> {MinCol};
foreach my $col ($sheet -> {MinCol} .. $sheet -> {MaxCol}) {
my $cell = $sheet -> {Cells} [$row] [$col];
if ($cell) {
printf("( %s , %s ) => %s
", $row, $col, $cell -> {Val});
}
}
}
}
DESCRIPTION
This module is a (quick and dirty) emulation of Spreadsheet::ParseExcel for Excel 2007 (.xlsx) file format. It supports styles and many of
Excel's quirks, but not all. It populates the classes from Spreadsheet::ParseExcel for interoperability; including Workbook, Worksheet,
and Cell.
SEE ALSO
Text::CSV_XS, Text::CSV_PP
http://search.cpan.org/~hmbrand/
A pure perl version is available on http://search.cpan.org/~makamaka/
Spreadsheet::ParseExcel
http://search.cpan.org/~kwitknr/
Spreadsheet::ReadSXC
http://search.cpan.org/~terhechte/
Spreadsheet::BasicRead
http://search.cpan.org/~gng/ for xlscat likewise functionality (Excel only)
Spreadsheet::ConvertAA
http://search.cpan.org/~nkh/ for an alternative set of cell2cr () / cr2cell () pair
Spreadsheet::Perl
http://search.cpan.org/~nkh/ offers a Pure Perl implementation of a spreadsheet engine. Users that want this format to be supported in
Spreadsheet::Read are hereby motivated to offer patches. It's not high on my todo-list.
xls2csv
http://search.cpan.org/~ken/ offers an alternative for my "xlscat -c", in the xls2csv tool, but this tool focusses on character encoding
transparency, and requires some other modules.
Spreadsheet::Read
http://search.cpan.org/~hmbrand/ read the data from a spreadsheet (interface module)
AUTHOR
Dmitry Ovsyanko, <do@eludia.ru<gt>, http://eludia.ru/wiki/
Patches by:
Steve Simms
Joerg Meltzer
Loreyna Yeung
Rob Polocz
Gregor Herrmann
H.Merijn Brand
endacoe
Pat Mariani
Sergey Pushkin
ACKNOWLEDGEMENTS
Thanks to TrackVia Inc. (http://www.trackvia.com) for paying for Rob Polocz working time.
COPYRIGHT AND LICENSE
Copyright (C) 2008 by Dmitry Ovsyanko
This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself, either Perl version 5.8.8 or,
at your option, any later version of Perl 5 you may have available.
perl v5.10.1 2010-05-16 Spreadsheet::XLSX(3pm)