Win a copy of Production-Ready Serverless (Operational Best Practices) this week in the Cloud/Virtualization forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Bear Bibeault
  • Jeanne Boyarsky
  • paul wheaton
Sheriffs:
  • Junilu Lacar
  • Paul Clapham
  • Knute Snortum
Saloon Keepers:
  • Stephan van Hulst
  • Ron McLeod
  • Tim Moores
  • salvin francis
  • Carey Brown
Bartenders:
  • Tim Holloway
  • Frits Walraven
  • Vijitha Kumara

Reading an excel file and putting it into an array  RSS feed

 
Ranch Hand
Posts: 163
2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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...

Any suggestions on how to accomplish this?
 
Bartender
Posts: 20562
120
Android Eclipse IDE Linux
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Ranch Foreman
Posts: 285
2
Fedora Netbeans IDE Spring
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I second what the person above wrote.

 If you have the option to use csv then you can just read that like a text file because the columns are separated by ,
 
John Morgan
Ranch Hand
Posts: 163
2
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sometimes the simplest is the easiest... I just saved it as a pipe delimited CSV file... problem is solved. (I hate when I try to over complicate things like this).  Now on to my next problem.
 
Sheriff
Posts: 24282
55
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
when your children are suffering from your punishment, tell your them it will help them write good poetry when they are older. Like this tiny ad:
global solutions you can do in your home or backyard
https://coderanch.com/t/708587/global-solutions-home-backyard
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!