3

I have hundreds of Excel files (databases) from which I want to extract a subset of variables.

The data are not systematically in the same cell (e.g. G4) and also don't always start on the same row. However, they do have the same variable name. The titel of the file contains a date which I would need to extract as well.

I am basically looking for a way to get all values under a certain variable name within a file in a single file.

What would be the easiest approach to combine all sheets in a single database ? Using Macro's? All tips/links are very welcome.

Improve this question
5
  • 1
    What's the output format supposed to be? Another Excel file? A CSV?
    – RoyalTS
    Sep 29, 2015 at 21:55
  • 1
    An example of the file would help folks answer you better. You might also try putting some $$ against your request on a site like bountify.co -- just a thought
    – Mark Silverberg
    Sep 30, 2015 at 0:36
  • Output can be either Excel or CSV
    – Thomas
    Sep 30, 2015 at 7:39
  • 2
    This is really a programming question and not related to OpenData.
    – philshem
    Sep 30, 2015 at 7:41
  • If programming is allowed: open the file through OLE, search for specific cells. I have done this in the past where we knew blocks of cells would have a header and an indeterminate number of rows (move down to the first empty cell). Named cell ranges would make things easier.
    – user4293
    Oct 5, 2015 at 14:14

1 Answer 1

Reset to default
1

I have personally found that using Perl and Spreadsheet::ParseExcel was relatively simple and useful for extracting data from Excel sheets.

Another approach that may work is to upload into Google and use the Google APIs to extract the data.

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.