Exporting to CSV - Numbers not formatted correctly in Excel

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.

This is due to the documented issue detailed here:

Documented Issue

There are a few solutions to this issue:

  1. The easiest being to change to use different software, such as Google sheets, Libre Office or Numbers if using a Mac.
  2. 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
  3. 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