06-30-2008
is this one time effort or you have to do this every time?
if its just one time only:
1) export the excel file as csv , choose a suitable delimiter
2) loop through the csv file, construct SQL statements as you go along
3) depending on which database you have, there will usually be some command line database tool to bcp into the database.
if its every time you need to do this: i suggest you use programming languages such as Perl/Python which have libraries to extract data from excel file (automated). Then as usual, construct SQL statements and do your database updating. there may also be command line tools designed to extract excel data as well so you can also use that in your script.
9 More Discussions You Might Find Interesting
1. Shell Programming and Scripting
hi,
i m working on a perl cgi script which uploads a file to the server. i m stuck. i hav written the errors.
plz help.
Sachin Kaw
______________________________________________________________________
#!/usr/bin/perl -w
use CGI;
use CGI qw(:standard);
use strict;
use POSIX... (4 Replies)
Discussion started by: sachin_kaw
4 Replies
2. Shell Programming and Scripting
Hi all
i have a such question this is my shell script my script must upload file to webdav server
//////////////////////////////////////////////////////////////////////////////////////////////////////////////
#!/bin/sh
###... (1 Reply)
Discussion started by: knut
1 Replies
3. Shell Programming and Scripting
hello,
I'm trying to upload a file to this ftp server and others ftp://ftp.byethost12.com as you can see in the output of CURL using the -v option
curl reports that the upload succeeded but when i connected to the server with file-zilla there is no file uploaded the same command
upload files... (5 Replies)
Discussion started by: laraaj
5 Replies
4. Shell Programming and Scripting
Hello,
I have a script that finds the latest version of a file in a folder on my Minecraft server. I'm trying to come up with something that will then FTP that file over to my fileserver.
Here's what I have that finds the newest file:
find /home/mc/archive/sbhouse -type f -mtime +45 -exec... (7 Replies)
Discussion started by: nbsparks
7 Replies
5. Shell Programming and Scripting
Xperts,
My requirement is something like this,
I have a sql script which i need to embed in a shell. The sql (oracle) script will generate a .csv file in some Unix directory. the approximate file size is around 10 mb which i need to upload to an sftp server. My concern here is how to make... (5 Replies)
Discussion started by: Showdown
5 Replies
6. Shell Programming and Scripting
All,
I have an excel sheet Excel1.xls that has some entries.
I have one more excel sheet Excel2.xls that has entries only in those cells which are blank in Excel1.xls
These may be in different workbooks. They are totally independent made by 2 different users.
I have placed them in a... (1 Reply)
Discussion started by: Anamika08
1 Replies
7. Proxy Server
I have a requirement of following -
1. Transfer excel spreadsheet from desktop to unix server
2. Open the spreadsheet and read the sql row by row at a time
3. Run each sql on database using unix and then send output back to spreadsheet that was uploaded earlier and then transfer file back to... (12 Replies)
Discussion started by: Analabhatla
12 Replies
8. Programming
I have some records to be updated in oracle table. I am using sql developer tool. could any one tell me how to update those records in oracle table. I am having excel sheet with those records. (4 Replies)
Discussion started by: ramkumar15
4 Replies
9. UNIX for Dummies Questions & Answers
Hi,
I have requirement for automation, wanna confirm whether is it possible in shell scripting.
1) Need to read data from excel sheet
2) And upload the details in html page
I know first requirement is possible by converting excel into csv form, but not sure about the second one.
If... (6 Replies)
Discussion started by: stew
6 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)