Parsing Excel and producing new file based on certain criteria

Help
22

Do you mean the file name ?

23

Yes. Since the data was copied to a datatable, how do I produce that table as a file?

24

@nyabbas

Yeah that right…what difficulties do you facing

Simply put right range & give any name
That it…

25

The file being output (errorData.xlsx) pulling from newTable is the same data as the original file. It did not filter out any of the information. What it should show is all rows in which the zip column contains zipcodes less than 9 characters and/or end in “0001” through “0009.”

26

@nyabbas

Can I see you array assign screenshot

Otherwise share you xaml & Excel file…I will check and update you…

27

Sure.

image
image1355×47 16.5 KB

28

@nyabbas

Everything is right…& I had demo with my system is working fine.

For better clarification

You have to share your xaml & Excel file …then only I will find the issue…

29

USASpendingTestv.3.xaml (25.8 KB) Here is the xaml.

It is telling me the excel file is too large

30

Upon closer examination, it looks like the file output from the formula is giving me the opposite, i.e. correct zip codes.

31

@nyabbas
Its Working…
For me please see the example

may problem in your Excel file I have look it Your Excel File then only I can give solution

[project.json|attachment](upload://b9El9wQexFh8lyXIuHF2v5uuOUa.json) (684 Bytes) TestZipcode.xlsx (8.8 KB) USASpendingTestv.3.xaml (30.7 KB)

32

@amaresan When I run your code with the test excel file you provided, it is giving me two rows of data. Both rows have 9-digit zip codes, which is the opposite of what I was trying to achieve. In my mind, it makes sense to just input the opposite criteria (i.e. (“recipient_zip_4_code”).tostring.trim.Count<9 rather than >=9), but then I receive the “Start Index cannot be less than zero” runtime error.

Your code is working flawlessly for displaying the correct zip codes. However, I need to locate and display all of the incorrect codes.

33

@amaresan this may be due to some of the zip column cells being left blank in the excel file. Does that make sense with this error?

34

@nyabbas

Oh Man I understood your requirements wrongly…ha ha

Okay now I’m clear

use below code

spendingData.AsEnumerable().Where(Function(x) if(x(“recipient_zip_4_code”).tostring.trim.Count<9 ,true,arrZipCode.Contains(x(“recipient_zip_4_code”).ToString.trim.Substring(x(“recipient_zip_4_code”).ToString.trim.Count-4)))).CopyToDataTable

As well as xaml

project.json (679 Bytes) TestZipcode.xlsx (8.8 KB) USASpendingTestv.3.xaml (30.3 KB)

1 Like
35

You are the man – thank you!

1 Like
36

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