Extract Matched Data from CSV or XLS

[buickgn]

Hello all,

I have a file I can download into either a CSV or a XLS file. I then want to take the file which contains customer data (Name, company, address, city, state, country, email, phone, etc) and separate the customers that are in the US and those that aren't in the US. I've been able to import the entire XLS into labview as a 2D Array, but I'm not sure how to separate the US and Non-US customers and return the results back to an excel template.

Attached is an excel file with test data. The excel file will always vary in size, but the columns will always be the same.

Thanks all!

[josborne]

So you want to sort the data, and then write it back to Excel?

Lots of ways to do that in LabVIEW.  Try running the array through a FOR loop and then check the value of the country column (conditional) and append to a new array inside a case statement.

But its way easier to do that in Excel.  In fact, it too me 6 mouse clicks.

Is this data being produced from a DB export?  If so, you should just add a SORT BY command to the export routine.

[buickgn]

I don't want to sort the data. I want to separate the US and non-US customers and then export them into separate Excel templates.

It's being downloaded from a marketing website. I've tried running it through a FOR loop but it doesn't return the values before the "Country" column. Also, I want to write a LV program because I want the program to send emails to all the customers as well as send the XLS files to specific users. For example, non-US file to one person, US file to another person.

[josborne]

OK.

To do that in LV:

Run the 2D array through a FOR loop using auto-indexing.  So for each iteration of the FOR loop, the auto-indexing will pull out a 1D array (the relevant row).  Then you need to index the appropriate column from that row and process the conditional logic.

[buickgn]

josborne,

This is where I am so far. The VI searches and returns only one row from the file that contains the string I enter. However, I want it to return every row that contains the string I enter.

Thanks again

[Mark_Yedinak]

Use build array and a case statement (or a conditional array tunnel in LV 2012) to add only the elements that match your criteria.

[buickgn]

Hi Mark,

Do I still use the "Search 1D Array" function or is there a better way to do it? I'm not sure exactly how to code that. By any chance, can you provide an example? Thanks for your help

[josborne · Accepted solution]

You don't need to use search array (though it would be a faster and clever way to do it).  The simple way to do it is this:

[josborne]

BTW:  You seem like you could use some training in the basics. 

No offense, but you might want to start here:  http://www.ni.com/academic/students/learnlabview/

[buickgn]

Josborne,

I appreciate it. I actually had this before I posted, but it wasn't reporting the correct results so I assumed it had something to do with the way I was reading the XLS file. Thanks for putting me on the right track, I'll keep at it. I actually have been to the first 2 core courses, just trying to get acquainted with arrays.

Take care