Data Warehouse - CSV Export and Import

Version 1.0

Version Create Date Author Notes
1.0 2021-11-19 John Puttman Initial Creation

Overview:

This document covers the bulk maintenance of AgronDW tables through the use of CSV Export and Import. This functionality is handled through flows that are created in Power Automate Desktop. This is installed on the HQ-POWERAUTO Virtual machine.

Security Notes:

  • Users must be added to the remote desktop users of HQ-POWERAUTO
  • User’s domain accounts need to be added to the CustomerDataMgmtRole within the AgronDW database.
  • Users must be licensed for Power Automate Per User License

Accessing the Power Automate Desktop Interface:

  • Remote into HQ-POWERAUTO using your Agron\<username> domain account
  • Launch the Power Automate Desktop Application
  • Select the Environment you wish to work with (Production or Agron.com (default) which is development)
    • To select an environment click the “Environment” label in the top bar of the application
    • Click the environment desired

Exporting CSV Files:

  • Select the CSVExport Flow and click Run

  • Enter the table name that you wish to export and click OK

  • This will create a .csv file with the table name followed by a date and time suffix. The file will be saved at C:\AgronDW_<Environment>\Exports

Importing Files:

When importing a data table through this process all data in the existing table will be deleted. Prior to the delete, a copy of the data will be saved under C:\AgronDW_<Environment>\Backup\. The imported CSV file is required to be named the TableName without any date suffix. (e.g. AmazonBrandName.csv). This file is required to have all the working columns with the exact column names defined in the table. Non-working columns are the Sys time columns as well as they create and update user and date-time columns.

  • Start with an exported file from the table that you wish to bulk edit
  • Edit the file data in the file as needed
  • For any added rows, ensure to add an incremental and unique value to the primary key row
  • Save the file to the C:\AgronDW_<Environment>\Imports folder

  • Run the Customer Data CSV Import Flow

LINK to a How-To video