Import/ Export
      Back to home
      1. Help Center
      2. Data Management
      3. Import/ Export

      Importing data from CSV and TXT files

      Created by: Gunnar Steinn Magnússon

      In this article, you will read about importing data from CSV files (comma-separated values) and TXT files with fixded column width into exMonDM. In this example, we have a CSV file with three columns, a header row and two data rows.

      Contents:

      Resource: Download Customers.csv

      Creating a table in exMonDM

      1. Open exMonDM and click Create a table
      2. Select Empty table

      3. Name the table Customers
      4. Click on Table Designer
      5. Click Add and name the first column ID

      6. Repeat step 5 for the columns Name and Contact
      7. Click the Home tab and add in the following dummy information
        ID Name Contact
        A Andy andy@duck.com
        B Buzz buzz@duck.com
        C Jesse jesse@duck.com

         

       

       

       

       

      Importing a CSV file

      1. In exMon DM, click Import Data.

      2. A dialog opens to select the data source.
      3. In Data source select Flat file and then Browse to find your CSV file.

      4. A dialog opens to select the data source.

      5. In Data source select Flat file

      6. Then Browse to find your CSV file.

      7. You can choose or override the Delimiter and if your data uses the first row as column names.

      8. When changing the selection, the preview below displays how exMon will interpret the data.

      9. When you are ready, click Next.

      This step allows you to map columns from the CSV file to the table in exMon DM.

      Below you can see a preview of the the in exMon DM and how the import will map the data.


      For each destination column (column in the exMon table), select a column from the CSV file. If the column names in the CSV and the exMon table are the same, then the mapping will be automatically filled out.

      Fixed width files

      If your file is not separated by a delimiter, but has fixed width columns you can configure the import by:

      1. Selecting Fixed width in the Delimiter dropdown.

      2. Inserting the correct size of all the columns, separated by a comma, in the text editor.

      What is the Source Column?

      For each destination column you have three options:

      • Select a column from the CSV file.
      • Fixed: A predefined value for all rows. When selected you will enter the value in Custom Value.
      • [None]: The column will be left empty.

       

      What is an Action?

      After mapping the columns you will need to select an Action for each column. The import can either add rows from the CSV or find existing rows and update attributes when found. For example, if you are importing a customer address list you might want the import just to find the customers in exMon DM and update the addresses.

      The Action determines the behavior for the import.

      • Unique Key: This means that this column is what makes the row unique. For example, the customer id or product id. You can select multiple columns as the Unique Key.
      • Update: The data in the exMon table will be updated with data from the CSV.
      • Ignore: The column is ignored during the import. It's the same as selecting Source Column as None.

       

      Add rows not found in existing data

      When using Unique Key the import will try to find matching rows in exMon when importing. This option is to change behaviour when the import does not find a row from the CSV. When unchecked, the import will not add new rows, only update existing rows.



       

      Custom Numeric and Date formatting

      When importing CSV with numbers and dates, exMon assumes that the number and date format is the same as regional settings in Windows. When working with CSV from external sources this is often not the case.

      You can override both numeric and date formats using this dialog.



       

      When you have chosen all the mapping options, click Preview to preview the import.

      The preview window will show the results of the import. At the top, there is a summary of the import and below each row of the import is displayed.

      If there were any errors during the import they will be displayed in this dialog.

      When ready, you can click Save and Close to apply the import.

      Note that you have not made any changes to the exMon table until you click Save. When Save and Close has been clicked exMon will suggest the correct size for string columns depending on the longest string in each column which you can change in the 'Format & Size' tab in the Table Designer  if you prefer it differently.





      Gunnar is the author of this solution article.