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

Procedure

  1. Save the Excel file as a CSV file, and specify column names that meet the database requirements.
    Note If your data does not contain a column to store unique IDs, we recommend that you add a primary key column to facilitate storage and queries.
    Excel data
  2. Use DMS to log on to an ApsaraDB for RDS instance.
  3. Select a target database and create a table in the database.
    Note The tables in the database and in the Excel file must be in the same schema.

    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
    Create a table
  4. In the top navigation bar, choose Data Operation > Import.
  5. Create a task to import data from Excel to RDS.
    1. In the upper-left corner of the page, click New Task.
    2. Configure the following parameters.
      Create a task
      Parameter Description
      File Type The type of the file to be imported. Select CSV.
      File Encoding The character set used by the file to be imported. Select Automatically identify the character set.
      Database The name of the database to which the file is imported.
      Table The name of the table to which the file is imported.
      Option The import method.
      • Ignore the error: By default, if a SQL statement fails to be executed, DMS terminates the import. If you select this option, DMS will skip failed SQL statements and continue to execute the following statements. This option can lead to risks to your data.
      • Truncate the table: Truncate the table before data is to be imported into the table. You cannot roll back this operation.
      Data Location Specifies how data is arranged in the Excel file.
      • First row is property.: The first row stores the table column names and data starts from the second row.
      • First row is data.: Data is stored starting from the first row.
      Written Method Specifies how data is inserted.
      • insert into: The common data insert method. If you insert a row that contains an existing primary key, an error is returned.
      • insert ignore into: If a primary key conflict occurs when you insert data, the error is displayed as an alert.
      • 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.
      Attachment The Excel file to be imported. The maximum file upload size is 100 MB in DMS Free Edition and 1,000 MB in DMS Premium Edition.
      Description Enter a task description to briefly specify the imported content and reason for later review.
    3. Click Start.
      Note If the import is terminated because an error occurs, you can fix the error based on the error message and retry the import.
  6. Query the number of data records to check whether all data is imported.