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
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.
- If you want to remove all non-printable characters from text, including carriage returns, you can use the clean function in excel.
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.
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?