Problem parsing semicolon delimited file with DBD::CSV

sxmwb has asked for the wisdom of the Perl Monks concerning the following question:

I am trying to use DBD::CSV to read a file with semicolon delimited.

Data looks like:

Date;Time;Station;Frq;Mode;Pwr;Conf;Sent;Rcvd;End;Name;QTH;Remarks;S;R
+;DXCC;STX;SRX;Mult;Pts;ITU;CQ;ContID;Pfx;Cont;Freq;RXpwr;State;Prov;C
+ounty;QSL Via;Age;Grid;QslRdt;QslSdt;Sect;Iota;Prop;Qslmsg;Satmod;Sat
+nm;Tenten;Addr;Notes;Submode;DOK;OBL;JCC;JCG;BandRX;QRP;SWL;Cmpl;QSO#
+;Lat;Lon;eqslS;eqslR;eqslS_Dt;eqslR_Dt;lotwS;lotwR;lotwS_Dt;lotwR_Dt;
+Rig;Az;El;Eq;FI;MB;MSSH;Bursts;Pings;Op;Stn;Ownr;Sig;SigInf;Key;Confi
+g;Email;S_via;R_via;Q;ud1;ud2;Sp4;Sp3;Sp2;Sp1
2005-10-29;21:34:00;VC3O;14;SSB;;MWB;59;59;;;;;N;N;1;4;4;;;12;09;;VC3;
+NA;;;;;;;;;;;;;;;;;;;;;;;;;;0;; ;0001;;;Y;Y;;;Y;N;;;;;;;N;;;;;;;;;;;;
+;E;;N;;;;;;
2005-10-29;21:41:00;NY1Q;14;SSB;;MWB;59;59;;;;;N;N;291;4;5;;;8;5;;NY1;
+NA;;;RI;;Washington;;;;;;;;;;;;;;;;;;;;;0;; ;0002;;;Y;N;;;Y;N;;;;;;;N
+;;;;;;;;;;;;;;;N;;;;;;
2005-10-29;21:47:00;NQ4I;14;SSB;;MWB;59;59;;;;;N;N;291;4;5;;;8;;;NQ4;N
+A;;;GA;;Spalding;;;;;;;;;;;;;;;;;;;;;0;; ;0003;;;Y;N;;;Y;N;;;;;;;N;;;
+;;;;;;;;;;;;N;;;;;;
2005-10-29;21:57:00;K3LR;14;SSB;;MWB;59;59;;;;;N;N;291;4;5;;;8;5;;K3;N
+A;;;PA;;Mercer;;;;;;;;;;;;;;;;;;;;;0;; ;0004;;;Y;Y;;;Y;Y;;2006-04-14;
+;;;;N;;;;;;;;;;;;;E;;N;;;;;;
[download]

Code:

#!/usr/local/bin/perl
use strict;
use warnings;

use DBI;

my $dbh DBI->connect('dbi:AnyData(RaiseError=>1):','csv_sep_char=\;');
$dbh->func( 'callsigns',
              'CSV',
             'C:\\Documents and Settings\\My Documents\\MyProjects\\Pa
+rsingXML\\log.jdb',
                'ad_catalog');
my $sth = $dbh->prepare("SELECT Station, Date, Time FROM callsigns");
$sth->execute();
while (my $row = $sth->fetch) {
       print "@$row\n";
}
[download]

and I get the following error messages when the program runs and obviously do not understand them:

Execution ERROR: Can't use string ("CAN'T FIND COLUMN NAMES ON FIRST")
+ as an ARR
AY ref while "strict refs" in use at C:/Perl/site/lib/AnyData.pm line 
+126, <GEN0
> line 2.
.
DBD::AnyData::st execute failed:
Execution ERROR: Can't use string ("CAN'T FIND COLUMN NAMES ON FIRST")
+ as an ARR
AY ref while "strict refs" in use at C:/Perl/site/lib/AnyData.pm line 
+126, <GEN0
> line 2.
.

 [for Statement "SELECT Station, Date, Time FROM callsigns"] at C:\DOC
+UME~1\N7DQ
\MYDOCU~1\MYPROJ~1\PARSIN~1\TEST-C~1.PL line 13.
DBD::AnyData::st execute failed:
Execution ERROR: Can't use string ("CAN'T FIND COLUMN NAMES ON FIRST")
+ as an ARR
AY ref while "strict refs" in use at C:/Perl/site/lib/AnyData.pm line 
+126, <GEN0
> line 2.
.

 [for Statement "SELECT Station, Date, Time FROM callsigns"] at C:\DOC
