You are not logged in.

#1 2013-04-03 15:08:14

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.

Offline

#2 2013-04-03 16:33:46

graysky
Member
From: /run/user/1000
Registered: 2008-12-01
Posts: 8,594
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 packagesZsh and other configs

Offline

#3 2013-04-03 16:41:55

Trilby
Forum Moderator
From: Massachusetts, USA
Registered: 2011-11-29
Posts: 13,966
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.


InterrobangSlider
• How's my coding? See this page.
• How's my moderating? Feel free to email any concerns, complaints, or objections.

Offline

#4 2013-04-03 16:44:02

jakobcreutzfeldt
Member
Registered: 2011-05-12
Posts: 1,033

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.

Offline

#5 2013-04-03 17:46:01

Trilby
Forum Moderator
From: Massachusetts, USA
Registered: 2011-11-29
Posts: 13,966
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


InterrobangSlider
• How's my coding? See this page.
• How's my moderating? Feel free to email any concerns, complaints, or objections.

Offline

#6 2013-04-03 18:18:35

Mr. Alex
Member
Registered: 2010-08-26
Posts: 623

Re: Need a suggestion on how to process XLS files

graysky wrote:

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)

Offline

Board footer

Powered by FluxBB