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.

I have some data in a database that uses ids that have the form of 16 digit numbers. In some situations i need to export the data in such a way that it can be manipulated in excel.

So i export the data into a file and import it into excel.

I've tried several file formats and I'm stuck.

The problem I'm facing is that when reading a file into excel that has a cell that looks like a number then excel treats it as a number.

The catch is that (as far as i can tell) all numerical values in excel are double precision floating point which have a precision of less than 16 digits. So my ids are changed: very often the last digit its changed to a 0.

So far I've only been able to convince excel to keep the Id unchanged by breaking it myself: by adding a letter or symbol to the Id. This however means that in order to use the value again it must be "unbroken".

Is there a way to create a file where i can specify that excel must treat the value as a text without changing the value? Or its there a way to let excel treat the value as a long (64bit integer)?

share|improve this question
    
What format are you using? CSV? –  Hand-E-Food Oct 3 '12 at 23:35

4 Answers 4

up vote 2 down vote accepted

You can put the number in quotes and add an equals sign before it, like so: ="001145" (this is in the CSV, of course)

share|improve this answer
    
As a test I created a file with this line: ="12345678901234567890","12345678901234567890",12345678901234567890 and your solution works great in this test. –  Niels Basjes Oct 5 '12 at 19:34

The standard way to force something that looks like a number to be treated as text is to put a single quote, a.k.a. apostrophe ('), in front of it.

share|improve this answer
    
This certainly works when typing in Excel. However, I just tried this out in a CSV. The apostrophe is copied into the cell as well. –  Hand-E-Food Oct 3 '12 at 23:36
    
@Hand-E-Food: This is exactly one of the situations I ran into. –  Niels Basjes Oct 4 '12 at 14:35

You can force Excel to import this as text from a .csv. Once you've created your .csv file, then

  1. Select Data > From Text
  2. In the Text Import Wizard Step 1, choose Delimited
  3. In the Text Import Wizard Step 2, choose Comma
  4. In the Text Import Wizard Step 3, select your long ID column and choose Text data format.
  5. Then finish.

Excel will treat your long ID column as text from the beginning, rather than trying to convert it to a number then back to text with the resulting loss of data.

Here's an example of the same data string being imported and formatted three different ways from .csv:

long_id

Note, if you try and open the .csv directly (which Excel will gladly do for you), you'll already be past the point of being able to assign a data type value to your columns-Excel will have done it (erroneously) for you.

share|improve this answer
    
So that's where they've moved it to in Excel 2010! –  Hand-E-Food Oct 4 '12 at 23:46
    
Thanks. I forgot to mention I want a solution that is easy to use for business people. So manually doing the right settings each time they want to look at an export is considered too much effort by them. –  Niels Basjes Oct 5 '12 at 19:30

In addition to what I've classified as the credited answer I've also found that in LibreOffice I can export a sheet in what they call "Excel 2003 xml". This format is an XML that looks pretty easy to generate myself and has this kind of definitions for a cell in a sheet:

<Cell>
  <Data ss:Type="String">12345678901234567890</Data>
</Cell>
<Cell>
  <Data ss:Type="Number">1.23456789012346E+019</Data>
</Cell>

When I simply give such an xml the xlsx extension my LibreOffice opens it correctly without any questions.

share|improve this answer

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.