Beefy Boxes and Bandwidth Generously Provided by pair Networks
go ahead... be a heretic

Re: Can't Find my way in Excel

by FreeBeerReekingMonk (Deacon)
on Sep 17, 2019 at 19:35 UTC ( #11106320=note: print w/replies, xml ) Need Help??

in reply to Can't Find my way in Excel

Here is a fresh point of view:

0. Before a query, find all your xls files

1. Keep a stored hash of MD5 checksum's and filenames. This will tell you if a xls has changed and it's data needs to be re-extracted.

2. For each new Excel file not yet with MD5 (or with different checksum), convert the excel to xlsx and extract the ./xl/sharedStrings.xml (which contains all the texts from all tabs)

3. Grep inside all sharedStrings.xml for faster response times (adjust your grep parameters, like grep -l)

For example, Libreoffice has:

libreoffice --headless --convert-to
If you do not have access to install a current version of LibreOffice then there are macros in VBS (XlsToCsv.vbs on S.O.) that output to multiple csv files.

On Linux there might also be unoconv in your repository.

oh, and if you give up rolling your own implementation, why not use a ready-made solution: DocFetcher

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://11106320]
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others exploiting the Monastery: (10)
As of 2019-12-16 12:53 GMT
Find Nodes?
    Voting Booth?

    No recent polls found