Re: Loading Fixed Length ASCII / EBCDIC
On 4 Jan 1997 06:53:16 -0500, cdr@xteam.co.at (Chris Drauch) wrote:
>John Cokos wrote
>>
>>Importing ASCII Files
>> I am able to convert and load from Delimited Ascii, and DBF files via 4gl
>> and Perl...
>> but I am having a lot of difficulty with Fixed Length and EBCDIC Files.
>> Has anyone solved this?
>
>Yes - > How?
>
>------------cut here ----------------------------------------------------------
>#! /usr/local/bin/isqlperl
>
># open for input
>unless (open(DD,"dd if=ebcdic.file conv=ascii 2>/dev/null|")) {
> printf (STDERR "Can not open dd for input\\n");
> exit(1);
>}
>
>++$isql_autoclose;
>&isql_execute("database test") || die $isql_code;
>&isql_execute("begin work") || die $isql_code;
>
>while (<DD>)
>{
>
> chop;
> $key = substr($_,0,9);
> $field = substr($_,9,20);
> $value = substr($_,29,10);
>
> &isql_execute(sprintf(
> "insert into test values(\\"%s\\",\\"%s\\",\\"%s\\")\\n",
> $key,$field,$value)) || die $isql_code;
>
>}
>
>&isql_execute("commit work") || die $isql_code;
>--------------------cut here---------------------------------------------------
>
>We use isqlperl to insert directly to the database; I you don't have that great
>tool :) you could easily write records for an unload file.
>
>> Is there some common utility for this that I am not seeing?
>Dunno.
>
>Salute,
>--
>Chris Drauch <cdr@xteam.co.at> ... just speaking for myself ...
The above isqlperl stuff is cool, but if you have acces to 4gl you
should have a utility called dbload that comes with Informx.
This utility is designed specificaly to handle this type of thing. It
is explained in detail in the 4gl ref. manual vol. 2. In short, it
will let you define rules for loading fixed length records in to a
table in any order you need and with any default or hard-coded values
you may want. It also gives you control of when to commit the
inserted row(s) and how many errors to allow before termination and
will place bad rows in an error file. Great tool... I haven't run
into a situation yet where I can't use it. A technique I use a lot
when I have data that wont load straight into a table is to laod the
data into a "temporary" table as one char column the length of the
row and then select out my individual columns out of this and work on
them and insert after I've checked and fixed everything. This may not
work for the EBCDIC issue, but the dd mentioned above should handle
ebcdic conversions. Let me know if you need further info.
-robw