At 10:49 AM 23-01-02 +0800, Andrew Ferguson wrote:

>Hi all.
>
>Boy am I stuffed. Just spent yesterday (and most of the early hours today) banging out a CSV-to-fixed-field prog. Works pretty darn good too -- finally. Handles any separator (well, okay, only tabs and commas for now), and it also handles quote string delimiters without a hitch -- I know because I kept throwing a 50,000 line, 64-field dumpfile at it until it gave up and begged for mercy.
>
>Yes, all this is to use EXTERNAL FILE. My idea was that I would do easy imports by:
>
>a) dumping old database to CSV text;
>b) run dump through the CSV2Fixed prog (which, incidentally, creates all the field DDL for you);
>c) create the external table in IBConsole;
>d) create new target table with refined field properties based on original non-IB table;
>e) insert everything from external file table into new target table;
>f) and voila! Oh, did I say 'easy imports'? Sure, there's Marathon etc. for datapumping, but I figured something a little more hands-on would accelerate my IB/FB knowledge.
>
>Well all worked according to my fiendish plan... except for one thing. I forgot that all field values inc. nulls are space-padded to max length.
>
>So now I have to figure out how to strip the padding when inserting the fields into the new target table.
>
>I know it's going to be a StProc to deal with it on a field-by-field basis, with right-trimming away the extra padding, but I can't get my head around the actual procedure layout.
>
>If anyone cares to kickstart me by pointing out the obvious way to do it, much appreciated.

Andrew,
One way or t'other, you are going to have to trim stuff. That means UDFs. It probably makes little difference performance-wise whether you do it with a SP or in your INSERT statement. I'd prefer a SP (or a trigger) for something that needs doing regularly and, besides, you'll be able to handle the nulls with more ease.

Grab Greg Deatz's FreeUDFLib from Claudio's website (http://www.cvalde.com) and check out the declarations for the various "Trim" and "strip" funcs. Because you are Delphi-literate, you'll be able to cruise the source and see exactly what's going on in each of these.

cheers,
Helen


All for Open and Open for All
Firebird Open SQL Database · http://firebirdsql.org
_______________________________________________________