Frequently Asked Questions

General Query Syntax

How do I check if a field is equal to one of many values?
Where do I place function "X"?
How do I make a field comparison case insensitive?
How do I replace a certain value within a field?
How do I do a line count, and does it include the header?
How do I check if one field is empty, and the other isn't?
How do I find any field values that are less than a certain numeric value?
How do I find any values that contain non-numeric characters?
How do I check for a function returning false?
How do I select multiple fields?

Freq (Frequency) Functions

How do I freq (frequency) multiple fields at once?
How do I do a freq of two different fields, combined as one?
How do I do a freq just the first few characters of a field?

Miscellaneous

I ran a query, but hitting the 'Write to File' button doesn't do any thing.
Is it possible to query against multiple files at once?
How come I sometimes can't normalize the results of a query?
Why do some of my queries seem to be off by 1?
When I search for a value with a space before or after it (ex. " VALUE"), it never matches to values, even when they actually have that space.


General Query Syntax

How do I check if a field is equal to one of many values?
Fields have an In function that compares the field to a list of values. If you wanted to find every field that was either ACTIVE or INACTIVE, then you could use the following where clause.
Where do I place function "X"?
There are three types of functions: field, line and query. All three are fairly straight forward as to where they are placed. Field functions go directly after a field (line[0].IsEmpty()), line functions go directly after selecting the entire line (line.LineNumber) and query functions go at the end of the entire query, between the closed parenthesis and the semi colon.
How do I make a field comparison case insensitive?
The simplest way is to uppercase the field you are testing with the ToUpper function, and compare it to an upper cased version of the text you are searching for. Notice that this only uppercases the value for the test, and if you select it later, it will retain it's original casing.
where line[0].ToUpper() == "JOHN"
How do I replace a certain value within a field?
There are two ways to do this, depending on if you want to select the entire line (with a single field changed) or if you are selecting a field individually. To modify the field within the line, you can use the line function ReplaceFieldValue. You simply pass in the field index you want to modify, the old text and then the new text. If you are selecting a field individually it's even easier, as you can just use the field function Replace. For that function, you only have to pass in the old text, and then the new text.
How do I do a line count, and does it include the header?
The easiest way to do this is to use the 'Line Count' template, which simply puts a Count function at the end of the standard query. Whether it includes a header depends on if the header property is selected for the file. If it is checked, then the header will not be counted, but if it is not, then all lines will be treated as data, you it will be counted.
How do I check if one field is empty, and the other isn't?
When you want to check more than one condition in a where clause, you must combine the two conditions with either the 'and' operator (&&) or the 'or' operator (||). To test if one field is empty and the other is not, you can do the following: (note the use of the 'not' operator (!) to switch the result of the second IsEmpty function)
where line[0].IsEmpty() && line[1].IsEmpty()
How do I find any field values that are less than a certain numeric value?
By default, all fields are treated as plain text. To treat a field as a number, you need to manually convert it through a field function. For whole numbers, you can use the ToInt function (or ToInt64 for number over +/- 2 billion), and for numbers with a decimal point, you must use the ToFloat function. Once the field has been converted, you can then compare it directly to a number. The following will find all fields less than or equal to 100.
where line[0].ToInt() < 100
How do I find any values that contain non-numeric characters?
A very powerful feature of File Query that not many people make use of is regular expressions. These allow you to do searches not only on exact text, but various patterns of text, and are accessed in File Query with the Like function. (You can get more information about them by reading the in-line help or user manual for the Like function, as well as looking up 'regular expressions' online.) To do a test for a value that contains some thing other than number, you can use the following where clause.
where line[0].Like(@"[^0-9]")
How do I check for a function returning false?
Some times you want to look at values that fail a certain test, such trying to find any value that does not start with "DBC", using the StartsWith function. To do this, simply put an exclamation mark before your test, flipping the result so that any falses will return true.
where !line[0].StartsWith("DBC")
If you are not using a function, but perhaps want to just get all values that do not equal a certain value, you can replace the double equals sign with !=.
where line[0] != "NA"
How do I select multiple fields?
You can select as many fields or values as you want by simply separating them with a comma.
select line[0], line[1]

Freq (Frequency) Functions

