Need some clarification of error handling from excel column Values

Help Studio
, ,
1

My use case is Excel have 3 columns …these three columns
1st column is First Name
2nd column is Last Name
3rd column is City,State,Zipcode.
in these 3rd column value need to Split by (,)Commas,But these columns one row does not have Zip Code…Split up the Values and using add Datarow to Add the values to New Excel Sheet…
In this scenario…Zipcode splitup which row is not available in Zipcode Exception Occured…how handle this condition…

2

Hi,

Try to make a condition check if string.isnullorempty(columnname) then made a custom exception in throw activity else : split up and add the values using datarow activity

3

when only ZIPCode is optional, maybe the following can help (Filling up)

Assign Activity
miniItemArray =

 strTest1.Split({","}, StringSplitOptions.RemoveEmptyEntries).Select(Function (x) x.Trim).Append(nothing).Take(3).Cast(of Object).toArray

grafik
grafik796×86 31.7 KB

Feel free to shorten the longer oneliner by breaking it into subtasks

4

hi,

3rd column need to split up then only i can get zip, how can i give a condition in column name ,it throwing error in spliting value variable…

5

Hi @nirmalad123

Do you mean the 3rd column has values separated by comma and when ZIP CODE value is not present split index changes?

a,b,c,zip code value, e index 3-> zip code
a,b,c,e index 3 → e

If so and you only need to get ZIP code from your 3rd column is better check and extract ZIP CODE using regex instead split method

6

Hi

some rows Zip code is not available in that input excel sheet … In that Row only showing index out of boundary exception…when i used split up function it is fine which row is available zip code…

7

excelinput
excelinput1085×352 31.4 KB

This is my input file

8

output
output924×295 33.4 KB

This is my Output File format …
here Zip is not available in 3rd row …Throwing index out of boundry Error.which one is using spilit functions

9

Thank for the clarification @nirmalad123

You can check if index exists before use it in a if activity

String.IsNullOrEmpty(yourSplitVariable.ElementAtOrDefault(zip_code_index))

ElementAtOrDefault = Returns the element at a specified index in a collection or a default value if the index is out of range (null value)

But I suggest use regex instead if this text format can be change in future (more commas and values, for example)

10

we gave you an option and also showcased on how to handle above:

grafik
grafik798×154 39.8 KB

11

And also going further within the prototyping with a minified test set:

grafik
grafik862×191 26.8 KB

Resulting to
grafik

dtResult =

(From d In dtData.AsEnumerable
Let fln = String.Format("{0} {1}", d("First Name").toString.Trim, d("Last Name").toString.Trim)
Let arrSplit = d(3).toString.Trim.Split({","}, StringSplitOptions.RemoveEmptyEntries)
Let ra1 = arrSplit.Select(Function (x) x.Trim).Append(Nothing).Take(3)
Let ra = ra1.Prepend(fln).Cast(Of Object).toArray
Select r = dtResult.Rows.Add(ra)).CopyToDataTable

We can also adapt and as more, we can rely on Patterns (e.g. States always with (XX) ) we can enhance by more detailed parsing City,State,Zip

1 Like
12

Hi,

Thanks for your Answer.i got expected output… can you explain this query…

13

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.