Problems using the DBI class to access CSV files.

------_=_NextPart_001_01C73F3A.B69B32BF
Content-Type: text/plain;
charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

I've been using the DBI class for a while now & I'm trying to port some =
code from a Windows environment where it is working perfectly to a Unix =
environment where it's not really working. I've also reviewed the Perl =
DBI book again & see nothing about this type of problem. It seems that =
it's just the CSV type of database that is having issues.

I'm trying to parse pipe separated log files to search for data so that =
actions can be taken based on what I find in the log files.

A sample log file:

TIME|Status|Description|
17:15:51|OK|No Problems|
17:31:12|Warning|Skipped file abc|
18:45:03|ERROR|Can't read file xyz|

Afraid that it might be a bug in my code, I began using "dbish" to test =
out my queries. But I get the same problems as in my code. I find all =
3 rows in Windows, but I always find Zero rows on the Unix platforms.

On Windows:
M:\...\logs>dbish
DBI::Shell 11.91 using DBI 1.49

WARNING: The DBI::Shell interface and functionality are
=======3D very likely to change in subsequent versions!

Available DBI drivers:
1: dbi:CSV
2: dbi:DBM
3: dbi:ExampleP
4: dbi:File
5: dbi:ODBC
6: dbi:Proxy
7: dbi:Sponge
Enter driver name or number, or full 'dbi:...:...' DSN: =
DBI:CSV:csv_sep_char=3D|

Connecting to 'DBI:CSV:csv_sep_char=3D|' as ''...
@> select * from RMSComprate.060707.log.txt
@> ;
TIME,Status,Description,
'17:15:51','OK','No Problems'.''
'17:31:12','Warning','Skipped file abc',''
'18:45:03','ERROR','Can't read file xyz',''

[3 rows of 4 fields returned]
@>

But if I attempt to do the same thing against AIX Unix, I get
heisys@het-web1:/.../logs> dbish
DBI::Shell 11.93 using DBI 1.52

WARNING: The DBI::Shell interface and functionality are
=======3D very likely to change in subsequent versions!

Available DBI drivers:
1: dbi:CSV
2: dbi:DBM
3: dbi:ExampleP
4: dbi:File
5: dbi:Multiplex
6: dbi:Oracle
7: dbi:Proxy
8: dbi:Sponge
Enter driver name or number, or full 'dbi:...:...' DSN: =
DBI:CSV:csv_sep_char=3D|

Connecting to 'DBI:CSV:csv_sep_char=3D|' as ''...
@> select * from test.log
@> ;
TIME,Status,Description,

[0 rows of 4 fields returned]

Can anyone offer suggestions?

Curtis Leach
Programmer Analyst
Harrah's Entertainment, Inc. - RMS I&AS
Phone: (901) 537-3453
Cell: (901) 573-7973



------_=_NextPart_001_01C73F3A.B69B32BF--

RE: Problems using the DBI class to access CSV files.

Never mind, after writing up the email I noticed the csv_eol tag &
tried setting it to "\n" for grins, and it solved my problem.


Curtis Leach


-----Original Message-----
From: Curtis Leach [mailto:cleach@harrahs.com]=20
Sent: Tuesday, January 23, 2007 4:06 PM
To: dbi-users@perl.org
Subject: Problems using the DBI class to access CSV files.

I've been using the DBI class for a while now & I'm trying to port some
code from a Windows environment where it is working perfectly to a Unix
environment where it's not really working. I've also reviewed the Perl
DBI book again & see nothing about this type of problem. It seems that
it's just the CSV type of database that is having issues.

I'm trying to parse pipe separated log files to search for data so that
actions can be taken based on what I find in the log files.

A sample log file:

TIME|Status|Description|
17:15:51|OK|No Problems|
17:31:12|Warning|Skipped file abc|
18:45:03|ERROR|Can't read file xyz|

Afraid that it might be a bug in my code, I began using "dbish" to test
out my queries. But I get the same problems as in my code. I find all
3 rows in Windows, but I always find Zero rows on the Unix platforms.

On Windows:
M:\...\logs>dbish
DBI::Shell 11.91 using DBI 1.49

WARNING: The DBI::Shell interface and functionality are =
=======3D very
likely to change in subsequent versions!

Available DBI drivers:
1: dbi:CSV
2: dbi:DBM
3: dbi:ExampleP
4: dbi:File
5: dbi:ODBC
6: dbi:Proxy
7: dbi:Sponge
Enter driver name or number, or full 'dbi:...:...' DSN:
DBI:CSV:csv_sep_char=3D|

Connecting to 'DBI:CSV:csv_sep_char=3D|' as ''...
@> select * from RMSComprate.060707.log.txt @> ;
TIME,Status,Description, '17:15:51','OK','No Problems'.''
'17:31:12','Warning','Skipped file abc',''
'18:45:03','ERROR','Can't read file xyz',''

