Parsing Excel and producing new file based on certain criteria

My goal is to parse the excel file (read range) and then pull rows in which a certain column’s contents meet specific criteria. This column contains zip codes with multiple different formats. I would like to catch any obviously incorrect numbers based on length and last four digits.

I’m assuming I need to use Assign on my new datatable, however I am not sure how to set the criteria.

image

Thank you.

Can you post which type of variable is ContractTable_ExcelVariable @nyabbas?

And also post the error by hovering in the error icon

@nyabbas

could I get criteria values?

Ex: ContractTable_ExcelVaiable.Select("[recipient_zip_4_code]=7545").copytodatatable

Variable type is DataTable. Error is due to the fact that I have yet to input criteria value into the expression.

Sure. Zip should not be less than 9 digits/characters. Last four of zip should not be “0001” and so on until “0009.”

Hi @nyabbas

  1. Create Array NameAs arrZipcode & set Value as {“0001”,“0002”) untill 0009

  2. use below code

  3. Use Zipcode col Name in "ZipColName Field

ContractTable_ExcelVariable.AsEnumerable().Where(Function(x) x(“ZipColName”).ToString.trim.Count>=9 And Not arrZipcode.Contains(x(“ZipColName”).ToString.trim.Substring(x(“ZipColName”).ToString.trim.Count-4))).CopyToDataTable

How do I create array?

Just create a variable in the variables pane and the type should be array of whatever type you want, then initialize the variable as new array(length you want){} @nyabbas

So this got a bit more complex. Once I extract the original downloaded file, it is in csv format. So I used “Read CSV” and saved the data table as a variable. Now, I may be misunderstanding the above advice, but my next step was to use assign with the new data table like so:

image

image

The error states that the variable name, i.e., the table you have is not recognized . May be it is not declared globally. Try changing it as a global variable and then check @nyabbas

I have changed the scope and still encountering the same error message.

@nyabbas

Can you share screenshot of contracts_excelvariable from variable pannel

Otherwise share xaml to us …we will check and let u know

Rather than extracting certain rows based on criteria, could I simply highlight the rows within the excel file based on criteria? And then save that file?

@nyabbas

Yes. You can

Thank you for this. I was able to get the validation error resolved, however I receive this runtime error upon execution:

image

My code is:

Any idea? Thanks in advance.

@nyabbas

The problem is Empty zip code … for that use below code …

tes.AsEnumerable().Where(Function(x) if(String.IsNullOrWhiteSpace(x(“ZipColName”).tostring),false,x(“ZipColName”).tostring.trim.Count>=9 And Not arrZipCode.Contains(x(“ZipColName”).ToString.trim.Substring(x(“ZipColName”).ToString.trim.Count-4)))).CopyToDataTable

MakeSure Change all variable name as per yours…

1 Like

@nyabbas

The problem is Empty zip code … for that use below code …

tes.AsEnumerable().Where(Function(x) if(String.IsNullOrWhiteSpace(x(“ZipColName”).tostring),false,x(“ZipColName”).tostring.trim.Count>=9 And Not arrZipCode.Contains(x(“ZipColName”).ToString.trim.Substring(x(“ZipColName”).ToString.trim.Count-4)))).CopyToDataTable

MakeSure Change all variable name as per yours…

Thank you, but I am still getting the same error message as before. Any idea?

Appreciate the assistance.

@nyabbas

Yeah I got what the problem…use below code

tes.AsEnumerable().Where(Function(x) if(x(“ZipColName”).tostring.trim.Count>=9 ,Not arrZipCode.Contains(x(“ZipColName”).ToString.trim.Substring(x(“ZipColName”).ToString.trim.Count-4)),false)).CopyToDataTable

Thank you @amaresan, no more error. Now how do I produce the new workbook/table with the improper zip codes?

image

Is this not correct?