SEO

#1 (**permalink**) 04-20-2008, 11:30 AM

So I'm importing a file into a temp table using DBLoad.

This file though does not have the same format as the table it will
move into. It needs to be "Last, First M" but within the file it is
"First M Last". Not all people have middle initial as well, and if they
don't have a middle initial there are 2 spaces in between.

The file is just a list of names,address,phone,etc.

My boss and I decided to load it into the temp table and figure out how
to seperate the First, Last, and Middle initial into sepearate fields
and then move it into the appropriate table using an Informer but we're
not experienced with String parsing.

Anyone got any tips? I can't imagine this is a new problem, but we've
never dealt with it. Thanks much.

David Bannister Jr.
bannisd@gmail.com

#2 (**permalink**) 04-20-2008, 11:30 AM

Dave wrote:
> So I'm importing a file into a temp table using DBLoad.
>
> This file though does not have the same format as the table it will
> move into. It needs to be "Last, First M" but within the file it is
> "First M Last". Not all people have middle initial as well, and if they
> don't have a middle initial there are 2 spaces in between.
>
> The file is just a list of names,address,phone,etc.
>
> My boss and I decided to load it into the temp table and figure out how
> to seperate the First, Last, and Middle initial into sepearate fields
> and then move it into the appropriate table using an Informer but we're
> not experienced with String parsing.
>
> Anyone got any tips? I can't imagine this is a new problem, but we've
> never dealt with it. Thanks much.

Dbload can do it for you. See the syntax for decoding a fixed format file
in the Migration Guide. It's something like:

FILE my_input
(last 1-20,
mi 22,
first 24-40 );

If the file is variable length, use UNIX tools to pad it out:

awk '\
{
if (NF == 3) { printf "%20s %1s %20s\n", $1, $2, $3; }
else { printf "%20s %20s\n", $1, $2; }
}

Or to convert it to delimited format:

awk '\
{
if (NF == 3) { printf "%s|%s|%20s|\n", $1, $2, $3; }
else { printf "%s||%s|\n", $1, $2; }
}

More elegant solutions using Perl I'm sure, but it takes me a minute or two
longer to write in Perl, so...

Art S. Kagel

#3 (**permalink**) 04-20-2008, 11:30 AM

Art S. Kagel wrote:
> Dave wrote:
Minor fixes to the AWK lines, forgot closing quotes and filenames and
backslash is not needed with single quotes. See below:

Art S. Kagel

>> So I'm importing a file into a temp table using DBLoad.
>>
>> This file though does not have the same format as the table it will
>> move into. It needs to be "Last, First M" but within the file it is
>> "First M Last". Not all people have middle initial as well, and if they
>> don't have a middle initial there are 2 spaces in between.
>>
>> The file is just a list of names,address,phone,etc.
>>
>> My boss and I decided to load it into the temp table and figure out how
>> to seperate the First, Last, and Middle initial into sepearate fields
>> and then move it into the appropriate table using an Informer but we're
>> not experienced with String parsing.
>>
>> Anyone got any tips? I can't imagine this is a new problem, but we've
>> never dealt with it. Thanks much.
>
>
> Dbload can do it for you. See the syntax for decoding a fixed format
> file in the Migration Guide. It's something like:
>
> FILE my_input
> (last 1-20,
> mi 22,
> first 24-40 );
>
> If the file is variable length, use UNIX tools to pad it out:

awk '
{
if (NF == 3) { printf "%20s %1s %20s\n", $1, $2, $3; }
else { printf "%20s %20s\n", $1, $2; }
}' input.file >output.file

> Or to convert it to delimited format:

awk '
{
if (NF == 3) { printf "%s|%s|%20s|\n", $1, $2, $3; }
else { printf "%s||%s|\n", $1, $2; }
}' input.file >output.delimitedfile

> More elegant solutions using Perl I'm sure, but it takes me a minute or
> two longer to write in Perl, so...
>
> Art S. Kagel

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Full Name help