Anyway, here we go. I'm afraid I can't EXACTLY replicate your desired output as
- it is NOT an insert (as requested) but an update statement
- the case of line 1 field 2 & 3 don't match the output and can't be made up THAT easy.
On top, it's NOT easy (again) to extract from EXCEL immediately although there seems to exist a perl module therefor. I'll assume you created a <TAB> delimited text file instead.
If you're fine with an approximation, try
If you don't want the header info but the data, replace NR == 1 with NR > 1 or drop it entirely if you want ALL lines represented.
Guys,
I want to insert some values from a log file into a temporary table. the values in the log file looks like this
SV_NAME CC_NAME CP_DISP_RUN_STATE
------- ------------------- -----------------
sble01 ALLTEL WorkMon24Hrs Running
I want to enter the... (2 Replies)
how to assign values in this date format
a=`date +"%H%M%S"`
how to give value of H=22,here in this format so that i can grep 22nd hour.Below is the script
--------------------------------------------------------------------------
a=`date +"%H%M%S"`
for i in *.log
do
cat $i | grep $a... (3 Replies)
I am trying to insert a picture into a worksheet in Excel using Perl the following is the code
use Win32::OLE;
use Win32::OLE::Const "Microsoft Excel";
use Win32::OLE qw(in with);
# Initiate Excel application
$Excel = Win32::OLE->new('Excel.Application', 'Quit');
$Excel->{Visible} =1;
#... (1 Reply)
Hello viewers,
I am connecting to db2 database from unix. I am executing a query for example
select * from emp.
I need to export the data obtained from the above command to a excel file. Could you please suggest how to proceed on this?
thanks and regards,
k.n.v.santosh (3 Replies)
Here's the problem.
I have a shell script running for every one minute as cronjob that outputs to a log file. I want to be able to take the data from the log file and insert it into a mysql database. The script needs to run at a set period of time and remove the log file data once the insert is... (7 Replies)
Hi All,
I have a directory containing multiple files.
and also a txt file which contains the list of all filenames and certain values.
I want to read the text file line by line and if its 2nd column is matched with the filename in directory, then it must insert the values in 7th column to... (14 Replies)
Hi,
Please help me on this.
I want to insert data from text file to excel using shell script
nawk -v r=4 -v c=4 -v val=$a -F, 'BEGIN{OFS=","}; NR != r; NR == r {$c = val; print}' "file.csv"
I used above one to insert $a value in 4th row, 4th column in an excel file.csv and it... (3 Replies)
hi all,
i have one shell script like this
#!/bin/bash -xv
ENV_NAME=`cat $IB_HOME_DIR/cfg/ibProfile.sh | grep "RDM_CONN" | cut -f 2 -d "@"`
CURRENT_DIR=`pwd`;
string=$IB_HOME_DIR
string1="$string/FRGFOLDER/input"
#sed "s/string3/$string1" frg_event_src.sql > modifiedinsert.sql
sqlplus... (2 Replies)
I have a script working out ink percentages
#!/bin/bash
currentcyan=$'iso.3.6.1.2.1.43.11.1.1.9.1.1 = INTEGER: 990'
currentmagenta=$'iso.3.6.1.2.1.43.11.1.1.9.1.2 = INTEGER: 972'
currentyellow=$'iso.3.6.1.2.1.43.11.1.1.9.1.3 = INTEGER: 972'
currentblack=$'iso.3.6.1.2.1.43.11.1.1.9.1.4 =... (1 Reply)
hi ,
I'm new to Unix shell scripting.
I need help to insert read csv which has two columns -emp no and logged date.
csv file is a large file so i want to keep the insertion query in a separate .sql file.
csv file looks this:
empno | loggeddate
___________________
5666 ,... (5 Replies)
Discussion started by: preema
5 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)