Analysing data CSV files

Introduction

Comma Separated Values (CSV) files are used by Talis Aspire Reading Lists (TARL) and Talis Aspire Digitised Content (TADC) to present data information, such as exported All List Items report in TARL and Requests report in TADC, just examples of many available.

What is a CSV file?

A CSV file allows data to be saved in a table structured format. It takes the form of a text file containing information separated by commas, hence the name.

What is the difference with a CSV file to an Excel spreadsheet?

When you open a CSV file in Excel as a spreadsheet, you will notice that the numbers may not be appearing as you expect, for example, Local Control Numbers (LCN) can be affected in the following way when opening a CSV directly in Excel. Excel formats this in the following way 9.97E+12 if 16 digits or more.

How do I format my Excel document?

If it is just for one column:

1. Highlight the column you want to change the data in
2. Right-click to change the format
3. Select number option
4. Change to 0 decimal places

Alternatives to Excel

The easiest being to change to use different software, such as Google Sheets, Libre Office or Numbers if using a Mac.

Prefer Excel or required to enable sharing?

Then you can simply use the other software to format that column and export to Excel:

1. Upload the .csv into Google Drive
2. Open in Google sheets
3. Format the LCN Column to Plain text
4. Export as an Excel file

Not wanting to work outside of Excel?

Open a NEW blank worksheet 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, Next
• Select tab and comma as your delimiter, leave text qualifier as ", Next
• Work through each column changing Column Format to text where you have numbers eg. ISBN's, LCN's
• Finish
• Select the default where do you wish to put this.
• OK
• Your spreadsheet should now show all columns correctly and without missing numbers

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, take 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