How do I freq (frequency) multiple fields at once?
The Freq function only works on a single value at a time. If you want to freq multiple fields at once, you must use the FreqReport function. You can find a template for it, listed as 'Frequency Report'.
How do I do a freq of two different fields, combined as one?
The main advantage of the Freq function over the FreqReport function is it's ability to work with any type of values, and not just fields. If you want to do a freq on the combination of two fields, then just use the following code.
var query = (
  from line in inputFile
  select line[0] + line[1]
).Freq();
You can also do a freq on other types of data, such as a field's length.
How do I do a freq just the first few characters of a field?
There are two ways to do this, depending on if you want to freq multiple fields at once, or just one. If you are only freqing a single field, then you can simply use a field function on the field to get any version of it that you want. In this case, you would use the Substring function, selecting from the first character, index 0, for the desired length. The true value in the example tells File Query not to error out if a field has less the the desired number of characters, and it will just select what it has.
var query = (
  from line in inputFile
  select line[0].Substring(0, 3, true)
).Freq();
If you are using the FreqReport function to go against multiple fields at once, you can simply add the following line before your query.
Options[2] = new FreqFieldOptions() {NumOfCharacters = 4};
This line is also in the FreqReport template, but is commented out. It allows you to override the options for an individual field. This example just sets how many characters to look at, but the line in the template shows you the other options that you can override. One thing to note is how the field is specified. In this example, we say Options[2], and the 2 specifies which field to set. What can be confusing is that not only is the index 0 based, meaning we are actually looking at the third field, but we are looking at the third field passed into the FreqReport function, and not necessarily the third field in the file.

Miscellaneous

I ran a query, but hitting the 'Write to File' button doesn't do any thing.
In File Query, writing to a file is a preemptive step. If the 'Write to File' button is selected, then the next query run will be written to the output path, and if it's not selected, then it will just be displayed in the results screen. If you have already run a query without the 'Write to File' button selected, but still want to save the results, then you must use the 'Save Results to File' menu item under the 'File' menu.
Is it possible to query against multiple files at once?
Yes it is! While you can also do joins between two files, similar to an inner join in a database, right now we will stick to joining multiple similar files into a single file to query against at once. The simplest way to do this is to select multiple files from Windows Explorer and right-click to use the Send To menu. When you send multiple files at once to File Query, it will open up the first, and then setup the others within the code. It will then join them all together into a single variable called appendedFiles, and setup the default query to use that.

Be sure to note that the files are not actually appended together directly. They are just setup to be read sequentially, but treated as one. This means that there is no size limit to how big, or many files you can use. As a bonus trick, if you ever want to just append multiple files together, you can send them to File Query, and then just run the default query with 'Write to File' checked, and it will handle not only joinging them together, but also removing any headers from the extra files, so they are not embedded within the new file.

How come I sometimes can't normalize the results of a query?.
When running a query, you are able to change the output delimiter, and that new output delimiter is what is used to normalize the fields. Some of the built in functions (such as a delimited Frequency Report) also change the output delimiter, so running a query after them may affect it. If you have this issue though, you can just go to File->Edit Output Format, and correct the output delimiter.
Why do some of my queries seem to be off by 1?.
When File Query opens a file, it does it's best to figure out the file's format. Some times it can be wrong though. One of the most common way it can be off is with a delimited file that has no header, as in most cases it will assume that any delimited file has one. If this happens, where it thinks there's a header and there isn't, then File Query will skip the first line of the file in every query, causing your counts to come up short by 1. Since File Query boldens the line it thinks is the header, this is fairly easy to spot, so if you ever see the first line as bold, but it doesn't have a header, simply un-check the header button in the tool bar. Counts can also get thrown off if you do have a header, but File Query thinks that you don't, but it doesn't happen as often, and simply requires you to check the header button manually.
When I search for a value with a space before or after it (ex. " VALUE"), it never matches to values, even when they actually have that space.
By default, the 'Trim Fields' option is selected. This causes File Query to trim all leading and trailing spaces from field values before processing them. If you actually want to look at those spaces, then you can simply un-check the option before running the query. If you don't actually care about if the space is there are not, but are simply putting it in your search because you think you have to (such as for a fixed file), then you can just take the extra spaces out of your search and leave the option checked.