Skipping the data if we have any bad data present such as "-"

Help Studio
, ,
1

Hi,
The sheet contains some bad data containing “-” which need to be pushed to database.
We need to skip those data containing bad data but at the end while writing the data we need to have the same count of data as it was in the starting.
How to solve the same.
The sheet is attached for the same:-
Test2.xlsx (10.8 KB)
Can anyone suggest the solution for the same?
Thanks in advance!!

1 Like
2

@Kunal_Jain

When bad data is present instead of skipping try to add a dummy value orblank value or default value

cheers

3

Hi @Anil_G
Actually we need to add same value at last when we are sending the sheet.
To be precise this data of column “Num” is sent to SQL to get the data. So if we have bad data that creates a problem,
And after getting the data from SQL we combine both the data and create new sheet.
So count of column “num” has to be same in both the sheet.
Thanks!!

4

Then use a assign activity like this

  1. dtCorrected = OriginDataTableVar.Clone()

  2. Use another assign activity like this

dtCorrected = (From r In OriginDataTableVar.AsEnumerable
let ra = r.ItemArray.Select(Function (x)
System.Text.RegularExpressions.Regex.Replace(x.ToString.Trim, “[^\d\w].*”,"Null”)).toArray()
Select dtCorrected.Rows.Add(ra)).CopyToDataTable()

I just tried to replace the value any non numerical or non alphabetical characters with text as “Null”

Hope this helps

Cheers @Kunal_Jain

5

@Kunal_Jain

Then save the bad data into a separate table…and after the wuery append the data at the bottom using merge

Cheers

6

Did this method helped u to solve ur scenario
@Kunal_Jain