How To Manage Excel for Arctos

Why Use Excel for Arctos?

Excel is a widely used tool for managing data in tables and many people managing data use it as a mechanism for getting data from one system to another. It is often the default tool for opening .csv files (which are the kind of files you get when you download data from Arcots). Excel is user-friendly (mostly) and ubiquitous, almost everyone has access to this program either on their personal computer or on a computer at work. Excel can work for you, but at times it seems like it might be at odds with you. This How To provides insight into ways you can make Excel work better when it comes to uploading data or using downloaded data.

Uploading Data

Dates

• Enter a date into Excel…

• Leave the field. Evil magic happens.

• Instead - Format Cells…

• with a custom date format

• Everyone is happy!

Saving to CSV

When saving from Excel, save as CSV UTF-8 (Comma delimited)(*csv) – the UTF-8 format is important!

Non-printing Characters (noprint errors)

If you get an error that includes “noprint” this indicates that there is a non-printable character in a field that is described in the rest of the “noprint” quote. These can include non-ASCII characters and trailing or double spaces. Here are some strategies for dealing with data in Excel so that you can clean up or avoid “noprint” errors.

• Use the TRIM function on messy data to remove many non-printable characters.
• Use the “strip junk” option in Arctos if it available.
• Try this.

Downloading Data

Non-ASCII Characters

Arctos supports Non-ASCII characters, which makes it nice for Española Animal Control but when you download data and open the file in Excel, you get

Española Animal Control

which isn’t exactly useful. Here are two solutions:

Import the Data to Excel

Don’t just open the CSV with Excel. Do this instead.

1. Open Excel then go to Data Menu
2. Under Get Data, select From Text/CSV.
3. Select the CSV file that you want to open.
4. Select File Origin = 65001: Unicode (UTF-8) from the drop-down list.
5. Select the delimiter, in this case it is comma.
6. Select Data Type Detection = Do not detect data types (This will keep your dates in whatever format they came out!)
7. Select Load to create the Excel data.

• adapted from Microsoft Community Forum Answer

Convert the .csv to ANSI

1. On a Windows computer, open the CSV file using Notepad (Tip: change from Text Documents (.txt) to All Files (.*) in the file type in order to find your .csv).
2. Click “File > Save As”.
3. In the dialog window that appears - select “ANSI” from the “Encoding” field and “All Files (.)” from the Save as type field. Then click “Save”. 4 . That’s all! Open this new CSV file using Excel - your non-ASCII characters should be displayed properly.

• adapted from YCBM Knowledgebase

Exporting from Excel

Same as import rules: if exporting as an CSV ensure that the format is 65001: Unicode (UTF-8). If you imported in that format, then you are set. If you are starting from XLSX or other format then be sure to select UTF-8 when you Save As ‘CSV’

Edit this Documentation

If you see something that needs to be edited in this document, you can create an issue using the link under the search widget at the top left side of this page, or you can edit directly here.