Importing text file

[plsh]

Hi all,

I am trying to import a text file into SQL Server 2000. The fields that I am importing the data into are numeric, and have to be. The fields been imported are numeric but every now and then they have a trailing -, to indicate that the field is a negative number, but the program, that creates the file cannot alter this and will always display the field with a trailing - sign. Is there any way that I can format the field when it has a trailing - sign and make it come first, for example? 1- needs to be imported as -1. I know it can be done through Visual Basic using the CDBL command, but is this possible through the import?

Thanks for any help

 

[JohnDTampaBay]

Use an ActiveX transformation. Something like this:

'  Copy each source column to the destination column
Function Main()
	IF Right(DTSSource("Col001"), 1) = "-" THEN DTSDestination("Col001") = Right(DTSSource("Col001"), 1) + Left(DTSSource("Col001"), Len(DTSSource("Col001"))-1) ELSE DTSDestination("Col001") = DTSSource("Col001")
	Main = DTSTransformStat_OK
End Function

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[plsh]

Thanks AngelWPB,

Your idea works but now I have another problem. The idea that you posted works when you do a manual import of a text file, but I need to be able to schedule the import and can't see anywhere to place this sort of script. I have a text file in my dts as a source and a database table as my destination. When I link the two to transform the task, there is nowhere where I can place any sort of script. Anyway additional help would be great.

Thanks

 

[JohnDTampaBay]

Once the DTS package performs the import correctly, it can be scheduled to run at what ever interval you like.

In the DTS Designer open the Transform Data properties (double click the arrow between the Source and Destination connections). On the Transformations tab, click on the arrow that connects the source and destination columns in question. Delete that transformation then drag the column from Source to Destination to define a new transformation. Choose ActiveX as the type of transformation and click OK. Now click the Properties button and insert the code above in the code window (change the column names as appropriate). Click OK several times to close dialog boxes and save the transformation. Now run the package and test the results.

Any questions?

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[plsh]

BRILLIANT!!! thank you so much, makes my life much easier, thank you for all your help!!

 

[JohnDTampaBay]

Your welcome. Glad to help.

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.