Query using bind variable returns nothing

Question

Still a beginner here learning to use SOQL and I am not sure why this is returning nothing. I start off with using a txt file as a static resource and reading its contents which in my case are report names, one in each line. I then use the line variable in my query line by binding it in order to retrieve report ID and folder name.

I run it through the loop and I can confirm that the line variable is indeed getting each individual line in the txt file. But when I check the output, all it returns is () each iteration.

// sr is where I store the contents of a txt file that has report names
StaticResource sr = [select body from StaticResource Where Name = 'test'];
String contents = sr.body.toString();

for(String line : contents.split('\n')) {
    
    // Get the report ID and folderName
    List <Report> reportInfo = [SELECT ID, FolderName FROM Report where Name = :line];
    
    System.debug(reportInfo);
    
}

Any help is appreciated. Thanks.

Answer 1

If I had to guess, you may have Windows-based line endings in your file; commonly called CRLF, written as \r\n. Consider normalizing your line endings first. You can also take this opportunity to query all the reports at once using a bind variable of a list of strings:

StaticResource sr = [select body from StaticResource Where Name = 'test'];
List<String> reportNames = sr.body.toString().replaceAll('\r\n','\n').replaceAll('\r','\n').split('\n');
Report[] reports = [SELECT Name, FolderName FROM Report WHERE Name = :reportNames];

Notice how we normalize CRLF and CR to just LF in two separate replaceAll calls. We need to do this because files from different OSes and software might have line endings that are CR, LF, or CRLF, so we need to be able to handle each of those situations separately.