Import text file w/ numeric values as spaces

[OasisChurch]

I have a text file exported from a Unisys system that I need to import into SQL Server. In several of the fields that are supposed to be numeric, the text file will actually contain a space for every value instead of a zero "0" or NULL
EX: field1 numeric(5) will contain 5 spaces in the text file. I changed all the numeric fields to varchar in my exsiting table and was hoping to modify that data after loading, however, there are serveral hundred fields and this takes 6 hours. Would it be faster to use an active X script to say "if field is numeric and source is not, add that entry as NULL?

Never written an active X script. is is hard o do?

thanks in advance!
--scott

 

[mercwrought]

well in the last conversion that i did i actualy used a comand line text replacment tool to do the job. I had to export the file from an old system, clean the file, then import it into our new system. As for your specif task I think that an active x script will do they arnt that hard to write.

 

[Meleagant]

Active X scripts are really easy. Just click on one of the transformations, delete it, select New, then chose Active X Script, then select Properties. Using the Browser and the Language window in the left bar it is really easy to quickly write a little function. Something like:

Function Main()
    If isNumeric(DTSSource("Field5"))
        DTSDestination("Field5") = null
    End if
    Main = DTSTransformStat_OK
End Function

The only thing I don't like is you don't get syntax highlighting.

As for speed, I'm not sure that this is the best way or if your data source should be a query, rather than a table, where you do your data manipulation there so it is a straight Copy Column transformation rather than in an Active X script. I usually deal with stuff under a million rows and I've not really noticed any appreciable difference using Active X Scripts, other may have different opinion though.

Journeyman -- The Order of the Seekers of Truth and Penitence