This topic describes how to use Data Management (DMS) to import data from an Excel file to a database on an ApsaraDB RDS for MySQL instance.

Procedure

  1. Save the Excel file as a CSV file and change the column names that do not meet the database requirements to valid column names.
    Note If the file does not contain a column that uniquely identifies each row, we recommend that you add such a row as a primary key column to facilitate storage and queries. For example, insert data into column A to use column A as a primary key column.
    Excel data
  2. Log on to your RDS instance by using DMS. For more information, see Use DMS to log on to an ApsaraDB RDS for MySQL instance.
  3. Select the destination database and execute the CREATE TABLE statement on the SQL Console tab of the DMS console to create a table.

    Example

    CREATE TABLE `test1` (
      `id` int(11) NOT NULL,
      `order_id` int(11) DEFAULT NULL,
      `report_date` varchar(32) DEFAULT NULL,
      `customer_name` varchar(32) DEFAULT NULL,
      `order_level1` varchar(32) DEFAULT NULL,
      `shipping_type` varchar(32) DEFAULT NULL,
      `area` varchar(32) DEFAULT NULL,
      `province` varchar(32) DEFAULT NULL,
      `city` varchar(32) DEFAULT NULL,
      `product_type` varchar(32) DEFAULT NULL,
      `shipping_cost` float(10,0) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    Note The tables in the RDS instance and in the Excel file must use the same schema.
  4. In the top navigation bar, click Database Development. In the left-side navigation pane, choose Data Change > Data Import.
  5. In the Apply section of the Ticket Application page, configure the following parameters.
    Parameter Description
    Database The name of the database to which the file is imported.
    Note If the destination database is managed in a database instance in Flexible Management or Stable Change mode, make sure that you have logged on to the database. Otherwise, the database is not displayed for you to select.
    File Encoding The character set used by the file to be imported. Select Automatic Identification.
    • UTF-8
    • GBK
    • ISO-8859-1
    • Automatic Identification
    Import mode The import mode. Valid values:
    • Speed Mode: If you use this mode, the SQL statements in the uploaded file are read and directly executed in the Execute step to import data to the specified destination database. Compared with the security mode, this mode can be used to import data in a less secure but more efficient manner.
      Note By default, the speed mode is disabled for a database instance that is managed in Security Collaboration mode. You can enable the speed mode by performing the following operations: Go to the Details page of the security rule set that is applied to the database instance and click the SQL Correct tab. In the list below the Basic Configuration Item checkpoint, modify the Whether data import supports selecting speed mode configuration.
    • Security mode: If you use this mode, the uploaded file is parsed, and the SQL statements or CSV file data in the uploaded file is cached in the Precheck step. In the Execute step, the cached SQL statements are read and executed to import data, or the cached CSV file data is read and imported to the specified destination database. This mode can be used to import data in a more secure but slower manner.
    File type The type of the file to be imported. Select CSV.
    • SQL Script: By default, you can use only the INSERT and REPLACE statements to import data to database instances that are managed in Security Collaboration mode. If you want to use other SQL statements to import data, modify the security rules for data import as a database administrator (DBA) or DMS administrator. You can modify the security rules by performing the following operations: Go to the Details page of the security rule set that is applied to the database instance and click the SQL Correct tab. In the list below the Batch Data import rules checkpoint, modify the security rules as needed.
    • CSV: The delimiters in the file must be commas (,).
    • Excel: The file can contain table headers and data, or contain only data.
    Target Table Name The name of the destination table to which the file is imported.
    Data Location Specifies how data is arranged in the Excel file. Valid values:
    • 1st behavioral attributes: The first row of the table contains field names.
    • 1st behavioral data: The first row of the table contains data.
    Write mode Specifies how data is inserted. Valid values:
    • INSERT: The database checks the primary key when data is written. If a duplicate primary key value exists, an error message is returned.
    • INSERT_IGNORE: If the imported data contains data records that are the same as those in the destination table, the data records in the imported data are ignored.
    • REPLACE_INTO: If you insert a row that contains an existing primary key or unique index, the original row that contains the primary key or unique index is deleted and the new row is inserted.
    Note You can use the INSERT INTO, INSERT IGNORE, or REPLACE INTO statement to write data to databases on ApsaraDB RDS for MySQL, PolarDB for MySQL, PolarDB-X, AnalyticDB for MySQL, and ApsaraDB for OceanBase instances. You can use only the INSERT INTO statement to write data to other databases.
    Attachment Click Upload a file to upload the CSV file.
    Note
    • Supported file types include SQL, CSV, TXT, XLSX, and ZIP.
    • The uploaded file can be up to 5 GB in size.
    Other Options Specifies whether to skip errors. This parameter is optional.
    • By default, the check box is cleared. If an error occurs, DMS stops executing SQL statements and sends an error message.
    • If you select the check box, DMS skips errors and continues to execute SQL statements.
  6. Click Submit and wait until the precheck is complete. If the precheck fails, troubleshoot the issue and submit the ticket again.
  7. In the Approval step, click Submit for Approval. In the Prompt message, click OK.
  8. Optional:In the Execute step, click Execute Change.
  9. In the Task Settings dialog box, specify the time to run the task. You can set the Execution Strategy parameter to Running immediately. If you want to run the task later, set the Execution Strategy parameter to Schedule and specify a point in time for DMS to run the task. Then, click Confirm Execution.
    • Running immediately: By default, this option is selected. Click Confirm Execution to run the task immediately.
    • Schedule: If you select this option, you must specify the start time for the task. Click Confirm Execution to run the task at the specified point in time.
    Note
    • During the execution, DMS reads the SQL statements in streaming mode and executes the SQL statements in batches. Each batch of SQL statements is 1 MB in size.
    • In the Execute step, you can view the status, SQL check details, and scheduling logs of the task.
    • After the task is complete, you can open the SQL Console tab of the destination database and query the imported data.