Tags:
• Project Owner
• Creator

In this article  

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 by adding a flag or identifier. Use these identifiers to have Harmoni default to the required data type:
  • 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.

=CLEAN(B2)

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

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

No Variable/Column Name Duplicates in Excel:

Ensure there are no duplicate variables/column names in your Excel file. Harmoni does not allow you to define and select the Excel file if there are duplicates. Remove any duplicates if you see the following warning.

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?

• Learn more about data sources.
• Learn how to upload or connect to data sources.