Trim on Import

Trim on Import

Post by TKlingm » Thu, 18 Oct 2001 05:02:11



Does anyone know how I can use the Trim Function to trim fields on import?  I
realize I can use Replace with a calculation once the records are in my
database but it would be real nice to be able to do this on fields as they are
imported in! Thanks!

Tom Klingman

 
 
 

Trim on Import

Post by Bridget El » Thu, 18 Oct 2001 06:27:50


Hi Tom

Not that I am aware of.  Why not script it?

Freeze Window
Go to Layout [(Layout #1)]
Import [Restore, No Dialog]
Replace [No Dialog, Field1, Trim (Field1)]
Replace [No Dialog, Field2, Trim (Field2)]
Replace [No Dialog, Field3, Trim (Field3)]

The layout must be layout which has all the fields that Trim is to be used on.

The import command will automatically put only newly imported records in
the current set so that Replace command will only work on them.

If there are lots of fields that need to be trimmed and large numbers of
records to be imported, you might  be better off looping through the set
once using several Set Fields steps to trim each field rather than
performing Replace x number of times:  

Go to Records [First]
Loop
Set Field [Field1, Trim (Field1)]
Set Field [Field2, Trim (Field2)]
Set Field [Field3, Trim (Field3)]
Go to Record [Exit after last, Next]
End Loop

If you have to do this often, it might be worthwhile trying both and
seeing which is faster. If you find out, I'd be interested to know.

--
Bridget Eley



> Does anyone know how I can use the Trim Function to trim fields on import?  I
> realize I can use Replace with a calculation once the records are in my
> database but it would be real nice to be able to do this on fields as they are
> imported in! Thanks!

> Tom Klingman


 
 
 

Trim on Import

Post by TKlingm » Fri, 19 Oct 2001 23:07:44


Hi Bridget,

I am currently doing a Replace (w/Trim) Script step but I am always looking for
ways to minimize steps!

Fortunately this particularly application does not import that many records at
a time.  However, I do have another project with 250K records where I may want
to trim fields and when I do I will compare Replace versus Set and let you know
the difference!

Thank you (as always!).

Tom Klingman

 
 
 

1. Best way to trim all columns in import from fixed length file

I just got done making about 20 different import packages.  I then
noticed that my data has trailing spaces at the end, which I want to
remove.

Here is the infor for an example
Sql table name: dbo.BA_Local
one of the fields: BANUM    it's a varchar, 6 spaces

I am importing from a fixed length text file.  So the field that this
comes from starts in pos 1 and goes through 6. However the actual field
value is usually less that 6 characters:

TEST1 Field2
Tst2  Field2
000   Field2

So my Import Package does a copy column from the first 6 characters to
my BANUM field.  But it also copies the trailing spaces.

What is the easiest way to remove these trailing spaces during the
import?  I know of 3 ways:
1) instead of doing the Copy Column transformation, do the Trim
transformation.  But the problem with this is I can only define 1 source
and 1 destination for each transformation, where my Copy Column
transformation is just 1 Transformation has all source columns and all
of the destination columns selected.

2) The other way is to do an ActiveX script for each column in the
transformation and do a ltrim & rtrim:
DTSDestination("BANUM") = ltrim(rtrim(DTSSource("Col001"))
I know I can have multiple source & destination columns but that will
add alot of time to execute this script for each field in each record.

3) Do an update query at the end.

Isn't there a setting or something somewhere where I can tell it to drop
trailing spaces?

Any help would be appreciated

Thanks

-C

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

2. dbiMakePermanent with a TQuery ?

3. Trimming imported fields > 255

4. select distinct for two of four colums

5. How to TRIM leading spaces from imported text

6. Streams in ADO using #import in C++

7. ... and trim (cod_deleg) = trim (:cod_deleg) stop working when

8. pgsql/src/backend/utils/adt pg_lzcompress.c

9. Trimming ADO input parameters

10. Trimming Whitespace

11. How Do You Trim Leading/Trailing Non-Blank Characters

12. Trim function in SQL

13. How to trim this down?