X1 doesn't index numbers in excel files

[diecastblue]

Hi,
X1 is working great, but It wont index any cells formatted as numbers in excel sheets. If the cell is any other format, it will index them fine.

eg: have test.xls
have two cells, one with 1234 formatted as text, one with 5678 formatted as a number, X1 will find the file when you search "1234" fine, but not when you search "5678".

I have searched the forum, but can only fine old posts regarding this, saying it was a known bug as of v5.6 or so. Anything I can do to get this working?

[w0qj]

We'll rephrase your Feature Request as Substring Search.

ie: 12345678

-X1 can currently search for 1234
-X1 currently cannot search for 5678


If others feel the same way, please feel free to contribute to this thread!

[diecastblue]

Hi,
Sorry I think you misunderstood, this is not to do with substrings. Say I have two excel files called 1.xls and 2.xls, both have 1 cell containing "12345", but in one formatted as text and the other formatted as a number. If you index the two files, and search for 12345 it will only find the file where it is in a cell formatted as text.
X1 will never find any cells in Excel files that are formatted as a number (maybe other types too) and only contain digits.

Thanks

[w0qj]

OK, so your Feature Request is for substring searching of a filename, which has also been requested before.

If anyone else feel the need for this, please feel free to contribute to this thread!

Please also see a very similar discussion here:
http://forums.x1.com/viewtopic.php?t=3603

[diecastblue]

Ok, now you're just not listening.

~ THIS HAS NOTHING TO DO WITH SUBSTRINGS ~

It has to do with how X1 (Stellent?) parses .xls files. It does not index numbers in cells formatted as numbers, only when they are formatted as text.

Take my example above, change 1.xls, and 2.xls to a.xls and b.xls.
a.xls has 1 cell; containing '12345', and the cell is formatted as text
b.xls has 1 cell; containing '12345', and the cell is formatted as a number

If you then search for "12345" it will only return a.xls

I don't think I can be any clearer?!

I have a lot of serial numbers in excel files and if there is no way of searching for them (changing the excel formatting is not an option unfortunately) then X1 is not for me.

Same problems, some from 2004:
http://forums.x1.com/viewtopic.php?t=3097
http://forums.x1.com/viewtopic.php?t=1024
http://forums.x1.com/viewtopic.php?t=646

[gt13]

diecastblue, I agree with you.
This problem is also pointed out in my tests. See this post (in the Excel file, columns AL and AM):
http://forums.x1.com/viewtopic.php?p=11092#11092
If you need this feature, "Windows Search" included in Vista works in that case. It seems that it can also be added to XP if necessary (but I have not tested it):
http://www.microsoft.com/windows/produc ... fault.mspx
http://www.microsoft.com/windows/produc ... earch.mspx

Note also that my (old) X1 version 5.2.3 does not index the comments in Excel Files. Does it work with recent versions of X1 ?

Gerard

[diecastblue]

I am trying out windows search now. Seems pretty limited, X1 seems more powerful, but if it can search numbers in excel it might have to do, or at least work along side X1. It is currently still indexing.
Still on the wall with X1 though, the only thing holding me back is the above problem.

Did a quick test with the latest version of X1 (v6.1 build 3575) and it does not seem to index comments in excel files

[diecastblue]

Indexing has not finshed, so not 100% but it seems Windows Desktop Search is having the same problem, and does not find numbers in cells formatted as numbers...

[gt13]

I have 2 computers with Vista, and search for numbers in Excel files works in both cases with Windows Search.
You should check that Windows Search indexes your file. Try to search for the file name of your Excel file. If you get no answer, it means that your parameters for Windows Search are wrong.

[diecastblue]

