Introduction
Comma Separated Values (CSV) files are used by Talis Aspire Reading Lists (TARL) and Talis Aspire Digitised Content (TADC) when exporting the All List Items report in TARL or the Requests report in TADC.
What is a CSV file?
A CSV file allows data to be saved in a structured format. It takes the form of a text file containing information separated by commas, hence the name.
What is the difference between a CSV file and an Excel spreadsheet?
When you open a CSV file in Excel as a spreadsheet, you may notice that some data may not be appearing as you expect, for example, ISBNs or Local Control Numbers (LCN) can be displayed in the following way 9.97E+12 if 16 digits or more, or it might be given a decimal point.
How to make sure columns are formatted correctly?
If it is just for one column:
- Highlight the column you want to change the data in
- Right-click to change the format
- For example: if you want to remove decimal places:
- Select number option
- Change to 0 decimal places
If you need to use Excel?
To stop Excel guessing the format for different columns, you can convert them all to ext as the file is imported into a blank workbook.
To do this, open a NEW blank workbook and use the import data options to import from the CSV file and set all columns to be treated as text.
- Open new Excel spreadsheet
- Choose the import option in the File menu
- Select CSV file
- Select file to use
- Select delimited, Start import at row 1 (if you want to skip heading you might need to choose a different row), click Next
- Select tab and comma as your delimiter, leave text qualifier as ", click Next
- Work through each column changing Column Format to text where you have numbers eg. ISBN's, LCN's
- click Finish
- Select the default where do you wish to put this option.
- OK
- Your spreadsheet should now show all columns correctly and without missing numbers
When you export the data, be sure that it is exported as a CSV file.
10 digit ISBNs
Some users have highlighted that when exporting data from the All List Items report, Excel will automatically remove the '0' from the beginning of 10 digit ISBNs. To restore these, if you don't want to import the data as above, you could try the following steps:
- Highlight the column containing the 10 digit ISBNs
- From the 'Home' tab, click on 'Format cells'
- Choose 'Custom', then under 'Type:' click on '0'
- In the text field, enter 10 zeroes: 0000000000 and click on 'OK
- Your ISBNs beginning with '0' will now be restored, and those beginning with another number are unaffected
Alternatives to Excel
Other tools are available, such as Google Sheets, Libre Office or Numbers if using a Mac. These tools all work slightly differently, but you might find that they don't upset ISBNs or LCNs so much!
Prefer Excel or required to allow sharing of the file?
Then you can simply use the other software to format that column and export to Excel:
- Upload the .csv into Google Drive
- Open in Google sheets
- Format the LCN Column to Plain text
- Export as an Excel file
Checking a CSV file is correctly formatted
You may need specific column counts, or to have some empty columns at the end of a row in a CSV file that you are building. You can use Notepad++ as a tool to view the 'raw' text in the CSV file and ensure that the commas being used as column delimiters are in the correct places.
You might also like to try Visual Studio Code with some of the CSV syntax and CSV editing extensions loaded. This can help you work with the data and validate that the file looks correct.