• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Opening a text file in MS Excel - without data type conversion?

 
Ranch Hand
Posts: 401
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Sorry to interrupt with a technical question, but I don't know where else to ask this.

The situation is that my app creates a tab-delimited text file, which I want to 'open with' MS Excel. The problem is that one of the cells contains a long string of binary data (e.g., 01010101111111100000000010101010101000000000001111111110000011). I cannot find a way of making it display correctly though when I open the file in Excel.

When I open the file in Excel, it either shows as...
1.1E+198
...or as...
1010101111111100000000010101010101000000000001111111110000011 (with the leading zeroes missing - which is no good)

If my app writes the data into the file with an apostrophe in front (e.g., '01010101111111100000000010101010101000000000001111111110000011) then the apostrophe also gets displays in Excel.

How can I make it so that just the 01010101111111100000000010101010101000000000001111111110000011 shows when I open the file in Excel (preferably without having to modify loads of cell properties)?!

Thanks in advance,
James
 
Marshal
Posts: 28193
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
When you import the text file, you have the option of going through a wizard where you specify the format for each column. I don't know whether "binary" is one of those options (I rather doubt it but I haven't looked) but at any rate if you don't go through the wizard then you're stuck with whatever Excel chooses to do. As far as I know, anyway.

I'll move this to the General Computing forum.
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I had encountered similar problem in the past - I've put an apostrophe in the text file, but Excel kept displaying it when I opened that file. I discovered after much haggling that when I use the Replace form to replace an apostrophe with an apostrophe, it stops being shown in the cells. Unbelievable, but true.

A macro to do this can be probably easily recorded. I'm not sure whether this operation marks the file as modified. If it does and you don't want it, you could handle it in the macro.
 
James Hodgkiss
Ranch Hand
Posts: 401
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Cheers for the replies, but I've found the solution now... I decided to open my file in MS Works Spreadsheet and I noticed that actually uses the " character (double apostrophe, rather than single apostrophe) at the start of text cells, so I've put that into my app and it's now sorted!!
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic