| Info |
|---|
Page scope Follow the steps on this page for opening:
|
...
| Note |
|---|
If you do not follow these steps, you will 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:
|
Instructions for recommended applications
LibreOffice Calc (recommended, available here)
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”.
| Panel | ||||||||
|---|---|---|---|---|---|---|---|---|
| ||||||||
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). |
Microsoft Excel
| Note |
|---|
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.
...
Now you may save the Excel workbook as an .xlsx file. You can open this file normally in Excel.
Instructions and warning for opening .csv files with Google Sheets
| Note |
|---|
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. |
...
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”
Cautionary examples
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
LibreOffice Calc
Opened without setting column type to Text:
...
Trailing zero removed from second idNumber.
Microsoft Excel
Opened normally (File > Open or CTRL-o)
...
The date strings have been changed. Our otherId values have been switched to scientific notation.
...
| Code Block | ||
|---|---|---|
| ||
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 |
Opened with File > Import, but not setting all column data formats to Text
You get the exact same result as opening normally.