DTS Text Import - Blanks vs. Nulls 1

[BuGlen]

I'm setting up a DTS package to retrieve a file via FTP and import the data from tab delimted to a table. Everything works fine except that empty fields in the delimited file contain a space. I would like to convert that empty field to a null value on import.

Does anyone have an ideas on how to do this?

TIA!

- Glen

Know thy data.

 

[SQLSister]

After import you could run the following update statment (you can add it to you DTS package using the Execute SQL TAsk)

Update TAbleName
SEt Field = Null
Where Field = ''

 

[BuGlen]

Hi SQLSister,

I saw that solution in Google.Groups, but unfortunately it didn't work for some reason. Just to be sure, I tried it with no space '' and with a space ' ' and neither worked.

Is there a way to tell what is actually in the field if the character is a non-viewable ascii value?

- Glen

Know thy data.

 

[SQLSister]

On a test table, try:

Update TAbleName
SEt Field = Null
Where Field < 'a'

 

[BuGlen]

You Rock! :~)-

Works perfectly. I also discovered (since I'm just learning DTS) the transformation options of the Trim transformation. Unfortunately, to use this type of transformation, you have to map each text column transformation seperately.

Your solution is much easier to implement. Thank you so much!

- Glen

Know thy data.

 

[BuGlen]

Just as a side note. I did find that using the < 'a' condition would null text fields that began with a numeric value. A quick fix is to use &quot;where {column} < '!'&quot;

Still a very easy to implement solution. Thanks again SQLSister!

- Glen

Know thy data.