My target is to send a file created by Unix process to myself as an excel file. So I have used the below commands to achieve it.
After receiving the file on my mail box, I save it in Windows machine. I try to extract the archive using 7zip. It attempts to extract and says Data error in file.xls. File is broken
Hello,
Im trying to extract a portion of a big file.
Using unique pattern /occurrence ,
(ex. loginname1,logoff and loginname2,logoff ),
I like to print the lines that contain the patterns and the lines between them.
Also, create a file for every login occurrence.
Thanks for everyone's... (1 Reply)
Hi, everyone,
Now I have a ".csv" file and I want to extract a string-like date value from the file, say, it is in the 2nd row of the file and it is in the fixed context like:
"
This is the file title, //Row 1
The lastest update happened on: 10-Mar-2006 //Row2
....//other irrelavent rows
"... (6 Replies)
Dear Members,
I have a huge file generated by the command 'whois' for hundred of IPs. Each section in the file starts with
I want to extract those lines which start with any of these words: Input:
OrgName: University of C
OrgID: U1
Address: OIT
Address: NH
City: ... (5 Replies)
Hi all,
I'm new in the unix environment. I'm having a challenge in extracting data from a flat file and convert it to a CSV file format or I should be able to open it with MS Excel.
The input data in my flat file looks like this:
AV00001001155000063637143326711145412082EM SITHOLE... (3 Replies)
Hi All,
I have a requirement where I have to search the file with some text say "Exception". This exception word can be repeated for more then 10 times.
Suppose the "Exception" word is repeated at line numbers say x=10, 50, 60, 120. Now I want to extract all the lines starting from x-5 to... (3 Replies)
Hello everybody,
Here is my problem : I cannot find a way to extract data from a particular file and more precisely I cannot extract the result of my awk script to an external file because I am currently working on HP-UX.
I would like a simple script (without awk) which asks for a date like... (4 Replies)
Hi,
I have three files as below:
AA.DAT20110505063903.Z
AA.DAT20110405062903.Z
AA.DAT20110305061903.Z
All the above files are appended with Date and timestamp in compressed format.
I need to extract AA.DAT20110505063903.Z(which is the latest file) from one server and uncompress it... (2 Replies)
I need to extract some content of a file.
Example
file abc
vi abc
ooooooooo
bbbbbbbbb
vvv 1234 5
vvv 6789 3
xxxxxxxxxx
xxxxxxxxxx
i want to extract only the following content from file abc and store in another file say temp.
1234 5
6789 3
what should be my approach? (2 Replies)
Hi Freinds,
I have a file1.txt in the following format
File1.txt
I want to get 2 files from the above file filextra.txt should have the lines which are ending with "<" and remaining lines in the filecompare.txt file.
Please help. (3 Replies)
FILE_ID extraction from file name and save it in CSV file after looping through each folders
My files are located in UNIX Server, i want to extract file_id and file_name from each file .and save it in a CSV file. How do I do that?
I have folders in unix environment, directory structure is... (15 Replies)
Discussion started by: princetd001
15 Replies
LEARN ABOUT DEBIAN
dbd::excel
Excel(3pm) User Contributed Perl Documentation Excel(3pm)NAME
DBD::Excel - A class for DBI drivers that act on Excel File.
This is still alpha version.
SYNOPSIS
use DBI;
$hDb = DBI->connect("DBI:Excel:file=test.xls")
or die "Cannot connect: " . $DBI::errstr;
$hSt = $hDb->prepare("CREATE TABLE a (id INTEGER, name CHAR(10))")
or die "Cannot prepare: " . $hDb->errstr();
$hSt->execute() or die "Cannot execute: " . $hSt->errstr();
$hSt->finish();
$hDb->disconnect();
DESCRIPTION
This is still alpha version.
The DBD::Excel module is a DBI driver. The module is based on these modules:
* Spreadsheet::ParseExcel
reads Excel files.
* Spreadsheet::WriteExcel
writes Excel files.
* SQL::Statement
a simple SQL engine.
* DBI
Of course. :-)
This module assumes TABLE = Worksheet. The contents of first row of each worksheet as column name.
Adding that, this module accept temporary table definition at "connect" method with "xl_vtbl".
ex.
my $hDb = DBI->connect(
"DBI:Excel:file=dbdtest.xls", undef, undef,
{xl_vtbl =>
{TESTV =>
{
sheetName => 'TEST_V',
ttlRow => 5,
startCol => 1,
colCnt => 4,
datRow => 6,
datLmt => 4,
}
}
});
For more information please refer sample/tex.pl included in this distribution.
Metadata
The following attributes are handled by DBI itself and not by DBD::Excel, thus they all work like expected:
Active
ActiveKids
CachedKids
CompatMode (Not used)
InactiveDestroy
Kids
PrintError
RaiseError
Warn (Not used)
The following DBI attributes are handled by DBD::Excel:
AutoCommit
Always on
ChopBlanks
Works
NUM_OF_FIELDS
Valid after "$hSt->execute"
NUM_OF_PARAMS
Valid after "$hSt->prepare"
NAME
Valid after "$hSt->execute"; undef for Non-Select statements.
NULLABLE
Not really working, always returns an array ref of one's. Valid after "$hSt->execute"; undef for Non-Select statements.
These attributes and methods are not supported:
bind_param_inout
CursorName
LongReadLen
LongTruncOk
Additional to the DBI attributes, you can use the following dbh attribute:
xl_fmt
This attribute is used for setting the formatter class for parsing.
xl_dir
This attribute is used only with "data_sources" on setting the directory where Excel files ('*.xls') are searched. It defaults to the
current directory (".").
xl_vtbl
assumes specified area as a table. See sample/tex.pl.
xl_skiphidden
skip hidden rows(=row height is 0) and hidden columns(=column width is 0). See sample/thidden.pl.
xl_ignorecase
set casesensitive or not about table name and columns. Default is sensitive (maybe as SQL::Statement). See sample/thidden.pl.
Driver private methods
data_sources
The "data_sources" method returns a list of '*.xls' files of the current directory in the form "DBI:Excel:xl_dir=$dirname".
If you want to read the subdirectories of another directory, use
my($hDr) = DBI->install_driver("Excel");
my(@list) = $hDr->data_sources(
{ xl_dir => '/usr/local/xl_data' } );
list_tables
This method returns a list of sheet names contained in the $hDb->{file}. Example:
my $hDb = DBI->connect("DBI:Excel:file=test.xls");
my @list = $hDb->func('list_tables');
TODO
More tests
First of all...
Type and Format
The current version not support date/time and text formating.
Joins
The current version of the module works with single table SELECT's only, although the basic design of the SQL::Statement module allows
joins and the likes.
KNOWN BUGS
o There are too many TODO things. So I can't determind what is BUG. :-)
AUTHOR
Kawai Takanori (Hippo2000) kwitknr@cpan.org
Homepage:
http://member.nifty.ne.jp/hippo2000/ (Japanese)
http://member.nifty.ne.jp/hippo2000/index_e.htm (English)
Wiki:
http://www.hippo2000.net/cgi-bin/KbWiki/KbWiki.pl (Japanese)
http://www.hippo2000.net/cgi-bin/KbWikiE/KbWiki.pl (English)
SEE ALSO
DBI, Spreadsheet::WriteExcel, Spreadsheet::ParseExcel, SQL::Statement
COPYRIGHT
Copyright (c) 2001 KAWAI,Takanori All rights reserved.
You may distribute under the terms of either the GNU General Public License or the Artistic License, as specified in the Perl README file.
perl v5.8.8 2008-03-01 Excel(3pm)