Hi gt13. I am running XP, and downloaded the XP version of desktop search. Running with office 2003 (I assume it uses office dll's to parse files?). But it doesn't index number formatted cells. I have saved a copy of all my xls files in xml format in a different folder, and both X1 and WDS index them fine as they are plain text. In WDS they then show, nearly the same as xls formatting, although WDS does no highlighting. X1 does do highlighting but shows xml as raw. So, no optimal solution yet. By far I would like to find a way to get X1 understanding xls as X1 still has the best interface.

[gt13]

I also prefer the X1 interface !

Just to show that it indeed works with Vista, here is a screen capture:
http://snipurl.com/20zco
You can get the files used for the test in another post:
http://forums.x1.com/viewtopic.php?p=11092#11092

Gerard

[Greg Dawes]

Possible work-around:

In its' default state, X1 is able to index Excel files that have cells formatted using the general format and not cells that have been formatted using the number or currency format (for example).

However, X1 has the ability to utilize IFilters. When you install MS Office, a related IFilter for Excel should have also been installed. Telling X1 to use the MS Office IFilter may solve this issue; however, because there are different versions of the IFilter installed, this solution may not always work.

This is the IFilter in question and its' location: c:\windows\system32\offfilt.dll
(the offfilt.dll on my computer is 188KB and was both created & modified on Aug. 4, 2004)


To tell X1 to index your Excel files using the installed IFilter, perform these steps:

1. In X1, go to Tools > Options > Files > More Indexing Options > Specify File Types

2. In the "Additionally index all files with these extensions:" dialog, enter: XLS

3. Click Close, Close then OK

4. At this point, X1 needs to reindex the XLS files, so you've got a couple of options:

- 4a. Move the applicable files to a new/different folder and update your file index; move the files back to the original folder and update your file index a second time
OR
- 4b. Rename the folder(s) where these XLS files reside and update your file index; rename the folder back to the original and update your file index a second time
OR
- 4c. Clear and rebuild your index - note, that rebuilding include files + emails

Once these XLS files have been reindexed, you should then be able to perform searches against your Excel files that have cells formatted using the number/currency format (for example).

**Please note that IFilter support was introduced in v5.6 (Build 3377); so you must be running build 3377 or higher.

Please post back on whether this works for you or not.

Thanks!

[diecastblue]

Greg:
Yeah I had seen offilt.dll there when I was playing around a couple weeks ago. And I already had *.* in my file types list. But in the interests of research:
I just deleted my whole index, copied some test files to a previously un-indexed drive, set my file types list to XLS only, and set x1 to index just that directory.
If you search for the directory, then all the files come up. If I add the "Indexing Status" column, it lists all the XLS files as "IFilter OK", but with just the same result, cannot find numbers in XLS files formatted as numbers

My offilt.dll is 192,000 bytes, v2006.0.5730.0, modified a month ago
(I think I went on an FTP crawl to try and find some more recent versions, but never got it working)

Does this work for you?

gt13:

That first screen shot looks a lot more comprehensive that what I have with WDS in XP!
Mine looks like playschool:

[Greg Dawes]

Does this work for you?

Yes, it does.


Additionally, to clarify a couple of points:

- You "must" place the XLS extension in the "Additionally index all files with these extensions" dialog as mentioned in step 2 above for this to work -- Just want to be clear on this.

AND

- You also "must" force X1 to reindex your XLS files using one of the options listed in step 4


- I am running Microsoft Excel 2003 (11.8146.8132) SP2 on a Windows XP SP2 box

[diecastblue]

Very strange. I only have Office 2003 (11.5604.5606), on XP SP3, so am currently downloading Office SP2 too see if that helps, but I have tried on multiple computers to no avail. And yes XLS is currently in the "additionally index all files with these extensions" dialog, and before that it was set to all (*)
Have you tried gt13's files? They are a good standard test. For the excel problem you need to search for "67356976541"

[Kenward]

A couple of points, is it worth adding this solution to Excel indexing as a separate "sticky" in the Q&A or Tips area?

Are there any other neat iFilter tricks we should know about?

[gt13]

@ Greg
- I have c:\i386\offilt.dll, version 2003.5.28.0, 120832 bytes
- I have XLS extension added in X1 files options
- my indexes have been rebuilt
- I have Excel 2003 SP2
- I use an old version: X1 ® Desktop Search Version 5.2.3 (Build 1852bz-bs) (Released Friday, August 26, 2005) because it it the last one that indexes accented letters
- in this case, X1 does not index numbers (formated as numbers) in Excel cells
- Same results with XP Pro SP2 and Vista
- Perhaps it does not help a lot due to my old version of X1...

Screen capture: http://snipurl.com/214od

Gerard

[Kenward]

The key may be this bit:

Clear and rebuild your index - note, that rebuilding include files + emails

Maybe you did this. You don't say.

[Greg Dawes]

Hi gt13,

IFilter support was introduced beginning with v5.6 (Build 3377). Unfortunately, you must be running build 3377 or higher for the previously provided steps to work (I'll edit my original post to include this additional bit of information).

[gt13]

OK Greg.
Thanks