Hi,
I have a plane txt file with two columns delimited with a simple tab. Once I open with Numbers it creates only one column and I do not manage to get two columns out of it.
I already tried to copy first in Pages and save as plane txt, I already tried to increase the space between the column.
Is everybody know if there is an official way to do it like in Excel?
I usually drop the file on the Numbers icon in the dock.
Can you email the file to me? My email is in the bio of my profile
A plain text file that uses a tab for the delimiter will open in Numbers as multiple columns. Are you right clicking on the file and opening with Numbers? Does it have a txt extension so Numbers knows it is a text file? Are you sure it is a tab, not a bunch of spaces? It does work as you expect it to so something else must be going on (or I have misunderstood what you are doing or want to do).
I looked at the file and it is a rich text file. It is NOT tab delimited. Like Badunit suggested the fields are separated by many spaces:
Here is how the file looks in Text Edit:
Here is how it looks in a hex editor (called 0xED):
Notice the highlighted green is the space between two fields. The text "20" is hexadecimal for a space character. A tab would be "09"
suggestion how to go make this work will require you to provide you work flow AND how often you do this. If it is very often that would beg for a solution that is convenient but may take some work up front. If this is a one time operation then there others.
If you double click the file it should open in Text edit.
hold the option key while the cursor is over the text window, the cursor should change to a cross hair. Now drag from the top left corner to the bottom right corner of the first column, as shown, copy. Now switch over to Numbers and paste into the first column:
Now select the second column using the same technique:
I looked at the file and it is a rich text file. It is NOT tab delimited. Like Badunit suggested the fields are separated by many spaces:
Here is how the file looks in Text Edit:
Here is how it looks in a hex editor (called 0xED):
Notice the highlighted green is the space between two fields. The text "20" is hexadecimal for a space character. A tab would be "09"
suggestion how to go make this work will require you to provide you work flow AND how often you do this. If it is very often that would beg for a solution that is convenient but may take some work up front. If this is a one time operation then there others.
If you double click the file it should open in Text edit.
hold the option key while the cursor is over the text window, the cursor should change to a cross hair. Now drag from the top left corner to the bottom right corner of the first column, as shown, copy. Now switch over to Numbers and paste into the first column:
Now select the second column using the same technique:
If you have to do this very often then I would say a better method is in order. For a better solution we would need to know the regularity of the column widths
Here is one way that allows you to copy the data out of the text file and paste it into the first column of a Numbers table, then the columnized data appears in later columns:
paste the data in column A
enter the values in row 1 columns B thru H as shown. Pasted here for convenience:
25 | 11 | 15 | 14 | 14 | 14 | 15 |
I1=LEFT(A1, B$1)
this is shorthand for select cell I1 (thet's the letter "I" 1 (the number one), then type, or copy and paste from here, the formula:
=LEFT(A1, B$1)
J1=MID($A1, 1+SUM($B$1:B$1), C$1)
select J1, copy
select J1 thru O1, paste
select I1 thru O1, copy select columns I thru O, paste
Paste the contents of the text file into column A
yes of course I'm doing with right click and open with numebers. 🙂 Yes it's for sure a plane text and I actually create the file so I can put a tab or a space or whatever I want and I am pretty sure I tried all. but I'll try with a single tab again just to be sure I didn't mix it up.
possible this file as a problem but due to the fact I find myself doing this import quite some times for work I wonder if there was a more practical way to do it because paste it in pages and exporting again and then opening with numbers it's pretty stupid and not practical at all.
sorry but in which file did you look exactly? that's of course not my file 🙂
I looked in the file you email me which I showed in my earlier post
Sorry? I did not send any email to you with any file!
What did you determine about your file? Tab delimited text files do import into columns and rows so something must be up.
I do not know what you mean exactly by "determine" my file, but I found out what was the problem. When I create the txt I had to put \t to get the right tab. Now it works.
Nevertheless I truly believe that an import like Excel in Numbers is needed!
Thank you guys for answering any how...
I meant "what did you 'find out' about your file". It appears you found out it was not actually tab delimited.
Excel does have more options for importing text-based files. Most of those options are unnecessary 99% of the time but they are there when you need them.
yes you are absolutely right that you do not need all the options of Excel of import most of the time but in same cases are needed...Nevertheless copying a file first in Pages and the export and then open it with numbers is a stupid way to solve the problem! Image you have a lot of files what do you do?
Is that what you ended up doing? Numbers will import a tab-delimited file as columns and rows without having to go through Pages. Is there a way to get whatever software is generating your incorrectly formatted text file to format it correctly from the start? Or, if you want, send me one of these files and I'll take a look at it and see what I would do instead.
No wait wait wait! For this specific file I did not do because I create so I just add a \t in my code and it worked.
But I had other files coming from a program so where I could not put a tab to delimit but there was only a space and I had to copy in Pages first and so on and so for...I could send you but does not make too much of a sense if a standard approach for import does not exist, next time with another file I will have another problem...
I see. You might consider using TextEdit instead of Pages to find/replace, such as to find all spaces and replace them with tabs. It is the same procedure but textedit saves natively as TXT instead of having to export. Or, if you do this on a regular basis, a script could be written to make it much simpler.
I cannot import txt in numbers with column tab
