topic Re: File crawling in SAS Programming

File crawling

pink_poodle — Mon, 04 Oct 2021 18:50:42 GMT

Hi fellow SAS users and @kevin12 ,

I am on a task that is similar to web crawling (

Webcrawling website for certain webpage that has t... - SAS Support Communities).

I want to search a bunch of Xcel files for a word and return file (ideally file and folder) names that contain it.

Any suggestions are welcome.

Many thanks!

Re: File crawling

Reeza — Mon, 04 Oct 2021 19:40:22 GMT

Before you automate a process you should start with a single file.
Do you know how to do this for a single file?

Is using OS commands an option or do you need SAS entirely?

AFAIK you would likely have to import each Excel file, that could then have multiple sheets with the possibility some of the data may not be visible to SAS - ie anything in a text box. No idea if that's actually an issue in your situation but something to think about :).

@pink_poodle wrote:

Hi fellow SAS users and @kevin12 ,

I am on a task that is similar to web crawling (

Webcrawling website for certain webpage that has t... - SAS Support Communities).

I want to search a bunch of Xcel files for a word and return file (ideally file and folder) names that contain it.

Any suggestions are welcome.

Many thanks!

Re: File crawling

pink_poodle — Mon, 04 Oct 2021 20:43:06 GMT

Thank you, @Reeza! I also found a publication that is in the right direction (please see attached). The paper contains a macro that searches text files in folders for a word and returns their names if the word is there. I could save the xcel files in .csv format that is similar to text and see what the %strsrch macro does. I would rather automate file coversion as well. I would really want to know if this macro was already made into a SAS procedure.

Re: File crawling

Quentin — Mon, 04 Oct 2021 20:49:35 GMT

While SAS could do it, to me it doesn't feel like a SAS task. If you're on Windwos, I would try Windows file search first. From a quick google, looks like it can handle it, if you set the appropriate options. Or since you mention dumping the data to CSV, if you did that most text editors (Notepad++, Ultraedit) could do the search.

Re: File crawling

Reeza — Mon, 04 Oct 2021 22:08:14 GMT

SAS VBS macro to convert XML to XLSX, you can modify this to work the opposite direction to convert XLSX to CSV. Not sure how it works if you have multiple sheets.

https://gist.github.com/statgeek/c51f58a009f8d315a200f34912e494b1

Search a bunch of files for words:
https://gist.github.com/statgeek/2f733d27820f43fa37d6ba92c30f22cf

Re: File crawling

Kurt_Bremser — Tue, 05 Oct 2021 07:53:11 GMT

Web crawling is quite easy, as HTML is simple text. Similarly, text files are very easy to search and locate.

But since Excel files (xlsx) are zip-compressed archives of XML files, each file needs to be uncompressed and decoded (the correct XML files need to be searched for data, as some of them only contain metadata) first before you can search.

In case of text files, grep on a UNIX can search all files in a directory tree in one call.

Re: File crawling

ChrisNZ — Tue, 05 Oct 2021 09:51:36 GMT

This macro does all you asked and more.

Re: File crawling

Oligolas — Tue, 05 Oct 2021 11:59:13 GMT

In SAS it's a one liner...

if you write an appropriate powershell script that returns the results to SAS formated as table

First step is to get your powerShell script to run.

Start here

Re: File crawling

pink_poodle — Tue, 05 Oct 2021 13:52:17 GMT

Thank you for helpful suggestions! A simple solution will do for now :).

Re: File crawling

ChrisNZ — Tue, 05 Oct 2021 20:38:02 GMT

> you write an appropriate powershell script that returns the results to SAS formated as table

Including reading compressed (zip, xlsx, egp, rcv etc) files and subfolders?