importing numbers as text

[barnabel]

I am trying to write a file from another system that will be opened with Excel

If the fields have quotes around them and are TAB separated then it is
pretty easy to get Excel to read it. Some of the fields have values that
look like numbers but really should be treated as text (They are Hex with
leading zeros)

I know that you can use the wizard when you open the file and select the
column and say it is type text. But the people opening the files will fail
to follow dirrections 9 of 10 times if they bother to read them at all.

I thought putting a leading single quote in the field whould make it open as
text but it displays the quote.

Is there anything that can be put in the file to maintain the format while
not displaying in Excel?

TIA
Peter Richardson

 

[Dave O]

Would a single blank space do it?

 

[barnabel]

I tried both leading and trailing space inside double quotes. I tried single
quote with no external double quotes.

The only way that I've been able to get it to do what I want is to open with
the text wizard and tell it to format that column as text.

 

[Earl Kiosterud]

Peter,

Some things to try. If you'll be wanting to bring the same, or similarly laid out, text
files into an Excel sheet repeatedly, then setting up an Import Query will be good. Data -
Import external data, Import data. YOu'll be stepped through the same text import wizard,
where you can specify text. Now to read in the text file again, just do a Refresh. It
reads it in again, remembering all the setup you did. You can change the file name first if
you wish.

Another possibility is to record a macro of the File - Open you're doing, which will record
all the Text Import Wizard specs you did. Give the users a button that runs the macro.
Bob's your uncle.
--
Earl Kiosterud
www.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...

 

[barnabel]

I thought about an import macro or open macro the problem is that macros
cannot be stored in text files and the system that is producing this can only
put out csv or tab delimited text. This would mean that the person who will
be opening it will have to have something in their Excel personal.xls which
is inconvienient.

The person creating the text file will be producing it on a Unix machine.
No MS office installed and then sending it to somebody else who will load it
into Excel. It is not importing the data into an existing workbook.

I recorded and looked at the command to open the text file and dreaded
trying to explain it to the 2 people that will be using this.

 

[Earl Kiosterud]

Peter,

The person importing the text file need only have Excel, and an Excel workbook containing a
sheet set up with the import. No macro required. This will bring the data into the same
sheet each time the refresh is run (you can change the text file name first, if necessary).
Then you could do whatever processing/formatting necessary, and save the workbook as a
workbook or as a text file or whatever.

With the import, the text file is imported into the sheet set up for the import each time
the refresh is performed.

With the File - Open, it's read into a new workbook. The purpose of the macro in that case,
if you want to use one, is to have it remember all the stuff specified when you went
through the Text Import Wizard. The macro would live in a separate workbook, which wouldn't
have to be personal.xls.
--
Earl Kiosterud
www.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...
-----------------------------------------------------------------------