Spreadsheets S3 is a software tool that allows users sync data from spreadsheets stored in Amazon S3 into their chosen targets.

Prerequisites

To obtain the Tables required setting for connecting to Spreadsheets Anywhere, you need to have access to the spreadsheet that you want to connect to. Once you have access, you can identify the name of the table or tables that you want to connect to. The table name should be entered in the appropriate field when setting up the connection to Spreadsheets Anywhere. The AWS Access Key ID and AWS Secret Access Key are the connection credentials for connecting to your S3 bucket.

Enable BATCH mode for large syncs (optional)

Instead of one record message per row, the tap can buffer rows and write them to Arrow IPC files, then emit batch messages that reference them - a compatible target loads these files directly instead of processing records one at a time. Set Batch Format, Batch Root Directory and Batch Size to enable it. Caveats: arrow is the only batch encoding this tap supports; only a target that advertises the batch capability processes batch messages, so leave these settings unset for any other target. Enabling BATCH mode does not otherwise change the schema seen downstream - add "arrow_native_timestamps": false to a table entry in Tables to keep its date-time columns as strings instead of native Arrow timestamps (the default), for a table with non-ISO-8601 date formatting.

Settings

Tables

A list of table definition objects

Table definition

• name: string (required)

  The name to assign to the stream

• path: string (required)

  The bucket containing the files, in the format s3://<bucket_name>

• format: string (required)

  The format of files to sync - one of csv, json, jsonl, excel or detect

• pattern: string (required)

  A regex pattern to filter resolved files on name by - set to "" if path specifies some kind of filtering mechanism (e.g. glob pattern matching) and no more granular filtering is required

  The pattern is matched against the object keys listed from the bucket, so it can also target a folder (prefix) - e.g. exports/sales_.*\.csv for the sales CSV files in the exports folder

• start_date: string (required)

  An ISO-8601 date-time to filter resolved files on last modified timestamp by

• key_properties: array of strings (required)

  The stream primary keys - for files where a primary key cannot be clearly identified, you can reference meta-properties

  [
    "_smart_source_bucket",
    "_smart_source_file",
    "_smart_source_lineno"
  ]
  

  or [] for append-only behaviour

  If using the meta-property approach outlined above, be aware that changes to file locations or contents may result in unexpected duplicates or overwrites - therefore, it is safest to use this approach if the targeted files are, for all intents and purposes, immutable

• encoding: string (default: utf-8)

  The encoding to use when reading files

• skip_initial: integer (default: 0)

  The number of lines to skip over when reading a file - mostly useful for excel format files

• max_sampled_files: integer (default: 50)

  The number of files to sample during dynamic catalog discovery

• max_sampling_read: integer (default: 1000)

  The number of lines to sample for each file during dynamic catalog discovery

• sample_rate: integer (default: 5)

  Controls how frequently lines are sampled (i.e. every nth line) during dynamic catalog discovery

• prefer_schema_as_string: boolean (default: false)

  Whether or not to skip inferring property types during sampling - if using with CSV files, you can set max_sampling_read to 1 alongside this to improve discovery performance (only the header row needs to be sampled to resolve the property set, given that all values will be treated as strings)

• arrow_native_timestamps: boolean (default: true)

  Only consulted in Arrow BATCH mode (see below) - whether date-time columns are typed as native Arrow timestamps (parsed with a strict ISO-8601 parser) rather than left as unparsed strings. Set to false for a table with non-ISO-8601 date formatting.

csv format only

• delimiter: string (default: ,)

  The value delimiter sequence used in the targeted files

• quotechar: string (default: ")

  The quotechar delimiter character used in the targeted files - set to detect to auto-discover

excel format only

• worksheet_name: string

  The specific worksheet name to pull out - defaults to the sheet with the most available data

See the tap README for further information and other miscellaneous settings

Batch Format

Format of the batch files. This tap only supports Arrow.

Batch Size

Number of rows per BATCH message file. Defaults to 100,000 if not specified.