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.
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER