Page scope Follow the steps on this page for opening:
|
You can open .csv files with LibreOffice Calc, Microsoft Excel, Google Sheets, and other spreadsheet programs.
This page gives instructions for opening .csv files in LibreOffice Calc and Microsoft Excel so that the expected data format and encoding are retained. It also gives instructions for opening .csv files in Google Sheets and explains why this is not recommended. Finally, it gives some examples of why it is important to follow the instructions outlined here, or similar procedures in your spreadsheet application of choice.
If you do not follow these steps, you may run into things like:
You may not notice these changes, but if you save them and import them via the CSV Importer, you run the risk of adding incorrect data. We are providing this small sample CSV for you to download and use to experiment with your own CSV workflows: csv_unforced_quotes.csv. This file contains data formats that will clearly illustrate whether the process you use to open it changes the data:
|
Open the LibreOffice application
From the main screen, choose “Open File” or do CTRL-o
Navigate to the .csv file you wish to open
The text import dialog will open.
Ensure the settings are as shown below.
Click anywhere in the data preview at the bottom, do CTRL-a to select all columns, and set the Column Type to “Text”.
If LibreOffice opens your .csv with Writer (word processing) instead of Calc: On LibreOffice home screen, click Create > Calc Spreadsheet. Once in that spreadsheet, open the existing file as described above (File > Open or CTRL-o). |
The following instructions are for the Excel desktop application. The browser/Office 365 version of Excel does not currently support opening .csv files in a safe way. |
Create a new, blank Excel workbook
Select/click into A1 of Sheet1
Do File > Import (in the application menu bar, not the ribbon)
Make sure “CSV file” is selected and click “Import”
Navigate to your .csv file and click “Get Data”
In the first import screen, ensure “Delimited” is selected and File origin = “Unicode (UTF-8)”, then click “Next”
In the next import screen, deselect Tab and select Comma. Make sure the Text qualifier is a double quote. Then, click “Next”.
In the next import screen:
Click in the first column of “Preview of selected data”. It will turn black to show that column is selected.
Then, pull the little gray slider at the bottom of the “Preview of selected data” all the way to the right. (If there is no slider, all your columns fit in the preview display)
While holding down the Shift key, click in the right-most column. All columns should now be selected/black. If not, you may have clicked off the first column while moving the slider.
Once all columns are selected, select “Column data format” = Text
Click “Finish”
If you started off in cell A1, just click “OK” in the next box. Otherwise, make sure it looks like the image below and click OK.
To be extra safe
Why? There are some situations and combinations of settings/events that could cause the data you have imported to change in Excel. If you had made changes to the data in the Excel, they might be lost. If you remove the connection to the external data source, this absolutely cannot happen. |
Now you may save the Excel workbook as an .xlsx file. You can open this file normally in Excel.
In our testing, Google Sheets sometimes converted 20220831.010 to 20220831.01 even with the “Convert text to numbers, dates, and formulas” box unchecked. This depends on how the .csv was formatted/saved. Since .csv data exported from CollectionSpace does not force wrapping of all values in quotes (see below), we do not recommend using Google Sheets to prepare data for the CSV Importer unless you are certain your data will not be affected by this kind of issue. Details:
If your .csv was written like this, rbGoogle Sheets removes the trailing zero from the second exitNumber, even with the “Convert text to numbers, dates, and formulas” box unchecked. This is because it parses the value as a decimal number, not text. Some applications have the option (usually well-hidden) to wrap all values or all text values in quotes, resulting in:
Google Sheets retains your data integrity for the above if you uncheck the “Convert text to numbers, dates, and formulas” box. |
Instructions:
Create a new blank spreadsheet
From inside that spreadsheet, do File > Import
Navigate to/select the .csv you want to open. The details depend on whether this .csv is saved in your Google Drive, or whether you need to upload it from your computer/device.
In the Import file dialog, choose “Separator type” = Comma and uncheck “Convert text to numbers, dates, and formulas”
These examples all use as their source data the downloadable csv_unforced_quotes.csv. This file uses the common CSV dialect where only values themselves containing commas are wrapped in quotes. Here is the raw data in this file:
idNumber,note,date,otherId20220831.009,"Something, something",2022-08,978432678498720220831.010,Something something,9/1/2022,978432678498820220831.011,"CSV, not really a standard format",01-04,9784326784989
Trailing zero removed from second idNumber.
The date strings have been changed. Our otherId values have been switched to scientific notation.
idNumber has been interpreted as a decimal numeric value. If I increase the number of decimal points to three, it looks correct and the idNumber will be saved correctly. If I increase the number of decimal points to four:
And saving the file writes:
idNumber,note,date,otherId 20220831.0090,"Something, something",Aug-22,9.78433E+12 20220831.0100,Something something,9/1/2022,9.78433E+12 20220831.0110,"CSV, not really a standard format",04-Jan,9.78433E+12 |
You get the exact same result as opening normally.