0

Background

As part of a broader application that allows users to search thousands of MS Office documents on a private network, I need to index and make searchable Microsoft Excel files.

My basic approach for all document types is this:

  1. Loop through the raw directories I have been given,
  2. Upload the document to a network share
  3. Extract some searchable text from content of the document.
  4. Create a new MS SQL record with the name of the document, URL of it's location, and searchable content.
  5. Use MS SQL's Full Text indexing on the "content" column so that end-users can search key words or phrases via a user interface.

Problem/Question

I am not sure of the most optimum approach for extracting searchable text from Excel docs. My initial thought is to loop through the doc: cell-by-cell and pull out all text and then concatenate that text together into the SQL record, but that seems to be a bad strategy as there could be several cells that are exact duplicates, cells that are only numeric, and generally other types of noise.

Is there an established design pattern or strategy for case? Absent that, I welcome and appreciate any suggestions (as will future Stack Exchange users facing the same problem).

  • I think your strategy sounds not too bad (you surely have to check if the performance and the amount of needed disk space is feasible). You might have to filter out numeric content, and you could delete the duplicates before you insert the texts into your database, if that really bothers you, but if that is really worth the hassle, or if this is premature optimization, depends on how the majority of your Excel documents look like. And I guess what is "noise" and what is content you cannot decide. Better let your users decide. Numeric content could be somthing worth searching. – Doc Brown Jun 22 '14 at 17:10
  • Anybody have any experience with Microsoft Search Server 2010 Express? It's free, and may do some of what you require. I've never used it so I can't recommend it one way or another. – dbc Jun 22 '14 at 19:14
1

Excel files use a string table which "folds together" duplicate entries; if you're willing to do the work, learning the file format and getting to that string table would run much more quickly than launching Excel and etc etc etc. This strategy would have one flaw: it would NOT index strings that were the result of formulas, because those aren't in the table.

|improve this answer|||||
  • Sounds like extremely much effort for an optimization we don't know if the OP will ever need. And there are (even some free) ready-made libraries for reading Excel files directly, without using MS Excel, so I don't think it's a good idea to reinvent the wheel and build your own Excel reader just for indexing. – Doc Brown Jun 23 '14 at 9:15
  • All depends on what the guy is doing. I would expect something like this approach to be used either if the code is running on a server where performance matters, or if this is part of a commercial product. The existing Excel-reading libraries would be a good starting point, either whole or as the basis for a bit of cannibalism :) – mjfgates Jun 24 '14 at 18:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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