On Mon, 26 Nov 2012 12:29:25 -0500, "Scott R. Godin" <scot...@mhg2.com> wrote:
> On 11/26/2012 11:56 AM, H.Merijn Brand wrote: > > On Mon, 26 Nov 2012 11:49:49 -0500, "Scott R. Godin" <scot...@mhg2.com> > > wrote: > > > > On 11/25/2012 04:16 AM, Jens Rehsack wrote: > >>> On 25.11.12 10:00, H.Merijn Brand wrote: > >>>> On Fri, 23 Nov 2012 17:43:50 -0500, "Scott R. Godin" <scot...@mhg2.com> > >>>> wrote: > >>>>> I've run into an issue where I need both col_names set and > >>>>> skip_first_line still set to TRUE, because of malformed colnames in the > >>>>> original dumpfiles that conflict with SQL Reserved Words (such as > >>>>> 'key') > >>>>> that I am unable to find any other acceptable workaround short of > >>>> Why not automate the hacking using Text::CSV_XS and rewrite the header > >>>> before using DBD::CSV? > >>> Or simply quote the column names in your SQL statement? > >> I tried various quoting mechanisms up to and including attempting to use > >> backticks, but all result in errors of one kind or another > > Can you attach the first 4 lines of your csv datafile? > Unfortunately, no, as I am under HIPAA restrictions. > > key consists of seemingly random alphanumeric [A-Z0-9] sequences that > may or may not contain one dash (at about position 11), of 16-char length > PHM_ID consists of P\d{7} and may repeat across records > of the other two fields DAW_CD is numeric(1) and GENBRND_CD is boolean > all records are pipe-delimited Well, in that case, I'd use Text::CSV_XS to rewrite the data before using DBD::CSV to use it. I do sorta the same on a regular basis, as my customers mak typos in the headers and change them all the time. In any case, "key" quoted/unquoted has no magical value at all. The key-name is what you pass in in column_names or in the first line. That the first line should be skippable when specifying your own column names is already agreed on. > The actual csv contains 44 columns; in the interest of brevity I limited > the sample to the below four. :) > >> $dbh->prepare(q{Select 'key', PHM_ID, DAW_CD, GENBRND_CD from clms limit > >> 10}) > >> results in every record having the literal value "key" for the column `key` > >> same if I try select 'key' as PKEY --8<--- example of CSV rewrites written without checking from top of my head my $csv_o = Text::CSV_XS->new ({ binary => 1, auto_diag => 1, eol => "\n", }); my $csv_i = Text::CSV->new ({ binary => 1, auto_diag => 1, sep_char => "|", # | often requires allow_white_space => 1 }); my %hdr; while (<DATA>) { m/^#/ and next; my ($fld, $text) = m/^(\S+)\s+(.*)/ or next; $hdr{$text} = $fld; } my @hdr; for (@{$csv_i->getline ($fh)}) { if (exists $hdr{$_}) { push @hdr, $hdr{$_}; next; } # special hardcoded fields if (m/^[A-Za-z]\w{0,7}-?\w{1,12}$/) { push @hdr, "key"; # This is a key? next; } die "I do not know how to translate '$_' to something useful"; } my %rec; $csv_i->bind_columns (\@rec{@hdr}); $csv_o->print ($out, \@hdr); while ($csv_i->getline ($fh)) { $csv_o->print (\@rec{@hdr}); } __END__ # Wanted Seen in their shit c_foo code foo c_foo kode foo c_foo kode-foo c_foo kode_foo foo description of foo foo omschrijving foo foo omschr. foo -->8--- -- H.Merijn Brand http://tux.nl Perl Monger http://amsterdam.pm.org/ using perl5.00307 .. 5.17 porting perl5 on HP-UX, AIX, and openSUSE http://mirrors.develooper.com/hpux/ http://www.test-smoke.org/ http://qa.perl.org http://www.goldmark.org/jeff/stupid-disclaimers/