Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Suppose I have a following string in a .csv file which I am trying to open in Excel 2010:

1073741824;1073741824;1073751824;1073741832;1152921513196781569

However, when I actually open it, the value 1152921513196781569 changes to 1,15292E+18, and even if I change the cell content type to numeric with zero fractional precision, it yields only 1152921513196780000.

Is there any way to overcome this issue?

share|improve this question

migrated from stackoverflow.com May 22 '12 at 13:00

This question came from our site for professional and enthusiast programmers.

add comment

3 Answers

up vote 2 down vote accepted

One possibility is to first format the sheet as text and then paste from a text editor. Here are the steps:

  • In a blank sheet select all cells and set the number format to Text
  • Enter something in A1 and then choose Data|Text to Columns|Delimited|Semicolon
  • Open the csv file in notepad select all with Ctrl+A and copy and paste to the sheet
share|improve this answer
    
Be aware that Excel can only handle numbers with 15 significant digits, so you are forced to handle some of these numbers as text. –  RonnieDickson May 8 '12 at 4:56
add comment

Open a new workbook. Use the Data->Get External Data->From Text tool to import your CSV. In the wizard, specify the formatting for that field as Text.

share|improve this answer
add comment

Both the given answers are correct. I offer a third alternative, along with some explanation:

When Excel opens a CSV file, it skips the "text import wizard", performing the data identification for you. It will try to be helpful, and detect numbers, dates, currencies... This causes your 1152921513196781569 to be shown (and saved!) as 1,15292E+18

Apart from copy-pasting the data (which is fast, but annoying when the data size is huge) or using the data-get external data, here is an alternative:

Rename the file to a .txt extension.

Then open the .txt file with Excel. It will show the data import wizard, and you can select all fields and mark them all as text. Now when you save as CSV, the numbers won't get transformed.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.