Link to home
Start Free TrialLog in
Avatar of Mr_Shaw
Mr_Shaw

asked on

SSIS null/ blanks values

I have a script task which extracts information from a text flat file.

The script works fine and extracts the information I need.

However, the parts of the text file I don’t need it seems to output what seems to be a Null value or blank space (e.g “ “). Therefore the end result looks a little like this:


,
,
,
,
,
,
,
,
,
1022,ABC, EFG, HIj, LMN, POL (3P)                                           1
1022,DIPH, PERT, TET, POL (1B)                                              2
1022,DIH, PERT, TET, POL, MR (1B)                                           7
1022,MNC (1P)                                                               2
1022,MR (1B)                                                                5
1022,PNEU (2P), DIPH, PERT, TET, HIB, POL (3P)                              1

How do I stop all those blank spaces separated by a comma?

I have included my script bellow.

''Capture Clinic Number
        If Left(Row.DataConversionText1, 6) = "Clinic" Then
            ClinicNo = Right(Left(Row.DataConversionText1, 13), 4)
        End If

        ''Capture imms 
        If Row.DataConversionText1 = "    Cat                                            Number of members" Then
            Row.NextRow()
            Try
                Do While Row.DataConversionText1 <> ""
                    Row.NextRow()
                    Row.Imms = Trim(Row.DataConversionText1.ToString())
                    Row.Clinic = ClinicNo
                Loop
            Catch e As RowIndexOutOfRangeException
            End Try
        Else : Row.Clinic = ""


        End If

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of sameer2010
sameer2010
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Mr_Shaw
Mr_Shaw

ASKER

thanks I used a conditional split.