Importing Data from Files and Feeds

Importing JSON file

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.

Import data using an OData feed

Refer the below presentation to learn how to import data using an OData feed

Import data from an email attachment

Import data from MS Access

Import data from a Web URL

Import data from Dynamic URL

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. 

Import data from secured/authenticated URLs

Now SkyDesk Reports supports importing data from secured URLs. You can import data from authenticated URL supporting the following authentication methods.

• Basic - Authentication system based on user credentials.
• OAuth 1 - Authentication system based on static Auth token. 
• OAuth 2 - Authentication system based on Auth tokens, which are refreshed. 

To know how to import data from these secured URL, refer here.

Step 2 : (Step 3 in case of Excel/HTML Import): Specify the Import settings

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:

1. First Row Contains Column Names: If the first row of the data being imported contains the columns names, then choose Yes, if not No. SkyDesk Reports will try to intelligently identify whether the first row contains column names and set the option automatically. You can verify this and override/reset it accordingly.
   
   It is recommended that the first row contains the column names. This would enable SkyDesk Reports to use the same column names while creating the table to store the data imported.
   
   If the imported data does not contain the column names in the first row, then SkyDesk Reports will generate its own column names and create the table. You can edit these column names subsequently after the table has been created.
   
   

2. 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.

3. More Settings: Clicking this option will expand the dialog box and provide more import settings appropriate to the file type being imported.

1. • Delimiter separating your column(s): Specify the delimiter character which separates the data in one column from the next. Comma would be the default. If not, you could choose what is relevant for your data set which could be any of Comma, Tab, Semicolon and Space.
     
     
   • Skip top row(s): Specify the number of rows to be skipped or ignored by SkyDesk Reports from the top of your data file being imported. This is optional and the default would be 0 i.e., no rows will be skipped.
      
   • Text Qualifier: If the string values in the data imported are enclosed within Single quote or Double quote, specify the same in this option. The default value is NONE.
     
     
   • Comment Character: If some rows in the data imported are commented and are to be ignored, then you need to specify the comment character that is used to indicate the same. Note that the specified comment character should be present as the first character in that row, if that row has to be ignored.
     
     
   • Thousand Separator: This option lets you to choose your own thousand separator to be used for data types like number, positive number, decimal number and percentage. The default value is NONE.
     
     
   • Decimal Separator: This option allows you to choose your own decimal separator to be used for data types like decimal number and percentage. The default value is DOT.

Preview:

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.

On Import Errors:

You could specify how SkyDesk Reports should handle errors condition (incase it occurs) while importing data. The following are the possible options:

• Set Empty Value for the Column (default) - Select this option to set empty value to the corresponding column value which had problems while importing.
  
  
• Skip Corresponding Rows - Select this option to skip the corresponding rows in which an error occurs while importing.
  
  
• Don't Import the data - Select this option to abort the import process, if any error occurs during importing.

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.

Step 3 : Import Summary

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:

• Column Details: Total number of columns in the input data and the total number of columns that where actually selected for import.
• Row Details: Total number of rows in the input data and the total number of successfully imported rows in it.
• Error/Warning Details: If there were any errors/warning generated during the import process, it would be listed under the Details section.

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.

Scheduling Import from the Web Feeds and FTP Servers

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.

Import Scheduling Options

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:

• Add records at the end - Use this option to append imported records to the end of the table.
• Delete existing records and add - Use this option to delete entire records in SkyDesk Reports table and add the new records into the table.
• Add records and replace if already exists - Use this option to update the already existing records in the table with the new values and append the new records at the end of the table.
• Add records, replace existing and delete missing record - Use this option to update the already existing records with the new values, append new records to the end of the table and delete records that are not available in the file but present only in the SkyDesk Repots table.
  
  

  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.

• Repeat- Specify the schedule interval from the drop-down list. Available options are.
  • Every 'N' hours - Choose this option to schedule the import every 'N' hours i.e., every 2 hour or 6 hours. On choosing this option, specify the time interval to import the data.
  • Every Day - Choose this option to schedule the import at a particular time everyday. On choosing this option, specify the time to import data.
  • Weekly Once - Choose this option to schedule the import once in a week on a particular day. On choosing this option, specify the day of the week and the time to import the data.
  • Monthly Once - Choose this option if you want to schedule the import once in a month on a particular day. On choosing this option, specify the day of the month and the time to import data.

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.