GeeCON Prague 2014*
The moose likes General Computing and the fly likes Opening a text file in MS Excel - without data type conversion? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Engineering » General Computing
Bookmark "Opening a text file in MS Excel - without data type conversion?" Watch "Opening a text file in MS Excel - without data type conversion?" New topic
Author

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

James Hodgkiss
Ranch Hand

Joined: Jan 22, 2004
Posts: 401
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
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18570
    
    8

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.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

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

Joined: Jan 22, 2004
Posts: 401
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!!
 
wood burning stoves
 
subject: Opening a text file in MS Excel - without data type conversion?