CSV to/from Database

Table to CSV – table2csv

This operation exports a customizable dataset from a table into a CSV file. You can export data from specific columns (properties) and/or specific records (with a where clause). Additionally, you can export either a specific block/page of data or the entire data set. The CSV file can be created as new or you can append the export to an existing CSV file. The operation has four parameters:

• filePath – String value. Required. A path to a CSV file located in the Backendless File storage of your app. The database table data will be exported into the specified file. The file is created if doesn’t exist. You can append to an existing file using the "append" option in the csvOptions parameter. If the value does not reference a directory and specifies only the file name, the file is created/updated in the root directory of your file storage.
• tableName– String value. Required. The name of a database table or a view from where the data will be exported.
• tableOptions– Object value. Optional. Configuration options to identify a subset of records from tableName. For more information see the tableOptions Structure below.
• csvOptions– Object value. Options. Configuration options to control the created CSV output. For more information see the csvOptions Structure below.

Return value

The method returns a URL for the CSV file created (or updated) by the operation.

Codeless usage

The Codeless logic below illustrates the basic usage of the API:

tableOptions Structure

Parameters in this structure help you select database records that fit specific criteria. All individual parameters are optional.

• where  – String. Backendless where clause. The API will export only the records that match the where clause condition.
• properties – An array/list of the column names to include in the export. By default, all columns from the source table are included. By using properties, only the specified column names will be included in the export.  In addition to property names, you can specify aggregate and database functions. You can use related column names. Use the dot-notation to identify columns of a related-of-a-related table. When referencing a related column for a one-to-many relation, you will get multiple exported records for the same parent record. The system exports a separate record for each combination between the parent and child records. To prevent this behavior, use the distinct parameter.
• excludeProps – An array/list of the column names to exclude from the export. If properties is not set, all column names from the source table are included. You can exclude specific column names using excludeProps.
• sortBy – An array/list of string values. Each value in the list must be a name of a column to sort the resulting export by. By default, the sorting order is ascending, to request the descending order, add “DESC” to the name of the column (separated with the space character).
• distinct – Boolean value, default is false. When set to true, the resulting export will exclude duplicate records based on the objectId value. This may be needed when a property name from a related one-to-many table is added to the properties parameter.
• relations – An array/list of related column names. When a related column name is added to the relations property, the related objects for the specified relation column are added to the export in the JSON format. These JSON objects become individual CSV values for a separate column dedicated to the related table.
• relationsDepth – A numeric value representing the depth of the related objects to include in the scope of the export operation. For more information about relation depth, see the Backendless API documentation.
• groupBy – An array/list of column names to group the export by. Grouping makes more sense when you use an aggregate function in the properties parameter.
• having – A string value containing a having clause used by aggregate functions. Should be used for result filtering when an aggregate function is used in the properties parameter. For additional information see Aggregated Data Filtering in Backendless API documentation.
• fileReferencePrefix – A string value containing a prefix added to the file paths stored in the database columns of the FILEREFERENCE data type. When the file path value in the database is for a file stored in the Backendless file storage, it is in a relative format starting with the root directory. By default, the Backendless database completes the path to make it a full URL when an object is retrieved for export. Using fileReferencePrefix, you can set a specific prefix (such as a domain name) that will be used to build an absolute file URL.
• pageSize – An integer value. Specifies how many records to include in the export. If no pageSize is set, offset will be ignored and all records from the database are exported.
• offset – An integer value. Assuming the very first record in the data set to be exported has an index of 0, this parameter sets the index of the first record to be actually exported. The number of records to export is set with the pageSize parameter. If the pageSize value is not set, the offset parameter is ignored.
• columnsMapping – Object. Provides instructions for how table columns should be mapped to the “columns” in the CSV file. The object should use table column names as keys and the assigned column in CSV as value. For example:

  {
    "amount":"order amount",
    "name":"customer name"
  }

  With the mapping above, the "amount" and "name" columns from the database table will be named "order amount" and "customer name" respectively in the export CSV.

csvOptions Structure

Parameters in this structure can configure various aspects of the export CSV. All parameters are optional.

• append – A boolean value (true or false). When set to true, the exported records are appended to the CSV file if it already exists.
• delimiter – A string value.  Sets the field delimiter. Can be one or multiple characters. Default is the comma character.
• escape – A string value, must be a single character. Used for escaping double quotes in the resulting CSV. The default value is ".
• header – A boolean value (true or false). Adds a header row in the CSV. The row contains the names of the columns.
• quote  – A single character or an empty string.  The value is used to quote/wrap string values from the database. String value wrapping is controlled by the quoted, quoted_empty, and quoted_string. The default quote parameter value is ".
• quoted_string – A boolean value (true or false). When set to true, unconditionally wrap/quote into the quote character all values of type string; The default value is false.
• quoted  – A boolean value (true or false). When set to true, unconditionally wrap/quote into the quote character all the non-empty values of type string; The default value is false.
• quoted_empty – A boolean value (true or false). When set to true, unconditionally wrap/quote into the quote character all empty values of type string; The default value is false.
• record_delimiter  – A string value. The value is used to delimit record rows. Delimiting rows means how rows end or what separator/delimiter is used to identify the end of a row. Supported special values are "auto", "unix", "mac", "windows", "ascii", "unicode"; The default value is "auto" (discovered in the target file or "unix" if the target file is created by the operation).
• eof – A boolean value (true or false). When set to true, adds the value of "record_delimiter" on the last line of the CSV file. The default is true.

Examples

Consider the following data table:

The related Address table has the following structure and data:

Example 1

The example below demonstrates the following capabilities:

• Exporting records that match a query address.country = 'United States'. Notice the query references a column in a related table
• Exporting specific columns/properties. Notice two of the columns come from the related table
• Adding a header in the resulting CSV
  

A CSV created in this example has the following data and the structure:

Example 2

The example below demonstrates the following capabilities:

• Exporting specific columns/properties.
• Exporting related records – notice the relations parameter.
• Sorting the exported records – notice the sortBy parameter.
• Adding a header in the resulting CSV
• Configuring the string values to be unconditionally surrounded by (wrapped into) double quotes.
  

A CSV created in this example has the following data and the structure:

"name","phoneNumber","address","objectId"
"Bob","555-4343","{""country"":""United States"",""city"":""Dallas"",""created"":1626484471000,""___class"":""Address"",""ownerId"":null,""updated"":1642509292000,""objectId"":""A36DDF2E-D239-457C-989A-B9F2319A58BC""}","8D79BA7D-ACE3-47DC-9D90-B5985CDDD363"
"Jack","555-9898","{""country"":""United States"",""city"":""New York"",""created"":1628465921000,""___class"":""Address"",""ownerId"":null,""updated"":1642509288000,""objectId"":""6F68AE45-43D7-4E3D-92E3-D9DCE37F0EA3""}","FC584FF8-E44B-408D-B9F4-F7CF387CD538"
"Joe","555-1212","{""country"":""United States"",""city"":""Miami"",""created"":1642509300000,""___class"":""Address"",""ownerId"":null,""updated"":1655507848005,""objectId"":""F1202A07-3775-4AEE-AC72-9EA0FC1BC54C""}","765EF32B-A715-4C54-84D6-EB1E8B622121"