[3 rows of 4 fields returned]
@>

But if I attempt to do the same thing against AIX Unix, I get
heisys@het-web1:/.../logs> dbish DBI::Shell 11.93 using DBI 1.52

WARNING: The DBI::Shell interface and functionality are =
=======3D very
likely to change in subsequent versions!

Available DBI drivers:
1: dbi:CSV
2: dbi:DBM
3: dbi:ExampleP
4: dbi:File
5: dbi:Multiplex
6: dbi:Oracle
7: dbi:Proxy
8: dbi:Sponge
Enter driver name or number, or full 'dbi:...:...' DSN:
DBI:CSV:csv_sep_char=3D|

Connecting to 'DBI:CSV:csv_sep_char=3D|' as ''...
@> select * from test.log
@> ;
TIME,Status,Description,

[0 rows of 4 fields returned]

Can anyone offer suggestions?

Curtis Leach
Programmer Analyst
Harrah's Entertainment, Inc. - RMS I&AS
Phone: (901) 537-3453
Cell: (901) 573-7973

Re: Problems using the DBI class to access CSV files.

Curtis Leach wrote:
> I've been using the DBI class for a while now & I'm trying to port some code from a Windows environment where it is working perfectly to a Unix environment where it's not really working. I've also reviewed the Perl DBI book again & see nothing about this type of problem. It seems that it's just the CSV type of database that is having issues.
>
My strong guess is that you are running into line-ending issues. Make
sure that you know what the line endings are in the CSV file, which will
vary depending on whether the file was created on doze or *nix. Set the
line ending (csv_eol) either in the connect or in the database handle:

my $dbh = DBI->connect( 'dbi:CSV' ... { csv_eol => "\012" }

DBD::CSV defaults to windows line endings ("\015\012"). DBD::CSV can
use either line ending (or other eols) on either platform, as long as
it's the correct line ending for the data file being used which may
depend on whether or not you transferred the file from one platform to
another using a line-ending aware process.

--
Jeff

> I'm trying to parse pipe separated log files to search for data so that actions can be taken based on what I find in the log files.
>
> A sample log file:
>
> TIME|Status|Description|
> 17:15:51|OK|No Problems|
> 17:31:12|Warning|Skipped file abc|
> 18:45:03|ERROR|Can't read file xyz|
>
> Afraid that it might be a bug in my code, I began using "dbish" to test out my queries. But I get the same problems as in my code. I find all 3 rows in Windows, but I always find Zero rows on the Unix platforms.
>
> On Windows:
> M:\...\logs>dbish
> DBI::Shell 11.91 using DBI 1.49
>
> WARNING: The DBI::Shell interface and functionality are
> ======= very likely to change in subsequent versions!
>
> Available DBI drivers:
> 1: dbi:CSV
> 2: dbi:DBM
> 3: dbi:ExampleP
> 4: dbi:File
> 5: dbi:ODBC
> 6: dbi:Proxy
> 7: dbi:Sponge
> Enter driver name or number, or full 'dbi:...:...' DSN: DBI:CSV:csv_sep_char=|
>
> Connecting to 'DBI:CSV:csv_sep_char=|' as ''...
> @> select * from RMSComprate.060707.log.txt
> @> ;
> TIME,Status,Description,
> '17:15:51','OK','No Problems'.''
> '17:31:12','Warning','Skipped file abc',''
> '18:45:03','ERROR','Can't read file xyz',''
>
> [3 rows of 4 fields returned]
> @>
>
> But if I attempt to do the same thing against AIX Unix, I get
> heisys@het-web1:/.../logs> dbish
> DBI::Shell 11.93 using DBI 1.52
>
> WARNING: The DBI::Shell interface and functionality are
> ======= very likely to change in subsequent versions!
>
> Available DBI drivers:
> 1: dbi:CSV
> 2: dbi:DBM
> 3: dbi:ExampleP
> 4: dbi:File
> 5: dbi:Multiplex
> 6: dbi:Oracle
> 7: dbi:Proxy
> 8: dbi:Sponge
> Enter driver name or number, or full 'dbi:...:...' DSN: DBI:CSV:csv_sep_char=|
>
> Connecting to 'DBI:CSV:csv_sep_char=|' as ''...
> @> select * from test.log
> @> ;
> TIME,Status,Description,
>
> [0 rows of 4 fields returned]
>
> Can anyone offer suggestions?
>
> Curtis Leach
> Programmer Analyst
> Harrah's Entertainment, Inc. - RMS I&AS
> Phone: (901) 537-3453
> Cell: (901) 573-7973
>
>
>
>