Greetings, Monks! I am having couple of troubles with using DBI for CSV files.

1. I am unable to get the BETWEEN operation to work, the usual syntax is something like: "Where column between x and y" but somehow, this does not seem to work.

2. How do I work with dates in a CSV file? Is there a quick hack or some trick that I can use?

Thanking in anticipation.

Please find the code, I have used for testing below:

use strict;
use DBI;

    my $query_string = "SELECT * FROM perfmon WHERE Date_and_Time BETW
+EEN '7034' AND '7037'";    
    my $csv_file_name = "perfdata.csv";
    my $csv_dbh;
    my $stmt_handle;
    
    $@ = "";
    eval{
            $csv_dbh =
                    DBI->connect( "DBI:CSV:f_dir=.",
                                    {
                                        PrintError => 1,
                                        RaiseError => 1,
                                        AutoCommit => 0
                                    }
                                );
            print "\n$csv_file_name\n";
            $csv_dbh -> {csv_tables} -> {perfmon} =
                            { 
                                file => $csv_file_name,
                            };
            print "\n$query_string\n";
            $stmt_handle =
                    $csv_dbh -> prepare("
                                                    $query_string
                                                ");
                    
            $stmt_handle -> execute();
            while ( my @row = $stmt_handle->fetchrow_array() ) {
                        my $rowstr = join(";", @row);
                        print $rowstr,"\n";
            }
            $stmt_handle -> finish(); $csv_dbh -> disconnect();
        };
    $@ and die "\n\nSQL database error: $@\n";
    
__END__
#Contents of the CSV file.
Level,Date_and_Time,Source,Event_ID,Task_Category,Status
'Information',26/01/2010 20:05:51,'Service Control Manager',7036,None,
+'The Multimedia Class Scheduler service entered the running state.'
'Information',26/01/2010 20:05:41,'Service Control Manager',7036,None,
+'The WMI Performance Adapter service entered the stopped state.'
'Information',27/01/2010 20:02:36,'Service Control Manager',7036,None,
+'The Application Experience service entered the running state.'
'Information',27/01/2010 20:02:35,'Service Control Manager',7036,None,
+'The WMI Performance Adapter service entered the running state.'
'Information',28/01/2010 20:01:25,'Service Control Manager',7036,None,
+'The Application Information service entered the running state.'

#command line used:
perl perflogquery.pl

#Output
perfdata.csv

SELECT * FROM perfmon WHERE Date_and_Time BETWEEN '7034' AND '7037'

DBD::CSV::st execute failed: Not an ARRAY reference at C:/Perl/site/li
+b/SQL/Statement/Operation.pm line 438, <GEN0> line 2.
 [for Statement "
        SELECT * FROM perfmon WHERE Date_and_Time BETWEEN '7034' AND '
+7037'
                "] at perflogquery.pl line 42.
DBD::CSV::st fetchrow_array failed: Attempt to fetch row without a pre
+ceeding execute () call or from a non-SELECT statement [for Statement
+ "
            SELECT * FROM perfmon WHERE Date_and_Time BETWEEN '7034' A
+ND '7037'
                "] at perflogquery.pl line 48.
[download]

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.