Manage File Uploads
This applies to: Visual Data Discovery
Symphony can visualize data from file uploads including CSV, JSON, and TSV files.
The maximum supported file size is 500 MB
Data from the file upload is stored in a PostgreSQL database
-
When you upload a flat data file, the first 1,000 records are used to determine if fields are imported as NUMBER or INTEGER.
If there are no decimal number records in the first 1,000 records, the numeric fields are imported as INTEGER instead of NUMBER: any decimal number records after row 1,000 may not upload fully.
To ensure all records are imported, sort your data to ensure decimal numbers are included in the first 1,000 records.
Before you can establish a connection from Symphony to your file uploads storage, a connector server needs to be installed and configured. See Manage Connectors and Connector Servers for general instructions.
After the connector has been set up, create a data source configuration and upload your file or files to that source. See:
Upload a New File in Visual Data Discovery
Log in as a user with the Administer Sources or Create New Data Sources privilege.
-
Select Add to add a new data entity, then select From File.
-
Add a unique Data Entity Name, then select Upload New File. The File Upload work area opens.
Enter File Details, such as a unique Display Name, and optional Description.
Use the Browse button to select a file to upload.
After you have selected a file, Symphony may autofill the Single Quote Char. and Field Delimiter fields. Adjust if needed.
If needed, change the No. of records to display from 10 to up to 1,000 records.
Enable or disable the checkbox Column Headers in first row to match your file layout. If no column headers are in your data, Symphony assigns numerical column headings, field_1, field_2, and so on.
Optionally, select Preview to preview your data.
Enable the checkbox Use Only File Structure to create the file using only the existing file structure, no data.
Enable the checkbox Disable Integer to Time Auto Detection to prevent auto detection of time fields.
Select Save to close the File Upload work area and continue creating or updating your data entity for this source.
Edit an Existing File in Visual Data Discovery
Log in as a user with the Administer Sources or Create New Data Sources privilege.
-
Edit an existing data source.
Select the data entity with the file you want to edit, then select Edit File. The File Upload work area opens.
-
Browse for a new file.
If you are replacing the existing file, your new file must use the same data file structure as the existing file. Enable the Replace checkbox in Upload Settings: previous data is replaced.
If you are adding data to the existing file, your new file must use the same data file structure as the existing file. Disable the Replace checkbox in Upload Settings: previous data is appended with new rows of data.
-
Select Preview to preview your data.
The Current Data tab shows the data of your existing file.
The New Data shows the replaced or amended data preview.
Select Save to Save your changes or Cancel to discard your changes. The File Upload work area closes.
API Endpoints
Log in as a user with the Administer Sources or Create New Data Sources privilege.
-
-
Select the data entity with the file you want to edit, then select API Endpoints. The API Endpoints dialog box opens.
The dialog offers convenient example cURL requests but the APIs can be leveraged from your preferred development platform.
-
Copy and modify the example cURL requests to include your own Symphony credentials, replacing the placeholders for username and password. Select Close to close the dialog.
Work with the Upload API
There are two operations that can be performed using the Upload API: appending additional data and clearing previously uploaded data. The source creation page offers convenient example cURL requests but the APIs can be leveraged from your preferred development platform. Select API Endpoints on the source creation tab to edit your data.
Modify the example cURL requests to include your own Symphony credentials, replacing the placeholders for username and password.
curl -v --user <username>:<password> <YourServer>
Example: Append Data
In the following example, the Upload API accepts an array of JSON objects. Note that the object field types must match those used to create the Upload API source originally. For example, if the value of the price field is a number, you can not upload new rows in which the value of the price field is a string.
Example: Clear Previously Uploaded Data
In the example below, the Upload API will clear all previously uploaded data from the data source with the ID of <YourDataSourceId>.
curl -v --user <username>:<password> 'https://<Your_Symphony_Server>/ api/upload/<YourDataSourceId>' -X DELETE --insecure
Feature Support
File upload support for specific features is shown in the following table.
Key: Y - Supported; N - Not Supported; N/A - not applicable
| Feature | Supported? |
|---|---|
Y |
|
Y |
|
Y |
|
Y |
|
Y |
|
N/A |
|
Y |
|
Y |
|
Y |
|
Y |
|
Y |
|
N |
|
Y |
|
Y |
|
N/A |
|
N/A |
|
N |
|
Y |
|
Y |
|
N/A |
|
Y |
|
N |
|
Y |
|
Y |
|
Y |
Connecting to Flat Files in Managed Dashboards
This applies to: Managed Dashboards, Managed Reports
Drag and Drop a Flat File
Just like with Excel files, you can drag a CSV or other text file from Windows Explorer or Finder and drop it onto the Symphony Explore window or the dashboard canvas.
This will automatically import the flat file data to Symphony and create a corresponding data connector.
Connect to a CSV File
As an alternative to dragging and dropping as shown above, the following walkthrough shows you how to manually create a data connector for a text file which contains comma-delimited stock price data.
To begin, go to the main menu, select New, and then select Data Connector.
In the New Data Connector dialog, click inside the Name box, and enter a name for your data connector.
Select the Data Provider drop-down and choose Flat Files.
Next, specify the CSV file using one of two options:
Select Choose File and use the file selector to select the CSV file to be imported. Note that changes to the original file will no longer affect the generated data connector.
Or, use the Text File field to specify the full network path or web address to the file (e.g., \\server1\shared\stockdata.csv). Note that changes to the original file will affect the data once the data connector gets refreshed. Use the Authentication setting above to change how Symphony should access the file if access is restricted.
For CSV files, the first line of data often contains the header text values. In this case, make sure the First Line Is Header option is checked.
If your data values are not surrounded by quotation marks, click to expand the Advanced section and clear Fields Are Enclosed In Quotes.
The Define Structure option lets you define the table and column names and data types, but it is recommended to just select Submit at the bottom of the dialog and let the auto-discovery run first. You can then go back and edit the discovered columns if needed.
Go to Explore and locate the newly added data connector. Expand it to see the CSV file and its discovered columns. Drag the file to the dashboard canvas to see its values displayed in a table visualization (in Raw Data format).
Note: An imported file will be automatically warehoused after the first use to improve performance.
Define Columns
Symphony can automatically discover the structure of the file, as well as details such as the delimiter character and culture code page when the data connector is first created. Afterwards, you can redefine these settings as needed.
First, locate the flat file data connector from the main menu or from the Explore window. Right-click (or long tap) on the data connector and select Edit from the context menu.
The Character Used As Delimiter is auto-detected by default, but you can change it if necessary. If your text file is delimited by Tab characters, just click inside the box, delete any existing character, and then type the Tab key, or right-click and choose the tab option.
In the Edit Data Connector dialog, scroll down and select Define structure.
In the Define Data Structure dialog, select the table and then scroll down to see the discovered columns. The columns are discovered on-demand so if you don't see any listed, select Re-discover table.
Select a column and then set the details for the column as needed. For example, date/time columns are discovered as string columns so you'll want to change the data type to Date/Time.
Read Multiple Files
Symphony can read multiple files under the same folder. To do this, specify the path using a wildcard character as a filename. Files with the same structure will be loaded simultaneously.
For example, the folder below contains 2 CSV files with the same structure:
In the Text File field, specify the path using a wildcard character to represent the part of the filename that changes. For example, \\server1\shared\*.csv.
The data from all files will be included together, and File Name is one of the columns you can access:
For more information, see: