Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
importing numbers as text
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
importing numbers as text
Would a single blank space do it?
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
importing numbers as text
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. "Dave O" wrote: Would a single blank space do it? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
importing numbers as text
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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
importing numbers as text
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" wrote: 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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
importing numbers as text
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... ----------------------------------------------------------------------- "barnabel" wrote in message ... 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" wrote: 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hex numbers displayed in scientific notation when importing text f | Excel Discussion (Misc queries) | |||
Importing long numbers from CSV file | Excel Discussion (Misc queries) | |||
Importing CSV file (saved as Text) into XL as Text -- over 60 colu | Excel Discussion (Misc queries) | |||
Retain Numbers as Text Format When Importing. | Excel Discussion (Misc queries) | |||
Importing text files to Excel with big numbers | Excel Worksheet Functions |