CSV files are intended to exchange data between different platforms. All data is included herein as text. Unfortunately, if you open them directly in Excel by double clicking on them, CSV files will not be imported properly. This causes some data to become corrupt and the entire file to become unusable. Therefore, never save a CSV file if you accidentally opened it in Excel.
We have a work-around for this to be able to edit these files in Excel. When we generate exports of data, we enclose each value with a setof 3 double quotes, (for example “””2020.12345″””). When such an export file is opened directly in Excel, this forces all values to still be displayed as text.
* Tip: After each step, save the csv file, make a copy of it and do the next step in that copy!
Step 1 – Secure special characters
Before you open the CSV file in Excel, you must first ensure that special characters (such as currency symbols or diacritics such as: æ, å, ç) are imported correctly.
1. Open the file in Notepad
2. Click File
3. Select Save as …
4. Click the drop down box Encryption
a. Windows 10 users: select UTF8 with BOM
b. Users of Windows 7 or older Windows versions: select UTF-8
5. Click the drop down box Save as: and select All files (*. *)
6. Click the button Save
Now you can open the file in Excel. Now all values are enclosed in one pair of double quotes, for example “12-10-2020”.
Step 2 – Avoid unwanted – because incorrect – ‘corrections’ by Excel
Excel ‘corrects’ all values that resemble a date or a number. For example, if you use registration numbers consisting of a year and a serial number with a period in between: 2015.12345, Excel will immediately change this to 201.512.345, if you remove the quotations. As a result, the relevant animal can no longer be found in the database. We must therefore first precede all values with 1 single quote ‘ in each column in the file, where these kinds of unwanted corrections may occur. This is because this is used in Excel to indicate that the contents of that cell should be interpreted as text. If this is then exported from Excel to a new CSV file, those few quotes will not be included. This mainly concerns all columns with data that must remain unchanged.
As an example we take the first column from an export of Animals (RegistrationNumber):
1. Right-click the header of column A and select Insert
2. Type the formula = “‘”& B2 (a single quote between two double quotes) in cell A2
3. Press Ctrl-C
4. Click in cell B2
5. Press Ctrl-arrow down, Arrow left, Shift-Ctrl-arrow up
6. Press Ctrl-V
7. Select cell A2
8. Press Shift-Ctrl-arrow down
9. Press Ctlr-C
10. Right click on B2 and Paste as: Values
You can repeat steps 2 to 10 for all columns that have to be kept as text by repeating steps 2 to 10, after adjusting the column letter in the formula in cel A2 . Do not forget to always paste the values of column A into the correct column in step 10.!
This concerns all columns in which, in addition to text, there may also be values that look like an amount of money with a currency symbol or as numeric values, but which must absolutely not be changed.
11. Delete column A
Only if you are sure that there is only text in a column, or that it is intended everywhere as a date, a numeric value or a monetary amount, you can proceed as follows:
12. Select the columns to which this applies by selecting them one by one with the Ctrl key pressed down by clicking on the column letter
13. Click Find and Select and select Replace
14. Fill in for Find what: “(a double quote) and leave Replace with blank (watch out for left behind spaces!)
15. Click Replace All
The standard date format in Excel (d-m-yyyy) differs from the ZooEasy standard. We need to correct this:
16. Select the date column(s)
17. Right-click in a selected column
18. Select Format cells…
19. Select Custom
20. Underneath Type, enter: dd-mm-yyyy
21. Click the button OK
Numbers with digit grouping symbols can be displayed in American format: € 16,000.00 this must also be corrected where it can occur (for example in the Cash Book: Income and Expence):
22. Select the relevant column(s)
23. Click Find and Select and select Replace
21. Fill in – if available – at Find what: the currency symbol and the space behind it and at Replace with: empty (make sure that no space is left here!)
22. Click the button Replace All
23. Fill in next to Find what: , (a comma) and leave Replace with: empty
24. Click the Replace All button
25. Fill in next to Find what: . (a period) and let Replace with: , (a comma)
26. Click the button Replace All
This action replaces the – American – decimal separator symbol with a comma and Excel – unsolicited – puts the European thousands separator in its place, including the currency symbol.
The file can now be edited and saved in .xlsx format.