Often you would already have data locally stored in tabular file formats like CSV, XLS (Excel), JSON, and HTML files. The data in such formats could also be available as a URL or Web feed or even some application generated. SkyDesk Reports offers easy import and copy-paste options of such data for jump starting your reporting and analysis. You can import the data into SkyDesk Reports by clicking the link Import from Files and Feed provided in any of the following contexts :
On clicking the Import from Files and Feed, an import dialog will pop-up. The first screen of the dialog will be different depending on context from where you invoked this dialog.
If you had invoked this from under Create Workspace, the first screen will look like the following. In this screen, you need to provide the database name (unique and mandatory), description (optional) and tags (optional) describing the database. Other options are common and described below:
If you had invoked this from New Table option, the first screen will look like the following. In this screen you need to provide the Table name (unique within the database and mandatory), description (optional) and tags (optional) describing the table. Other options are common.
Currently SkyDesk Reports supports importing data from the following sources.
Choose the Data Location from where the input data file is to be loaded. You can choose any of the following:
In case you choose this option you need to follow the below steps:
Please do note that, your User Name and Password will not be saved by default. It will be saved only if you chose to schedule the import.
Once you have provided the data input, click on the Next button at the bottom to proceed to the next step.
Note:
In case you have chosen to import an HTML or Excel file, you will see a screen as shown below in Step 2.
You will see the list of tables(sheets in case of XLS) that are present in the input file. You need to choose the table/sheet from which the data has to be imported. You can only import one table/sheet at a time. Once you had chosen the table/sheet, click on Next button to proceed importing.
If there had been no tables in the input HTML file, then you would not be able to proceed to the next step until you provide a valid input.
When importing data from Excel file, if the excel table contains hidden rows or columns, by default, only the visible rows will be imported in to the SkyDesk Reports table. However, SkyDesk Reports also allows you to import the hidden rows/columns by providing corresponding options under Import Hidden Rows/Columns option in this step of the wizard. Options provided are:
SkyDesk Reports supports importing data from JSON files. To do this set the File Type as JSON and select the required file or paste the JSON format in the Pasted Data field.
Note
Refer the below presentation to learn how to import data using an OData feed
Now you can import data from a dynamic URL using the parameter. You specify Current Date, Current Time and Current Date & Time as a dynamic value for the parameter to import.
Now SkyDesk Reports supports importing data from secured URLs. You can import data from authenticated URL supporting the following authentication methods.
To know how to import data from these secured URL, refer here.
In this step you would be prompted to provide the necessary settings for SkyDesk Reports to properly understand the data being imported.
The settings that are to be provided are described below:
By default, SkyDesk Reports tries to identify the date format of the given date column from the data provided and display the same. If you find that to be incorrect or if SkyDesk Reports had failed to recognize any date column then you can set the date format in the Format of Date Column(s) option. This date format will be applied to all date columns in the data being imported.
You can also set different date formats for each date column. Click the settings icon in the preview column header and select the Change Date Format option.
Now set the date format of the column.
To know more on how to construct custom date format, refer to this link.
SkyDesk Reports shows a preview of the data being imported by displaying the first 3 rows in a tabular format as shown below.
In the preview table shown you can verify the column names and the column type of the data being imported. If required, you can change the same too.
To change a Column Name, double click on the corresponding column header. This will switch the header into edit mode. You can then provide the required name and press the Enter key to set the same.
To change the Column type, choose the type from the drop list provided under each column. Ensure that you specify the right column type. Specifying a wrong type will generate errors on import and make SkyDesk Reports to ignore the value in that corresponding column while importing.
You could specify how SkyDesk Reports should handle errors condition (incase it occurs) while importing data. The following are the possible options:
Incase any error occurs during import, the details of the same would be shown in the Import Summary dialog (refer the next section below) which would be shown on Import process completion.
Once you have set all the options, click Create button provided to proceed Importing the data.
Once SkyDesk Reports imports the input data provided, it will bring up a Import Summary dialog as shown below, capturing the status of the Import process just completed.
The details that would be shown include:
If you import data from an URL, you would see a link Would you like to schedule this import periodically? which you can use to setup periodic import of data from this URL. Follow this link for more details.
On closing the Summary dialog, SkyDesk Reports will display the newly created table with the data loaded in the same.
You could setup scheduled imports from your data source if you have imported the data from the Web or FTP servers. If you have imported data from these sources your Step 3 dialog will look as shown below.
You can schedule imports by clicking the Schedule This Import button.
The Schedule Import Settings dialog will open listing the below options. The scheduling options for both Web feeds and FTP servers are similar.
How do you want to Import? - Choose how do you want to import from this drop-down list. Available options are:
Note: When Add records and replace if already exists or Add records, replace existing and delete missing record is selected, it is mandatory to specify matching columns using the Select the column(s) to match existing records option. You can choose one or more columns as matching columns. SkyDesk Reports will use the values in these columns as the key to match the existing records with the new records being imported. Hence the matching column(s) combination should uniquely identify each record in the table. Once a match is identified, then SkyDesk Reports will update that existing record in the table with the new record values present in the data being Imported. Incase there is no match found, those records will be appended to the end of the table.
Schedule Settings-This group box provides options to schedule the import.
Note: By default schedule time will be specified in your local timezone. You can also change this using the Select TimeZone option.
Note - Joining Tables |
|
SkyDesk Reports allows you to blend data across multiple tables using a special feature called Auto-Join. This feature automatically joins tables, if the tables are connected using a Lookup column. For this feature to work, you need to define a lookup relationship between two tables using a common column. Click here to know more. While importing a new table into an existing database, SkyDesk Reports auto identifies columns with the same column name and datatype and provides suggestions for look up. You can create a look up for these columns by clicking the Join Tables: Lookup column auto-identified link at the bottom (In step 2 of 2: Create Table). Incase SkyDesk Reports is not able to auto identify columns for lookup, you can create your own lookup by clicking the Join Tables link. The Join Tables dialog will open displaying the list of matching columns (that can be looked up with the existing table). Please do note that multiple join between the same two tables are not allowed. By default the column from the existing table is defined as parent and the column from the new table is defined as child. This parent and child defines how the tables are joined and combined (Click here to know more). You can swap the relationship by clicking the circle connecting the parent and child columns. To remove the look up suggestion click Remove adjacent to the suggestion. You can also define a look up column by clicking the +Add Lookup link. Click Save And Close to save the suggestion. |