I am trying to get a program together for to read an excel file, manipulate one of the columns, run a query based on that column and return a new table with the results of the query.
i.e The excel spreadsheet contains First Name, Last Name, Department, City, Phone Number, and a couple of other fields. I need to do the following:
1) Pull in the spreadsheet (maybe 200-300 records)
2) Change the phone number to remove any brackets or dashes so it is a single string of numbers
3) Convert the phone number to text
4) Run the query for every entry (all 200-300) to find any matches to the phone number (we typically get 4-5 returned)
5) View the results (all of the same fields from the original but only have the records that matched in the query in a table
6) Export the table to either a CSV or an XLXS file.
What I cannot figure out is how to use angular (or maybe this would be better in pure java) to either pull the data into an array or to read each line of the spreadsheet and run the query each time...
I don't know what Angular can do, but in Java, you can use the Apache Poi library to read and write Excel spreadsheets, including iterating through the rows and accessing cell data.
In my case, however, I'd use Pentaho Kettle, because it can do all that and more and can be programmed via a drag and drop interface. Actually, I helped improve the programming interface for Excel. But what you have is a classic example of ETL - Extract, Transform, and Load, with a side order of lookups, and that's what the "etl" in Kettle stands for.
Unfortunately, it's not quite as intuitive as one might wish, so for a one-off project you might find it quicker and easier to learn Poi and write a custom Java app. It's also, incidentally, not the only ETL tool available as open-source. I believe Talend is, and it's very popular.
An IDE is no substitute for an Intelligent Developer.
You're lucky that it's you who gets to choose the input format. In business programming it's not uncommon to have your customer insist on some g-dawful complicated format, and since they are your customer it isn't easy for you to tell them no.
All of the world's problems can be solved in a garden - Geoff Lawton. Tiny ad:
RavenDB is an Open Source NoSQL Database that’s fully transactional (ACID) across your database