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.
Thank you.
Can you post which type of variable is ContractTable_ExcelVariable @nyabbas?
And also post the error by hovering in the error icon
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
Create Array NameAs arrZipcode & set Value as {“0001”,“0002”) untill 0009
use below code
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:
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.
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?
Yes. You can
Thank you for this. I was able to get the validation error resolved, however I receive this runtime error upon execution:
My code is:
Any idea? Thanks in advance.
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…
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.
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?
Is this not correct?
Anil_G
Shakib
ashokkarale
sharu_priya
sven.wullum1
Babjee.Vangipurapu
Prathamesh_Trimurti_Hulav
riley
sharazkm32
A_Learner