Trimming in DTS while importing Text File to SQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Trimming in DTS while importing Text File to SQL

Author

Topic

Jeepaholic
Starting Member

36 Posts

Posted - 2002-09-05 : 18:21:28

Hi there...I'm taking a comma-delimted text file and pulling it into a table nightly. What I've found is that it's importing the trailing spaces as well. Is there an easy way to eliminate these during the import? Datafield is typically a varchar.

Thanks for the thoughts!

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-09-05 : 18:33:58

Maybe not the best way, but here's one solution:

Insert the data into a staging table,
and then do a INSERT INTO..SELECT.
In the SELECT, TRIM() your field

 
INSERT INTO ProductionTable(Field1, Field2)
SELECT TRIM(Field1), Trim(Field2)
FROM StagingTable

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

mr_mist
Grunnio

1870 Posts

Posted - 2002-09-06 : 04:32:10

In the DTS import, you can assign an ActiveX transformation to whatever line you want to trim, then code in a trim() function around whatever column it is that you need to revmove the spaces from.

So the transformation code will look something like

'*********************************
' Visual Basic Transformation Script
'*********************************

' Copy each source column to the destination column
Function Main()
DTSDestination("mytext") = trim(DTSSource("mytext"))
Main = DTSTransformStat_OK
End Function

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-09-06 : 07:42:17

Both good suggestions, however, the best solution to these types of things, and the first thing you should try, is to get your data provider to to give you good data in the first place ... Both of the above will give you slower performance than your status quo. Having good data will actually give you better performance than what you have now ....

Jay White
{0}

Jeepaholic
Starting Member

36 Posts

Posted - 2002-09-06 : 14:11:23

Bah, my apologies. Don't know why I said "comma delimited" when I meant to say "tab delimited". Regardless, the spaces in the non-numeric columns are sticking...which is what I'm trying to get rid of. I don't think it matters to the suggestions made, but thought I'd point it out for reference.

I attempted the Active X solution, but am still seeing the spaces. Below is how I implemented it...I did a basic straight transformation for the columns with numbers in them. The spaces are automatically removed from these. I added another transformation to this task for the column "mtf_name" [varchar(64)] by creating a new ActiveX Script. I added the source and destination columns (both "mtf_name") and put the follow9ing Active X function in:

===============
Function Main()
DTSDestination("mtf_name") = trim(DTSSource("mtf_name"))
Main = DTSTransformStat_OK
End Function
===============

...but I'm still getting the same spaces at the end. Am I doing something wrong?

BTW- I tried it without putting "mtf_name" in the source and destiation TABS...and leaving the ActiveX script as is. Same results.

Thanks for you help, again!

Edited by - Jeepaholic on 09/06/2002 14:17:08

ksw
Starting Member

24 Posts

Posted - 2002-09-06 : 18:58:28

Your VBScript looks right to me. I tested the logic on my machine and it works. The only things I could suggest is 1)Make sure that your Source Text File is properly defined as a Tab delimited file (Go to the connection properties & click the properties button to open up the Text File properties dialog) & 2)Double check that your destination fields really are varchar and not char. Char will save "ABC" in a char(5) field as "ABC ".

--KSW

mr_mist
Grunnio

1870 Posts

Posted - 2002-09-09 : 03:39:57

quote:

I added another transformation to this task for the column "mtf_name" [varchar(64)] by creating a new ActiveX Script. I added the source and destination columns (both "mtf_name") and put the follow9ing Active X function in:

===============
Function Main()
DTSDestination("mtf_name") = trim(DTSSource("mtf_name"))
Main = DTSTransformStat_OK
End Function
===============

...but I'm still getting the same spaces at the end. Am I doing something wrong?

I don't think so.

Is it definitely spaces you're getting at the end of your lines? Is every line the same length? How are you viewing the results to see the spaces (IE in Enterprise Manager, as text or as a grid?)

I'm starting to think that Page47 had the best idea with getting folk to provide you stuff without spaces in the first place :)

For your information, I use the activex method myself and it does work.

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-09-09 : 04:01:18

Jeep,

Try to avoid a ActiveX transformation at all costs..

It is effectively a CURSOR solution and has a high performance penalty..

I would go down Michael's path..

BULK INSERT/BCP (There is no faster way to move data) into a staging table and then parse etc.. into your "live" tables....

DavidM

"SQL-3 is an abomination.."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources