Re: DBD::CSV and skip_first_line

On 11/26/2012 11:56 AM, H.Merijn Brand wrote:
> On Mon, 26 Nov 2012 11:49:49 -0500, "Scott R. Godin" <[email protected]>
> 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" <[email protected]>
>>>> 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

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
>>
>> if I switch to double-quotes rather than single quotes around "key" in
>> the above, I get the following error:
>> Execution ERROR: No such column '"key"' called from clms_test.pl at 23.
>>
>> I'll look into playing with Text::CSV_XS, and see what I can come up with.
>>
>> I still think it would be easier if skip_first_line were not presumed
>> (forced to) false if col_names is set, but rather presumed false only if
>> not set explicitly true.
> We agree, investigating what is actually required (and should be
> documented)
>

-- 
Scott R. Godin, Senior Programmer
MAD House Graphics 
 302.468.6230 - main office
 302.722.5623 - home office