Read entire file into a Stata macro variable?

1
votes

I would like to open a file "my_query.sql" and read the entire text of that file into some macro variable x.

Clearly, I should start with something like:

file open myfile using my_query.sql

But my problem is that file read myfile x isn't quite right as that just reads the first line...

My initial ideas:

  • Perhaps there is a way to open it in binary and read the whole thing in with a single command?
  • Or do I have to do some hacked up, read the file line by line and concatenate the strings together?
2
stata
This may be relevant. - dimitriy

2 Answers

5
votes

My preferred solution is the "hacked up, read the file line by line and concatenate" solution.

I can also understand why the solution may seem hacked up, especially for somebody coming from a programming language. For example, this approach might even seem silly next to something like a BufferedReader in Java, but I digress...

You only get the first line of the file when you execute file read myfile x because, according to the documentation at help file:

"The file is positioned at the top (tof), so the first file read reads at the beginning of the file."

This is actually a convenience if you are writing to a file with file write because you won't have to embed newline characters in the string you wish to write - each call to file write will write a new line.

Now, there is a very simple loop construct that allows us to read line by line and store the contents into the macro.

So, if I had a .sql file at /path/to/my/file/ titled SqlScript.sql with the following contents: 

SELECT *
FROM MyTable
WHERE Condition

Then the solution becomes something along the lines of:

clear *

file open myfile using "/path/to/my/file/SqlScript.sql", read

file read myfile line
local x "`line'"

while r(eof) == 0 {
    file read myfile line
    local x "`x'" " " "`line'"
}
file close myfile
di "`x'"

and the result:

SELECT * FROM MyTable WHERE Condition

Here, I used r(eof) to condition my while loop. This is an end of file marker which evaluates to 1 when file read reaches the end of the file.

3
votes

Here's something that may help you open the file in binary and read it into a local macro.

The good news is, this appears to read the entire text file into the macro in one read.

clear *

file open myfile using "SqlScript.sql", read binary

file read myfile %100s line
local x "`line'"

file close myfile
di "`line'"

The bad news it, it (as written) reads 100 characters - it doesn't know where to stop. I think that if you know what signifies end-of-text-file on your operating system, you could search for that character and substring everything up to it. But dealing this this is beyond me at the moment. And you'll want to replace the newlines with spaces.

If this can be made to work for you I'd like to see the solution.