Load CSV files with the UI

The simplest way to load data is to upload a CSV or Excel file from the ThoughtSpot web interface.

You can load data through the web browser for small tables (under 50MB) with simple relationships between them. This method is not recommended for large, repeating data loads. Using this method, the data schema is created for you automatically.

Any user who belongs to a group that has the privilege Has administration privileges or Can upload user data can upload their own data from the browser.

Your data should be in a CSV (comma separated values) before you load it. A CSV file is a text file made up of data fields separated by a delimiter and optionally enclosed with an enclosing character. If your data contains multiple tables, you can have a separate CSV for each table.

Formatting the CSV

Your ETL (extract, transform, load) process will typically generate CSV files. You can also create a CSV file from a Microsoft Excel spreadsheet by opening the spreadsheet in Excel, choosing Save As and selecting CSV.

A CSV file contains a delimiter that marks the separation between fields in the data. The delimiter is usually a comma, but it can be any character. The file also contains fields optionally enclosed with double quotes. Use these guidelines when creating the CSV file:

  • If the CSV contains column headers, they must match the column names in the database exactly.

  • Often a | (pipe) or tab is used as the delimiter, because it may be less likely to occur within the data values.

  • When a field is enclosed in double quotes and it contains a double quote, it must be escaped with another double quote.

  • When a field contains the delimiter, the field must be enclosed in double quotes.

ThoughtSpot supports a wide range of date and timestamp formats. See Date formats. It interprets blank values in user-uploaded CSV files as NULL values. These include these case-insensitive values:

  • NULL

  • \N

  • NA

  • N/A

  • [space]

If you are appending data to an existing schema or table, columns in the CSV file must be in the same order as defined in the target table.

If you are loading a fact table that joins to dimension tables, you must load the fact table first, and then the dimension tables. The joining key must be a single column of unique values in the dimension table. NULL values in the fact table cannot be joined.

Create a CSV file

The first step in loading data is to obtain or create one or more CSV files that contain the data to be loaded into ThoughtSpot. CSV is a common format for transferring data between databases. ThoughtSpot requires this format.

Most applications such as Microsoft Excel or Google Sheets can output CSV formatted files. If your source is an Excel spreadsheet or Google Sheet:

  1. Save, export, or download the file in CSV format. The exact procedure you use will depend on the source application.

  2. Review the file’s format before uploading it to ThoughtSpot.

    Your source data may be in another database. If this is the case, your company’s ETL (extract, transform, load) process will typically generate CSV files. If your source is another database:

  3. Connect to the source database.

  4. Extract each table you wish to import into ThoughtSpot as a CSV file.

    The column delimiter should be a , (comma), | (pipe), or tab.

For general information about CSV files and the rules for creating them, see the Wikipedia article on Comma-separated values.

Load the CSV File

Any user who belongs to a group that has the privilege Has administration privileges or Can upload user data can upload their own data from the browser. To load the CSV or Excel file into ThoughtSpot:

  1. Sign in to ThoughtSpot from a browser.

  2. Click Data, on the top navigation bar.

  3. Select the Utilities tab in the left navigation bar.

  4. In the Upload CSV tile, select Upload CSV.

  5. In Step 1: Upload your file, complete these selections:

    1. Upload the CSV or Excel file by these methods:

      Browse

      Click Browse your files and select the file.

      Drag and drop

      Move the file from the file manager on your computer into the drop area.

    2. Answer the question Are the column names already defined in the file header?.

      The options are Yes or No.

    3. Answer the question Do you want to append to the existing data or overwrite it? by selecting:

      Append

      When adding new data, and not changing what is already in the table

      Overwrite

      When replacing all data in the table with new data

    4. Answer the question Are the fields separated by? by specifying one of:

      Comma

      (,), example: Jacket,Winter 2021,yellow,600
      Semicolon

      (;), example: Jacket;Winter 2021;yellow;600
      Pipe

      (|), example: Jacket|Winter 2021|yellow|600
      Space

      ( ), example: Jacket "Winter 2021" yellow 600
      Tab

      ( ), example: Jacket Winter 2021 yellow 600

      Click Next.

  6. In Step 2: Set column names, review your data.

    When necessary, click the column header names to change them to more descriptive and easier names.

    Click Next.

  7. In Step 3: Set column types, review the automatically generated data types for each column, and make necessary changes.

    You can also change column names here.

    These are the possible data types:

    TRUE/FALSE

    Boolean, Yes/no. See details.
    INTEGER

    Smaller integers represented by 32 bits. See details.
    LARGE INTEGER

    Larger integers represented by 64 bits. See details.
    DECIMAL

    Floating point or Decimal. See details.
    TEXT

    Character or text. See details.
    DATE

    Simple date, not including the time component. See details.
    DATE_TIME

    The date and time. See details.
    TIME

    Only the time, not including the date component. See details.

  8. Click Upload.

    When the file uploads, the system reports the results and offers further actions.

    • Click Link to Existing Data if you want to link the data you uploaded to the data in another table or worksheet.

    • Click the search icon if you want to begin a new search.

    • Click SpotIQ Analyze if you want to use the SpotIQ feature to find insights in your new data.

Append to an existing table

You can append data to your existing system tables through the ThoughtSpot application, even if the tables were initially loaded using tsload. The CSV file must have the same structure as the table it is being loaded into, including number and type of columns, in the same order as the target table.

To append data into ThoughtSpot:

  1. Sign in to ThoughtSpot from a browser.

  2. Click Data on the top navigation bar.

  3. Click the name of the table you would like to append data to.

  4. Click the Load Data button.

  5. Upload the CSV or Excel file by doing one of these options:

    • Click Browse your files and select the file.

    • Drag and drop the file into the drop area.

  6. Answer the question Are the column names already defined in the file header?.

    The options are Yes or No.

  7. Answer the question Do you want to append to the existing data or overwrite it?, select:

    • Append if you are adding new data, and not changing what is already in the table

    • Overwrite if you are replacing all data in the table with new data

      If you choose Overwrite, you lose all existing content in the table, and replace it with new content.

  8. Answer the question Are the fields separated by? by specifying one of:

    • Comma(,)

    • Semicolon(;)

    • Pipe(|)

    • Space

    • Tab

  9. Click Next.

  10. Click Upload.

  11. Click Link to existing data if you want to link the data you uploaded to the data in another table or worksheet. Or click Ask a question if you want to begin a new search.

Troubleshoot uploads

Boolean data must use TRUE/FALSE values. Other values such as Y/N are not supported.

Was this page helpful?
×