Arch Linux

Mr. Alex

Member

Registered: 2010-08-26

Posts: 623

Need a suggestion on how to process XLS files

Hello.

I need to process XLS files: compare 2 files and add in the first one lines from the second one. If some columns are the same, I need to add just amount (summarize numbers) and if some columns in second file are not in the first one, I need to add them to first one.

I need a tool suggestion. First I thought it will be easily done with Python and I prefer Python but after some search I'm not so sure anymore... Seems like Python won't do it smoothly as from here it looks rather foggy how to do it.

Please suggest something. XLS files contain tabs and I need to be able to read/write/modify inside of different tabs.

graysky

Wiki Maintainer

From: :wq

Registered: 2008-12-01

Posts: 10,688

Website

Re: Need a suggestion on how to process XLS files

If this is a one-off, why not use gnumeric or libreoffice?  Maybe I am misunderstanding what you need to do.

---

CPU-optimized Linux-ck packages @ Repo-ck  • AUR packages • Zsh and other configs

Trilby

Inspector Parrot

Registered: 2011-11-29

Posts: 30,332

Website

Re: Need a suggestion on how to process XLS files

An R script could do this quite easily - even though I got lost in the "first one second one, first one" talk, whatever the goal, if you want to move data around and do calculations on spreadsheets, an R script will do it.

---

"UNIX is simple and coherent" - Dennis Ritchie; "GNU's Not Unix" - Richard Stallman

jakobcreutzfeldt

Member

Registered: 2011-05-12

Posts: 1,041

Re: Need a suggestion on how to process XLS files

R doesn't support multiple tabs, if I remember correctly. OP should double-check, otherwise I second the vote for R.

Trilby

Inspector Parrot

Registered: 2011-11-29

Posts: 30,332

Website

Re: Need a suggestion on how to process XLS files

read.xls() has a parameter "sheet" which defaults to "1" or the first one.  You can import multiple sheets with multiple calls to read.xls.

ref: http://rss.acs.unt.edu/Rdoc/library/gda … d.xls.html

---

"UNIX is simple and coherent" - Dennis Ritchie; "GNU's Not Unix" - Richard Stallman

Mr. Alex

Member

Registered: 2010-08-26

Posts: 623

Re: Need a suggestion on how to process XLS files

If this is a one-off

It's not and those documents will have several thousand items each. So even one time I couldn't do it manually. Plus I'll have to do it weekly. Probably even more than once a week.

The idea is this.
Suppose we have "1.xls" with contects:

+----+-------------+-----------+
| ID | Name        |  Amount   |
+----+-------------+-----------+
| 1  | apple       |  2        |
| 2  | potato      |  3        |
| 3  | cherry      |  10       |
| 4  | water-melon |  1        |
+----+-------------+-----------+

Then we have "2.xls":

+----+-------------+-----------+
| ID | Name        |  Amount   |
+----+-------------+-----------+
| 4  | water-melon |  1        |
| 5  | pumpkin     |  1        |
+----+-------------+-----------+

And the script must sum them up basing on ID so the result must look like:

+----+-------------+-----------+
| ID | Name        |  Amount   |
+----+-------------+-----------+
| 1  | apple       |  2        |
| 2  | potato      |  3        |
| 3  | cherry      |  10       |
| 4  | water-melon |  2        |
| 5  | pumpkin     |  1        |
+----+-------------+-----------+

We had 1 new element with ID 5 and it's now at the bottom. And we had 1 similar, with ID 4 — they were summed up to amount == 2.
All this is xls and with tabs.

Can I do it with php? “R” is just too new to me at the moment and this job will need to be done soon.

Last edited by Mr. Alex (2013-04-03 18:19:39)