csv

The csv statement is used to create and populate CSV files. It is typically combined with foreach loops to write values extracted from an array in a JSON and/or XML document stored in a named buffer.

Details

CSV files are produced via the use of multiple csv statements which perform the following functions:

• Create a new empty CSV file

• Define the headers

• Finalise the headers

• Write data to one or more rows of the file

• Close the file

All CSV files created by the csv command use a comma - , - as the separator character and a double quote - " - as the quote character. Headers and data fields are automatically separated and quoted.

Create a new CSV file

The following is used to create a new, empty CSV file:

csvlabel = filename

The label must not be associated with any other open CSV file. Up to 16 CSV files may be open simultaneously and the label is used by suesequent csv statements to determine which of the open files the statement should operate on. Labels are case sensitive and may be from 1 to 15 characters in length.

The specified filename is created immediately, and if it is the name of an existing file then it will be truncated to 0 bytes when opened.

The filename argument may contain a path component but the csv statement does not create directories, so any path component in the filename must already exist. The path, if specified, will be local to the Exivity home directory.

Example

csv usage = "${exportdir}/azure_usage.csv"

Define the headers

This section refers to add_headers as the action, but either add_header or add_headers may be used. Both variants work in an identical fashion.

csv add_headerslabel header1 [header2 ... headerN]

All CSV files created by USE script must start with a header row which names the columns in the file. The number of columns can vary from file to file, but in any given file every data row must have the same number of columns as there are headers.

To create one or more columns in a newly created CSV file, the csv add_headers statement is used as shown above. The label must match the label previously associated with the file as described previously.

One or more header names can be specified as arguments to csv add_headers. Multiple instances of the csv add_headers statement may reference the same CSV file, as each statement will append additional headers to any headers already defined for the file.

No checks are done to ensure the uniqueness of the headers. It is therefore up to the script author to ensure that all the specified headers in any given file are unique.

Example

csv add_headers usage username user_id subscription_id

Finalise the headers

This section refers to fix_headers as the action, but either fix_header or fix_headers may be used. Both variants work in an identical fashion

csv fix_headerslabel

After csv add_headers has been used to define at least one header, the headers are finalised using csv fix_headers statement. Once the headers have been fixed, no further headers can be added to the file and until the headers have been fixed, no data can be written to the file.

Example

csv fix_headers usage

Write data

This section refers to write_fields as the action, but either write_field or write_fields may be used. Both variants work in an identical fashion

csv write_fieldslabel value1 [value2 ... valueN]

After the headers have been fixed, the csv write_fields statement is used to write one or more fields of data to the CSV file. Currently it is not possible to write a blank field using csv write_fields, however when extracting data from a buffer using a parslet, if the extracted value is blank then it will automatically be expanded to the string (no value).

USE keeps track of the rows and columns as they are populated using one or more csv write_fields statements, and will automatically write the fields from left to right starting at the first column in the first data row and will advance to the next row when the rightmost column has been written to.

It is the responsibility of the script author to ensure that the number of fields written to a CSV file is such that when the file is closed, the last row is complete, in order to avoid malformed files with one or more fields missing from the last row.

Example

csv write_fields usage Eddy 47EF-26EA-AAF1-B199 SUB_2311_89EFAA1273
csv write_fields usage Tim 2492-ACC2-8829-4444 SUB_2991_BBAFE20BBA

Close the file

csv closelabel

Once all fields have been written to a CSV file, it must be closed using the csv close statement. This will ensure that all data is properly flushed to disk, and will free the label for re-use.

Example

csv close usage

Example

Consider the file "\examples\json\customers.json" representing two customers:

    {
      "totalCount": 2,
      "items": [
        {
          "id": "1234-4567",
          "companyProfile": {
            "tenantId": "xyz-abc",
            "domain": "example.domain.com",
            "companyName": "Example, Inc"
          }
        },
        {
          "id": "9876-6543",
          "companyProfile": {
            "tenantId": "stu-vwx",
            "domain": "another.domain.com",
            "companyName": "A Company, Inc"
          }
        }
      ]
    }

Using a combination of foreach loops and parslets, the information in the above JSON can be converted to CSV format as follows:

        # Load the file into a named buffer
        buffer customers = FILE "${baseDir}\examples\json\customers.json"

        # Create an export file
        csv "customers" = "${baseDir}\exported\customers.csv"

        # Initialise and fix the headers (using two 'add_headers' statements for illustration)
        csv add_headers "customers" id tenant_id 
        csv add_headers "customers" domain company_name
        csv fix_headers "customers"

        # Iterate over the 'items' array in the JSON
        foreach $JSON{customers}.[items] as this_item
        {
            csv write_field "customers" $JSON(this_item).[id]
            csv write_field "customers" $JSON(this_item).[companyProfile].[tenantId]
            csv write_field "customers" $JSON(this_item).[companyProfile].[domain]
            csv write_field "customers" $JSON(this_item).[companyProfile].[companyName]
        }

        # Tidy up
        csv close "customers"
        discard {customers}

The resulting CSV file is as follows:

    "id","tenant_id","domain","company_name"
    "1234-4567","xyz-abc","example.domain.com","Example, Inc"
    "9876-6543","stu-vwx","another.domain.com","A Company, Inc"