Skip to main content

Imported Data - Excel, Comma, and Tab Delimited Files

  • Updated
    Tags:
  • Creator

In Harmoni, you can use data from various file types as the source for new projects, visualizations, and dashboards.

Learn how to best prepare your Excel, Comma, and Tab-delimited files (XLSX, CSV, or TXT) before importing them into Harmoni.

 

In this article  

Excel, Comma, and Tab-delimited Files

  1. General Rules
  2. Other Considerations

1. General Rules

  • Excel files must be in XLSX format. When you add an Excel source to a project in Harmoni only the first worksheet is imported.

  • Comma-delimited files must have a CSV extension.

  • Tab-delimited files must have a TXT extension.

  • The first row must contain headers with unique, non-blank descriptions.

  • Data types can be specified/overwritten in the import process. These are the defaults:

    • Heading labels that start with $ default to a measure.

    • Heading labels that start with $weight default to a weight.

    • Heading labels that start with & default to a verbatim.

    • Everything else defaults to a standard axis.

  • Fields should contain labels, not codes. If codes are used, they will need to be renamed to labels after importing into Harmoni.

  • Cells that contain text characters in a field flagged as a measure are not imported.

  • Fields cannot contain line feeds, carriage returns, non-printable characters, or | characters.  

To remove line feeds in Excel:

    • Press Ctrl+H to open the Find & Replace dialog box.
    • In the Find What field enter Ctrl+J. It will look empty, but you will see a tiny dot.
    • In the Replace With field, enter any value. Usually, it is a space to avoid 2 words joining accidentally. If all you need is to delete the line breaks, leave the "Replace With" field empty.
    • Press the Replace All button.

mceclip0.png

  • If you want to remove all non-printable characters from text, including carriage returns, you can use the clean function in excel.


=CLEAN(B2)

 

CLEAN.PNG

 

Notepad++ is a faster solution for source files with numerous columns with carriage returns. General steps for this:

  • Save your file as a .csv format
  • Open .csv file using Notepad++
  • Type CTRL+F to open the Find and Replace window
  • Find "\r" and replace it with a blank space. Use the following selections.

 

Find_Replace_Carriage_Returns.png

This will remove the carriage returns from your file and the data will look properly inline. You can load this .csv file to Harmoni. 

 

2. Other considerations

  • Fields containing commas or tabs in Comma and Tab Delimited files must be contained in double-quotes.
  • Fields contain labels, not codes. If codes are used, they will need to be renamed to labels after importing.
  • After the heading row, each row constitutes an unweighted count in Harmoni.
  • Blank rows are read as an unweighted count, so are included in the base.
  • Time/Date fields contain descriptions, not numbers.
  • Numeric values don’t contain text, e.g. $, commas, %.
  • If there are any foreign or special characters encoding your files into a UTF-8 format (UTF-8 to ensure anything you upload into Harmoni can be read and displayed properly. 

 

Where to from here?

 

Related articles