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 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?

There are a few solutions to this issue: 

If it is just for one column: 

  1. Highlight the column you want to change the data in
  2. Right click to change 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 

    • Upload the .csv into Google Drive
    • Open in Google sheets
    • Format the LCN Column to Plain text
    • 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 deliminators, 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

 

 

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk