How to Add a CSV File for Conversion into a Dataset

Prerequisites

You need the following before you can add a CSV file for conversion: 

• Admin role - Only admins and roles with editing privileges for Integrations can add CSV files. 
• CSV files in OneDrive - You need to upload the CSV files to OneDrive before you can add these for conversion. 
  If one field in your CSV file contains multiple data values, separate these values with commas. For example, Field A=a, b, c.
  
  
  
  
• Successfully connected to OneDrive integration - MSPbots should be successfully connected to OneDrive before you can perform the steps below, refer to Microsoft OneDrive Integration Setup.

How can I add a CSV file? 

Be guided by the following steps for adding a CSV file:

1. Navigate to Integrations in the MSPbots app.
   
   
   
   
2. Search for OneDrive integration.
   
   
   
   
3. Click the ellipsis   button and select Files. 
   
   
   
   
4. Click + New or + Add a New File button.
   
   
   
   
5. User Account will automatically be filled in the account name connected to OneDrive integration and cannot be changed.
6. Click the    button.
7. Select a drive where the CSV file you want to add are located from the Drives dropdown list.
8. Select a CSV file into the File field.
   • The available CSV files are CSV files in the OneDrive app of the connected OneDrive integration account.
   • You also can click Show File Path to view the path of the CSV file in the OneDrive app.
     
     
     
     
     The CSV file cannot contain any of the following: 
        *   Blank rows
        *   Blank column headers
        *   Duplicate column headers
        *   Symbols in column headers
        *   Column headers that are more than 25 characters long
        *   Numbers with commas
     
           *      Supports only each row with one or more empty values separated by commas, must not be all empty values. For example, a, b, c,  ,  , f,  , h,  .
     
           *      Ensure that the first 10 rows of each column in the CSV file contain values of the same data type, with no empty rows or other data types present. Otherwise, the field type change validation will fail.
     
9. (Optional) Sync By File Name - The default synchronization strategy is based on the file ID. When the box is checked, the system will use the file name as the key for synchronizing the file.
   For example,
   • The dataset generated by this CSV file will always search for the file by its name and then read its contents.
   • If you add this CSV file and successfully generate a dataset, and then rename the file, the dataset previously associated with that file will no longer retrieve data from it.
   • If you upload a file with a name that matches an existing dataset in OneDrive, that dataset should be able to retrieve data from this file.
   • Your choice will be displayed in the CSV file list you have added.
     • If checked, it will show as Sync By File Name.
     • If unchecked, it will show as Sync By File ID.
10. Contains Headers Row - Set whether to display the names of the Headers Row. This option is not checked by default.
    • If the uploaded CSV file contains Headers Row, check this option. If not checked, the generated dataset will not be accessible.
      • When checked, the specific names of the Headers Row will be shown.
        
        
        
        
    •  If the uploaded CSV file does not contain Headers Row, do not check this option. If checked, an alert will pop up.
      • When unchecked, the Headers Row will be displayed as Column 0, Column 1, Column 2, and so on.
        
        
        
        

11. In the Field List settings, set the following options:

    • Dataset Field - You can modify column names as needed, with special characters limited to underscores, for example, test_test.

    • Field Type - Select a field type based on the field value.

      • Text

      • Number - Set Field Type to Number when the field value is decimal or integer, etc.; otherwise,set it to Text.

      • Date Time - Set Field Type to Date Time only when the field value follows the YYYY-MM-DD hh:mm:ss.nnnn and YYYY-MM-DDThh:mm:ssZ format; otherwise, set it to Text.

        • YYYY-MM-DD hh:mm:ss.nnnn (for example 2022-12-25 18:30:45.1234)
          • YYYY - The year in four digits.
          • MM - The month in two digits, ranging from 01 to 12.
          • DD - The day in two digits,, ranging from 01 to 31.
          • hh - The hour in two digits, based on the 24-hour clock.
          • mm - The minutes in two digits, ranging from 00 to 59.
          • ss - The seconds in two digits, ranging from 00 to 59.
          • nnnn - The milliseconds in four digits, ranging from 0000 to 9999.
        • YYYY-MM-DDThh:mm:ssZ (The ISO 8601 standard date time format, for example 2022-06-15T09:30:00-05:00)
          • YYYY - The year in four digits.
          • MM - The month in two digits, ranging from 01 to 12.
          • DD - The date in two digits, ranging from 01 to 31.
          • T is a separator indicating the beginning of the time part.
          • hh - The hour in 24-hour format, ranging from 00 to 23.
          • mm - The minutes, ranging from 00 to 59.
          • ss - The seconds, ranging from 00 to 59.
          • Z - Indicates the time is in Coordinated Universal Time (UTC), which is time without any time zone offset. If a time zone offset follows the time, such as -05:00, it signifies a 5-hour difference from UTC.

      • Boolean - Set Field Type to Boolean only when the field value is True or False; otherwise, set it to Text.

           *   Make sure that each column name is unique in Dataset Field, without any duplicates.
           *   Hover over to display the data in the first row regardless of whether you have checked Contains Headers Row.
        
        
        
        

12. Click Test Field Types.
    
    
    
    

13. When all statuses are displayed as , click the Save and Sync button.

14. Wait for about 20-30 minutes, and the dataset will be successfully generated.

    • The Sync Strategy indicates the criteria used for generating the dataset from the csv file, whether it is based on file ID or file name to search for files and sync data.

    • You can click on the hyperlink of the generated Dataset to navigate to the dataset.

    • Last Updated means the most recent update time of the file on OneDrive.

    • The syncStatus represents the status of the data synchronization, either Sync Failed or Sync Success.

    • If you want to delete the uploaded CSV file,

      1. Click the Remove button on the row of the file.

      2. Click Remove to delete it.
         After deletion, you can still find the generated dataset in Datasets.
         
         

15. After the dataset is successfully generated, we will automatically sync the data when there are any updates to your CSV file.
    • If you add or delete columns in the CSV file, we are unable to automatically sync the data and update your dataset. You can submit a request through our Help Center, and we will manually update your dataset, or you can re-add this CSV file to generate a new dataset.