Hello,
I have a Perl script that reads in an Excel spread sheet and formats the values into a text file. I am having trouble with one column that can have numbers or letters. Excel left justifies the values that start with a letter and right justifies the values that contain only a number(s). I thought this bit of code would get rid of spaces, but it is not working:
It prints nothing. When I did a test with the code below it printed the value "92" without the spaces in front.
Not sure what to do with this. Is it possible that there are not spaces in the value but some other un-readable characters? If so what would be the correct code? I am using Active State Perl on a Windows 2000 machine. Thank you.
Hello all
im facing some kind of problem i have this string :
functionA() $" "$ functionB("arg1") $" = "$
i will like to replace all the pairs of opening and closing "$" to be something like that
functionA() <#" "#> functionB("arg1") <#" = "#>
i cant of course do is with simple ... (1 Reply)
Hi All,
I have a file that I need to be able to find a pattern match on a line, search that line for a text pattern, and replace that text.
An example of 4 lines in my file is:
1. MatchText_randomNumberOfText moreData ReplaceMe moreData
2. MatchText_randomNumberOfText moreData moreData... (4 Replies)
I have a file where the rows correspond to individuals and the columns are about 106 variables. Each variable is coded as either ACGT, and "missing" is coded as blank. This is a tab delimited file. I'm trying to replace all blanks (" ") with 0. The simple script I have is only replacing some of the... (3 Replies)
Hi. I have a file with asterisk field separators and backslash line terminators. The first field in each line names the line type. I am trying to process each range separately. Here's what the data looks like:
BA*DATA\
LS*DATA1*DATA2*00020*\
TA*DATA1*DATA2*DATA3*\
TA*DATA1*DATA2*DATA3*\... (1 Reply)
I am not sure if this is doable. I am trying to open and print the content of the file by replacing all instances fo perl to PERL . This is my code but it is
giving me the number count instead of the actual lines with changes.
open (PERLHISTORY, 'sample.txt') or die "The file sample.txt could... (3 Replies)
I am trying to write a simple perl script to run on a FreeBSD machine.
There are alot of posts here, and I have read so many, yet can not get this script to run.
#!/usr/bin/perl
-e 's/\r\n/~/' infile.txt outfile.txt
I am trying to take a windows text file, move it into Unix, run a script on... (1 Reply)
Hi,
I have a file having around 30 records. Each record has 5 fields delimited by PIPE. Few records in the file having Junk characters in the field2 and field4.
I found the junk charcter and I tested it and replace the junk with space with the command below
perl -i -p -e "s/\x00/ /g"... (1 Reply)
When I use special characters the command to replace multiple files with a string pattern does nt work.
---------- Post updated at 12:33 PM ---------- Previous update was at 11:38 AM ----------
This works
perl -pi -e 's/100/test/g' *
This does nt work
perl -pi -e 's... (1 Reply)
Hi All,
i can replace a perticular value in sentence using perl.
perl -pi -e 's/old/new/' sample.txt
but i am not able to replace whole string by perl.
file1 contains "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.147.109.211)(PORT=1526))(CONNECT_DATA=(SID= MWDBD22)))". i... (3 Replies)
How can I achieve this? Perl would be awesome.
Input string a_b c //Note there is a blank here
Needed Output a_b_c
Thanks (4 Replies)
Discussion started by: dragonpoint
4 Replies
LEARN ABOUT DEBIAN
spreadsheet::xlsx::utility2007
Spreadsheet::XLSX::Utility2007(3pm) User Contributed Perl Documentation Spreadsheet::XLSX::Utility2007(3pm)NAME
Spreadsheet::XLSX::Utility2007 - Utility function for Spreadsheet::XLSX
SYNOPSIS
use strict;
#Declare
use Spreadsheet::XLSX::Utility qw(ExcelFmt ExcelLocaltime LocaltimeExcel);
#Convert localtime ->Excel Time
my $iBirth = LocaltimeExcel(11, 10, 12, 23, 2, 64);
# = 1964-3-23 12:10:11
print $iBirth, "
"; # 23459.5070717593
#Convert Excel Time -> localtime
my @aBirth = ExcelLocaltime($iBirth, undef);
print join(":", @aBirth), "
"; # 11:10:12:23:2:64:1:0
#Formatting
print ExcelFmt('yyyy-mm-dd', $iBirth), "
"; #1964-3-23
print ExcelFmt('m-d-yy', $iBirth), "
"; # 3-23-64
print ExcelFmt('#,##0', $iBirth), "
"; # 23,460
print ExcelFmt('#,##0.00', $iBirth), "
"; # 23,459.51
print ExcelFmt('"My Birthday is (m/d):" m/d', $iBirth), "
";
# My Birthday is (m/d): 3/23
DESCRIPTION
Spreadsheet::XLSX::Utility exports utility functions concerned with Excel format setting.
ExcelFmt is used by Spreadsheet::XLSX::Fmt2007.pm which is used by Spreadsheet::XLSX.
Functions
This module can export 3 functions: ExcelFmt, ExcelLocaltime and LocaltimeExcel.
ExcelFmt
$sTxt = ExcelFmt($sFmt, $iData [, $i1904]);
$sFmt is a format string for Excel. $iData is the target value. If $flg1904 is true, this functions assumes that epoch is 1904. $sTxt is
the result.
For more detail and examples, please refer sample/chkFmt.pl in this distribution.
ex.
ExcelLocaltime
($iSec, $iMin, $iHour, $iDay, $iMon, $iYear, $iwDay, $iMSec) =
ExcelLocaltime($iExTime [, $flg1904]);
ExcelLocaltime converts time information in Excel format into Perl localtime format. $iExTime is a time of Excel. If $flg1904 is true,
this functions assumes that epoch is 1904. $iSec, $iMin, $iHour, $iDay, $iMon, $iYear, $iwDay are same as localtime. $iMSec means
1/1,000,000 seconds(ms).
LocaltimeExcel
$iExTime = LocaltimeExcel($iSec, $iMin, $iHour, $iDay, $iMon, $iYear [,$iMSec] [,$flg1904])
LocaltimeExcel converts time information in Perl localtime format into Excel format . $iSec, $iMin, $iHour, $iDay, $iMon, $iYear are same
as localtime.
If $flg1904 is true, this functions assumes that epoch is 1904. $iExTime is a time of Excel.
col2int
$iInt = col2int($sCol);
converts a excel row letter into an int for use in an array
This function was contributed by Kevin Mulholland.
int2col
$sCol = int2col($iRow);
convert a column number into column letters NOET: This is quite a brute force coarse method does not manage values over 701 (ZZ)
This function was contributed by Kevin Mulholland.
sheetRef
($iRow, $iCol) = sheetRef($sStr);
convert an excel letter-number address into a useful array address NOTE: That also Excel uses X-Y notation, we normally use Y-X in arrays
$sStr, excel coord (eg. A2).
This function was contributed by Kevin Mulholland.
xls2csv
$sCsvTxt = xls2csv($sFileName, $sRegion, $iRotate);
convert a chunk of an excel file into csv text chunk $sRegions = "sheet-colrow:colrow" (ex. '1-A1:B2' means 'A1:B2' for sheet 1) $iRotate
= 0 or 1 (output should be rotated or not)
This function was contributed by Kevin Mulholland.
AUTHOR
Rob Polocz rob.polocz@trackvia.com based on work by for Spreadsheet::ParseExcel by Kawai Takanori (Hippo2000) used with permission
SEE ALSO
Spreadsheet::ParseExcel, Spreadsheet::WriteExcel
COPYRIGHT
This module is part of the Spreadsheet::XLSX distribution.
perl v5.10.1 2010-05-16 Spreadsheet::XLSX::Utility2007(3pm)