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.

closed as off-topic by philshem, albert, magdmartin, Mark Silverberg, Joe Germuska Oct 14 '15 at 15:01

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "This question does not appear to be about open data within the scope defined in the help center." – philshem, albert, magdmartin, Mark Silverberg, Joe Germuska
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    What's the output format supposed to be? Another Excel file? A CSV? – RoyalTS Sep 29 '15 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 '15 at 0:36
  • Output can be either Excel or CSV – Thomas Sep 30 '15 at 7:39
  • 2
    This is really a programming question and not related to OpenData. – philshem Sep 30 '15 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. – Jan Doggen Oct 5 '15 at 14:14
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.

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