If you notice that your UPC and ISB numbers are being displayed with strange characters, Excel has likely transformed your files on you.

Our friends at Penguin Random House have written up a really nice tutorial on how to safely open a CSV data file in Excel.  These instructions can be followed for any data file from any publisher or distributor to ensure you don't corrupt the data.

How to safely open a PRH CSV Data File in Excel

If you are using Microsoft Excel to review and manipulate data from PRH’s CSV Data File, we highly recommend following these import steps in order to prevent loss of information or truncation/rounding issues.

For example: Microsoft Excel cannot handle numbers with more than 15 digits per cell. When UPCs are entered as a number in a cell, Excel will store the first 15 digits but then replace any remaining digits with zeroes. 

Other common issues you might run into if you haven’t imported the CSV safely into excel may include:
  • OrderRequirement looks like a timestamp 1:05:01
  • OrderRequirement looks like a decimal 0.111111111
  • OrderRequirementUPC looks like 7.59606E+16 or 75960609341000000
  • ISBN looks like 9.7813E+12 
  • Description or ContributorBio contain strange symbols like: LINK is a bestselling Japanese writer best known for the manga 
  • And more…
In order to import the PRH CSV Data file safely into Excel, follow these steps:
  1. Open the Excel spreadsheet where you want to import the CSV data. (This can be a brand new spreadsheet in Excel, or an existing XLSX spreadsheet.) Click the Data tab.
  2. In the “Get Data” section, select From Text/CSV.

3.Select the CSV file you are trying to import, and click Import.

4.You’ll see a screen that previews the CSV data. Make sure to select the following items from each dropdown menu:
a. File Origin: Unicode (UTF-8)
b. Delimiter: Comma
5. Then, select Transform Data.
6. The following columns will need to be transformed: MainIdentifier, UPC, ISBN, OrderRequirement, OrderRequirementUPC. 
a. Starting with MainIdentifier, select Text from the formatting dropdown menu. 
b. Then, select Replace Current. (You will see that all of your scientific notation UPCs now should have real UPC information on your preview.)
csv-

Pro Tip: If you’ve tried these steps and still see that your MainIdentifier column contains UPCs that look like scientific notation (7.59606E+16) or end in a string of zeroes (75960609341000000), you’ll need to close out the import query and try again.

7. Repeat steps 6a and 6b for the following columns: UPC, ISBN, OrderRequirement, OrderRequirementUPC.
8. Under the File menu, click Close and Load. 

You should now have your properly formatted Excel import of the CSV Data File!