+UME~1\N7DQ
\MYDOCU~1\MYPROJ~1\PARSIN~1\TEST-C~1.PL line 13.

C:\Documents and Settings\N7DQ\My Documents\MyProjects\ParsingXML>
[download]

Any ideas what I am doing wrong?

Thanks
Mike

20060727 Janitored by Corion: Added formatting, code tags, as per Writeup Formatting Tips

Comment on Problem parsing semicolon delimited file with DBD::CSV Select or Download Code

Replies are listed 'Best First'.
Re: Problem parsing semicolon delimited file with DBD::CSV by ikegami (Patriarch) on Jul 27, 2006 at 02:42 UTC
Your title says you're using DBD::CSV, but you're using DBD::AnyData. It's no suprise that DBD::CSV's `csv_sep_char` doesn't work if you're not using DBD::CSV. You should be using `my $dir = "C:\\Doc...\\ParsingXML"; my $dbh = DBI->connect("dbi:CSV:f_dir=$dir;csv_sep_char=\\;"); my $sth = $dbh->prepare("SELECT Station, Date, Time FROM log");` [download] (Note: That opens `C:\Doc...\ParsingXML\log`. Not sure how to open `C:\Doc...\ParsingXML\log.jdb`.) Update: It is not possible to specify an alternate sperator with DBD::AnyData. The only options for AnyData's CSV handler are col_names, eol, quotechar and escape_char.	[reply] [d/l] [select]
Re^2: Problem parsing semicolon delimited file with DBD::CSV by jZed (Prior) on Jul 27, 2006 at 03:48 UTC
I'll have to look at the docs to see what gave you that impression, but you're wrong, you can also specify the sep_char, see my answer below. update Well having looked at the docs, I see that the reason you got that impression is because I didn't mention anything about it :-(. I'll correct that in the next release, thanks for causing me to look.	[reply]
Re: Problem parsing semicolon delimited file with DBD::CSV by jZed (Prior) on Jul 27, 2006 at 03:46 UTC
Your problem is your connect statement. It has several problems. In the first place you omitted the second and third parameters (username and password). In the second place you neglected to put curly braces around the hash reference of attributes. In the third place you used a backslash before the semicolon but inside single quote marks which will not inerpolate the backslash. You made the same mistake with backslashes inside single quotes in your call to ad_catalog. Here's how you should connect and pass the arguments: `my $dbh = DBI->connect('dbi:AnyData(RaiseError=>1):'); $dbh->func( 'callsigns', 'CSV', 'C:/Documents and Settings/My Documents/MyProjects/ParsingXML/log +.jdb', { sep_char => ';' }, 'ad_catalog' );` [download]	[reply] [d/l]
Re^2: Problem parsing semicolon delimited file with DBD::CSV by sxmwb (Pilgrim) on Jul 27, 2006 at 23:59 UTC
Thanks for the information, I knew the samples I was using would not get me to the exact place. I made the suggested changes and still get the same error as above. I find through some research that it is not figuring out the columns from the first row. Anything else I can check? Thanks - Mike	[reply]
Re^3: Problem parsing semicolon delimited file with DBD::CSV by ikegami (Patriarch) on Jul 28, 2006 at 00:09 UTC
try using `field_sep` instead of `sep_char`. I can't find any mention of the latter in the sources.	[reply] [d/l] [select]
Re^4: Problem parsing semicolon delimited file with DBD::CSV by sxmwb (Pilgrim) on Jul 28, 2006 at 01:08 UTC
Re^3: Problem parsing semicolon delimited file with DBD::CSV by jZed (Prior) on Jul 28, 2006 at 15:03 UTC
Try to also directly set the eol character to "\n". Try to change the names of Date and Time (because they are SQL keywords). I ran your code with your data and the changes I suggested above and it worked for me.	[reply]
Re^4: Problem parsing semicolon delimited file with DBD::CSV by sxmwb (Pilgrim) on Jul 30, 2006 at 14:31 UTC
Re^5: Problem parsing semicolon delimited file with DBD::CSV by ikegami (Patriarch) on Jul 30, 2006 at 16:02 UTC
Re^5: Problem parsing semicolon delimited file with DBD::CSV by jZed (Prior) on Jul 30, 2006 at 17:16 UTC
Re: Problem parsing semicolon delimited file with DBD::CSV by jdtoronto (Prior) on Jul 27, 2006 at 02:29 UTC
sxmwb, I have no experience working with DBD::CSV but one thing that struck me - Date and Time are not valid column names in SQL (at least the SQL I know) because they are data types. Maybe that could be true here as well? jdtoronto	[reply]