by TKlingm » Thu, 18 Oct 2001 05:02:11
Tom Klingman
by Bridget El » Thu, 18 Oct 2001 06:27:50
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
> Tom Klingman
by TKlingm » Fri, 19 Oct 2001 23:07:44
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
11. How Do You Trim Leading/Trailing Non-Blank Characters