Loading data through the Web browser is recommended for smaller tables (under 50MB) with simple relationships between them. This method is recommended for small, one time 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’ll have a separate CSV for each table.
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:
- Save, export, or download the file in CSV format. The exact procedure you use will depend on the source application.
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:
- Connect to the source database.
Extract each table you wish to import into ThoughtSpot as a CSV file.
The column delimiter should be a
|(pipe), or tab.
Large organizations typically have a data administrator or department the builds ETL processes. If the data you want is in another system, speak with your ThoughtSpot administrator about getting CSV files extracted from this system.
Formatting the CSV
A CSV file contains a delimiter that marks the separation between fields in the data. The delimiter is usually 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 contains a double quote, it must be escaped with the character specified in the escape character argument in
- When a field contains the delimiter, the field must be enclosed in double quotes.
ThoughtSpot supports a wide range of date and timestamp formats in the CSV file. Blank values in user uploaded CSV files are interpreted as NULL values. These include the values (case insensitive):
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.
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:
- Log into ThoughtSpot from a browser.
Click on DATA, on the top navigation bar.
Click the Actions button in the upper right corner, and select Upload Data.
- Upload the CSV or Excel file by doing one of these options:
- Click on Browse your files and select the file.
- Drag and drop the file into the drop area.
- Answer the question Are the column names already defined in the file header?
- Answer the question Are the fields separated by? Click Next.
- Click on the column header names to change them to more useful names, if you’d like. Click Next.
- Review the automatically generated data types for each column, and make any changes you want. There are four data types: Text, Integer, Decimal, and Date.
When an upload is complete, the system reports the results and offers you some 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 Search if you want to begin a new search.
- Click Auto analyze if you want to use the SpotIQ feature to find insights in your new data.
Boolean data must use
FALSE values. Other values such as
N are not supported.