After a workbook is created, you can write data to the workbook for data analysis. You can also import data from a data store or import local data to the workbook. This topic describes how to import data to a workbook.

Prerequisites

A blank workbook is created. For more information, see Create a workbook.

Limits

The Query mode feature supports the following data store types and regions:
  • Supported data store types: MaxCompute, MySQL, PostgreSQL, DRDS, SQL Server, Oracle, AnalyticDB for MySQL V2.0, AnalyticDB for PostgreSQL, Hologres, EMR Hive, and EMR Spark SQL.
  • Supported regions: China (Shanghai), China (Beijing), China (Hangzhou), China (Shenzhen), China (Chengdu), China (Hong Kong), China (Zhangjiakou), China North 2 Ali Gov, Singapore, Indonesia (Jakarta), and Japan (Tokyo).

Go to the workbook editing page

  1. Go to the DataStudio page.
    1. Log on to the DataWorks console.
    2. In the left-side navigation pane, click Workspaces.
    3. In the top navigation bar, select the region where your workspace resides, find the workspace, and then click Data Analytics in the Actions column.
  2. On the DataStudio page, click the Icon icon in the upper-left corner and choose All Products > Data Development > DataAnalysis.
  3. On the DataAnalysis homepage, click Experience Now. The Web Excel page appears.
  4. In the All Spreadsheets section of the Web Excel page, click the name of the workbook that you want to edit in the File Name column to go to the workbook editing page.
    If you create a workbook in this step, the workbook editing page appears after the workbook is created. For more information, see Edit a workbook and analyze data.

Import data from a data store

You can use the Query mode feature to import data from a data store to a workbook for data analysis.

  1. In the upper-left corner of the workbook editing page, click Analysis mode to switch to Query mode.
    Analysis mode
  2. Add a data store.
    If the data store from which you want to import data is already in the data store list, you can click the data store type and double-click the data store.
    1. In the Data source section, click the + icon.
      Query mode
    2. In the Select Datasource dialog box, select a data store type and set the Data Source or Engine Instance parameter as prompted.
    3. Click OK.
  3. Import data from the data store to the workbook.
    1. Compile the code for querying data from the data store.
      You can compile the code for querying data from the data store by using one of the following methods:
      • Enter a query statement in the code editor.
        Note The query statement must follow the syntax that is specified by the data store type.
      • Double-click the name of the data store to view all tables in the data store. Double-click the name of a table or field to generate a query statement.
    2. Click the Run icon icon in the top toolbar. After the query is complete, you can view the imported data in the workbook.
      Run icon
    3. Save and manage query statements.
      In the lower part of the right-side pane, you can view the query statements that you have saved and run on the Saved query and History tabs. You can load, rename, or delete query statements as needed. You can also perform the following operations in the code editor:
      • Click the Save query icon icon in the top toolbar. In the Save dialog box, enter a name for the query statement in the File Name field and click OK.
      • Click the New icon icon in the top toolbar to clear the query statement in the code editor. After the query statement is cleared, you can enter a new query statement.
      • Click the Settings icon icon in the top toolbar. In the Settings dialog box, set the Data Placement Location, Data Placement, and Get Data With Header parameters and click OK.Settings dialog box
  4. After you query data from the data store, you can click Query mode to switch to Analysis mode in the upper-left corner of the workbook editing page. This way, you can perform data pivoting and profiling. For more information, see Edit a workbook and analyze data.
    Switch icon

Import local data

On the workbook editing page, move the pointer over Import in the upper-right corner and select Local File or File Data to import local data.
  • If you select Local File, you can import only Excel files from an on-premises machine. Data in all sheets of a selected Excel file is imported.

    Move the pointer over Import and select Local File. Select an Excel file and click Open to import data in all sheets of the Excel file to the workbook.

  • If you select File Data, you can import data from workbooks or import CSV files or Excel files from an on-premises machine. If you import data from a workbook or an Excel file, you can specify the sheet from which the data is to be imported.
    Move the pointer over Import and select File Data. In the Import dialog box, select one of the following types of source data based on your needs:
    • Spreadsheet
      In the Import dialog box, click Spreadsheet, set the parameters, and then click OK.
      Parameter Description
      Spreadsheet The workbook from which the data is to be imported. Select a workbook from the Spreadsheet drop-down list.
      Sheet The sheet from which the data is to be imported. Select a sheet from the Sheet drop-down list.
      Data Preview A section that displays the data in the selected sheet.
      Import Start Row The row from which the data is to be imported. Default value: 1.
      Placement Location The location where the imported data is placed. Valid values: Current Worksheet and New Worksheet.
      Placement Method The way in which the imported data is placed. Valid values: Append, Overwrite, and Active Cell.
    • Local CSV File
      In the Import dialog box, click Local CSV File, set the parameters, and then click OK.Import dialog box
      Parameter Description
      File The CSV file from which the data is to be imported. Click Select File(.csv), select a CSV file from the on-premises machine, and then click Open.
      Original Character Set The character set that is used by the selected CSV file. Valid values: UTF-8 and GBK. If garbled characters appear, you can change the character set.
      Separator The row delimiter and column delimiter.
      • Valid values of row delimiters: \r\n, \n, and \r.
      • Valid values of column delimiters: ,, ;, and \t.

      If the cell data cannot be correctly divided, you can change the delimiters.
      Data Preview A section that displays the data in the selected CSV file.
      Import Start Row The row from which the data is to be imported. Default value: 1.
      Placement Location The location where the imported data is placed. Valid values: Current Worksheet and New Worksheet.
      Placement Method The way in which the imported data is placed. Valid values: Append, Overwrite, and Active Cell.
    • Local Excel File
      In the Import dialog box, click Local Excel File, set the parameters, and then click OK.Excel
      Parameter Description
      File The Excel file from which the data is to be imported. Click Select File(.xlsx), select an Excel file from the on-premises machine, and then click Open.
      Sheet The sheet from which the data is to be imported. Select a sheet from the Sheet drop-down list.
      Data Preview A section that displays the data in the selected sheet.
      Import Start Row The row from which the data is to be imported. Default value: 1.
      Placement Location The location where the imported data is placed. Valid values: Current Worksheet and New Worksheet.
      Placement Method The way in which the imported data is placed. Valid values: Append, Overwrite, and Active Cell.