No-Code File Parsers

QBench allows you to setup a File Parser which transfers data from a spreadsheet-like file to a Spreadsheet Worksheet. The File Parser has different configuration options to choose from. To create a No-Code File Parser, you will need to navigate to the File Parsers module, create new, and select "Standard" as the Editor Type. Once you have done this you will be confronted with two new tabs:

Configuration

This is where you specify the type of file to upload and how the parser will be triggered

• Type
  • The type of file to parse. For now, you can only select "Delimited file (Excel, CSV, TSV, etc.)
• How Are File Contents Delimited?
  • This is essentially where you specify your "file type" e.g. .xlsx or .csv
  • Possible values: Excel, Comma Separated, Tab Separated, Custom Delimiter
  • If you select "Custom Delimiter", you will be prompted an additional input field to specify your delimiter. This can only be a single character.
• Data Target
  • This value specifies if you want to upload data to a Batch or a Test 
  • Possible values: Populate Batch Worksheet, Populate Test Worksheet
• Trigger
  
  • When and this File Parser will be triggered
  • Possible values: When file is added to Batch attachments, When file is added to Test attachments
• Filename Should
  • Filename comparison operator. This is used to determine if your File Parser should be triggered upon attachment upload
  • Possible values: Equal, Start With, End With, Contain
• Filename Text
  • Works in conjunction with the above. For example, choosing "Start With" and writing "Instrument Data" will trigger this file parser for the following file names:
    • "Instrument Data 01/01/2022.csv"
    • "Instrument Data.xlsx"
    • "Instrument Data For File Parser.tsv"

Finders

File Parsers can have different Finder Types. The following Finder Types you can choose are:

• By Cell Range - Specify a specific cell range to import
• By Sample in Batch - Construct a "Lookup Key" based on a Sample in a Batch using Jinja2 syntax. This will find the cell by the constructed "Lookup Key" where you can further specify how data is imported based around this cell.
• By Test in Batch - Construct a "Lookup Key" based on a Test in a Batch using Jinja2 syntax. This will find the cell by the constructed "Lookup Key" where you can further specify how data is imported based around this cell.

By Cell Range

A Finder is how your File Parser will find data in your import file. You specify a cell or range of cells which will be transferred over to the Batch or Test Worksheet.

• Cells From File
  
  • Range from the import file to import e.g. D5:E10
• How Should Each Cell Be Imported?
  • Same as cell position: Will import from the file to a Spreadsheet Worksheet into the exact same cells
  • Target Start Cell: Will import from the file to a Spreadsheet starting from the cell coordinate specified in the "Target Start Cell" input
• Transpose
  • If checked, the target cell range will be transposed. This means that the columns will become rows and vice versa.
• Repeat
  • Whether or not this Finder has a "repeated" pattern every X rows/columns
• Repeat Direction
  • Can specify if the repeat pattern will be per-row or per-column
• Repeat Every X Rows
  • The number of rows between the start/end of each pattern for this Finder.
• Repeat Count
  
  • The number of times your repeat pattern occurs

Example of a repeat pattern

Say you have a spreadsheet with the following data:

The pattern starts from range B5:D6 and repeats every 5 rows a total of 4 times. We can setup the following Finder to match this pattern:

The following cells will be imported:

Repeat #	Import File Cells To Import	Spreadsheet Worksheet Data Cells
0	B5:D6	A2:C3
1	B10:D11	A4:C5
2	B15:D16	A6:C7
3	B20:D21	A8:C9
4	B25:D26	A10:C11

And this will be the final result in the Spreadsheet Worksheet:

By Sample/Test in Batch

Finders by Sample in Batch and by Test in Batch function similarly - the difference being the entity being used for constructing the lookup key. For example, using By Sample in Batch, you can construct the lookup key with the Jinja2 code SAMPLE_{{sample.id}}. Using By Test in Batch, the lookup key can be defined as TEST_{{test.id}}. Here are the different fields for this finder:

• Source Lookup Key
  • Lookup key used to locate a cell within your source file that contains your Sample/Test ID. Input can be Jinja2 code.
    • For Finders by Sample in Batch, use sample as the entity context e.g. {{sample.id}}
    • For Finders by Test in Batch, use test as the entity context e.g. {{test.id}}
• (Source Cell Offset) X Offset
  • Horizontal offset (from the Source Lookup Key cell coordinate) to start retrieving data from. Must be a positive or negative whole integer.
• (Source Cell Offset) Y Offset
  • Vertical offset (from the Source Lookup Key cell coordinate) to start retrieving data from. Must be a positive or negative whole integer.
• (Source Cell Offset) X Size
  • The number of cells to import horizontally from the resulting X/Y offset. Must be a positive whole integer.
• (Source Cell Offset) Y Size
  • The number of cells to import vertically from the resulting X/Y offset. Must be a positive whole integer.
• Target Lookup Key
  • Lookup key used to locate a cell within your Batch Worksheet that contains your Sample/Test ID. Input can be Jinja2 code.
    • For Finders by Sample in Batch, use sample as the entity context e.g. {{sample.id}}
    • For Finders by Test in Batch, use test as the entity context e.g. {{test.id}}
• (Target Cell Offset) X Offset
  • Horizontal offset (from the Target Lookup Key cell coordinate) to start importing data into. Must be a positive or negative whole integer.
• (Target Cell Offset) Y Offset
  • Vertical offset (from the Target Lookup Key cell coordinate) to start importing data into. Must be a positive or negative whole integer.
• Transpose
  • If checked, the target cell range will be transposed. This means that the columns will become rows and vice versa.

Here is an example configuration of a Finder by Sample in Batch

And here is the Source File along with the Target Worksheet that data will be imported into:

Source File

The configured Finder will detect cell coordinate A1 of the Source File as a valid source cell because it matches the Lookup Key (SAMPLE_1310_SOURCE_DATA). It will then use an X Offset of 3 and Y Offset of 2 to target cell coordinate D3. With an X Size of 3 and a Y Size of 1, the cell range D3:F3 will be imported. The Finder will find all matching Lookup Keys and perform this same logic and import it into the matching Target Lookup Key of the Batch Worksheet

Target Batch Worksheet

Within the Batch worksheet is a column that matches the Target Lookup Key format, e.g. SAMPLE_1310_DATA. Using this example, the source range D3:F3 will be imported starting at cell D2 of the Target Batch Worksheet (because there is a Target Offset X of 1 and Target Offset Y of 0). Here is the resulting Batch Worksheet after all Lookup Key references have been processed:

Working with No-Code File Parsers and Dynamic Spreadsheets

Demo Video:

Dynamic spreadsheets can have as many sheets as possible. Now, the No-Code File Parser can parse data from your source file to any specific tab of your target dynamic spreadsheet. 

A new field on a No-Code File Parser’s Finder called Target Sheet Name is used to specify which tab of the spreadsheet you want your data to populate in.