Handling tab delimited files.

Remove the foolish .xls extension name and replace it by .txt and double-click the file's icon.

Yvan KOENIG (VALLAURIS, France) vendredi 21 janvier 2011 16:27:28

The result you got indicates that you probably didn't follow Yvan's instructions. If you change the extension on the text file and Right-Click the filename, Open With, Numbers, you will succeed.

It sounds as though you Copied the TSV data and Pasted it into a Cell in Numbers.

You can Paste into Numbers, but be sure that you don't have a flashing Text Insertion Cursor in the cell you are pasting to. You just want to select the cell, not open it for editing, then Paste.

Jerry

I use TSV files with iWork since the delivery of Numbers '08.
So, I know their behavior.

But of course, if you didn't read carefully the described scheme, it's not surprising that you got an odd result.

Of course, as you wrote that your document is a TSV I responded accordingly.

If you typed wrongly and you where asking about a csv file the answer would be different.

For csv files, the first thing to do is to replace the foolish .xls extension by the .csv one.

This done, we are supposed to be able to open it with a double-click but, as from my point of view CSV is the worst format ever invented, we are just supposed to be.

If we are running a system whose decimal separator is the period, the csv file must really use comma as values delimiter
but
if we are running a system whose decimal separator is the comma (they are numerous), the csv file must use the semi-colon as values delimiter.

If the described conditions aren't matched we will get every values of a row in a single cell.

I spent time to describe the behavior of CSV files because I guess that it's what or file is really.

Even if I copy the contents of a true TSV file then paste in a Numbers table, I get a correct result (except if I double click in a cell which will put the entire set of datas in a single cell).

Yvan KOENIG (VALLAURIS, France) vendredi 21 janvier 2011 19:07:36

jaendre wrote:
I read and understand English very well. I have followed your instructions and still do not get the results I want. Again, this is a tab delimited file that I am trying to import. I changed extension per your first response to .txt. double clicking only brings up text editor with more characters than I want to see. Double clicking the file icon and opening with Numbers09, gives me all the info entire file in one cell with all the delimiting symbols. Changing file extension to .csv produces same results. Dragging and dropping the file on a blank Numbers page and opening an existing file through Numbers09 gives identical results.

May you send the offending file to my mailbox ?
Click my blue name to get my address.
With the file in front of me, I will easily identify the wrongdoer.

Your explanation of CSV delimiting was very enlightening, but I need some direction on how to tell Numbers what the delimiting character should be.

You can't change the delimiter used by Numbers.

if the file is a .txt one, the delimiter must be TAB
if the file is a .csv one and *_your system_* use the period as decimal delimiter, values delimiter must be comma
if the file is a .csv one and *_your system_* use the comma as decimal delimiter, values delimiter must be semi-colon.

Yvan KOENIG (VALLAURIS, France) lundi 24 janvier 2011 18:35:47

jaendre wrote:
I changed extension per your first response to .txt. double clicking only brings up text editor with more characters than I want to see.

"more characters than I want to see" would indicate this is not a text file. Both TSV and CSV are plain vanilla ASCII text formats, and should contain no characters other than the data, the field separator character (generally a comma or semi colon) and the record separator character (return for both types).

Double clicking the file icon and opening with Numbers09, gives me all the info entire file in one cell with all the delimiting symbols.

What characters are "all the delimiting symbols" that you can see in the cell?

Regards,
Barry

jaendre wrote:
I read and understand English very well. I have followed your instructions and still do not get the results I want. Again, this is a tab delimited file that I am trying to import. I changed extension per your first response to .txt. double clicking only brings up text editor with more characters than I want to see.

You wrote :

How do I import an Exel tab delimited(.xls) file into Numbers09?

This is why I urged you to replace the .xls extension which is not the correct one for a Tab Separated Values document.

I described also the behavior of CSV files because some users don't make the difference betwee TSV and CSV.

The described behavior was not matching Double clicking the file icon and opening with Numbers09, gives me all the info entire file in one cell with all the delimiting symbols. Changing file extension to .csv produces same results. Dragging and dropping the file on a blank Numbers page and opening an existing file through Numbers09 gives identical results.
Your explanation of CSV delimiting was very enlightening, but I need some direction on how to tell Numbers what the delimiting character should be.

If you document isn't a tsv or a csv one you are the only one able to know what it is.

I repeat : send the document to my mailbox.
At this time everybody is wasting time more or less precious.

"Everybody" means :
you, the Original Poster
Barry which must work to earn its life so I guess that time available to help is limited
me because I'm old and in such a case, every minute is a precious one.

Yvan KOENIG (VALLAURIS, France) mardi 25 janvier 2011 21:51:31

I have the same problem - did anybody find a solution to this please?

ich009 wrote:
I have the same problem - did anybody find a solution to this please?

Have you tried the steps in Yvan's first post?
Have you tried the modification to that procedure (right click instead of double click) described in Jerry's first post?
Have you tried opening the file with a text editor, copying the contents, then pasting into the Numbers table, attending to Jerry's caution in the same post?

What were the results in each case?

Regards,
Barry

Send a sample fiole to my mailbox.
This way it will be easy to give directly the correct answer to a question which, at this time, is vague.

To be short it is :

"I have a problem, help me to solve it"

Click my blue name to get my address.

I was able to solve the problems linked to every TSV or CSV files which I received.

Yvan KOENIG (VALLAURIS, France) mardi 22 mars 2011 08:52:25

While having no experience with Numbers myself, Excel does offer a CSV export function in at least Excel version 2004 (yes, I haven't upgraded, I moved to Neo Office). Whether or not it applies to you, simply changing the extension will not automatically make it a plain text file. You need to Save As from Excel, and pick the CSV from the menu for the Format, below the file name. That will automatically change the delimiter used and make it into a text file as originally described. Reading the responses here, I can see how one might assume one just renames the file. If it has a .xls extension originally, it will still maintain its status an Excel file, and not a text file until you change it by a Save As command. At that time, it is highly recommended you keep the file extension that Excel gives you in the conversion process. If you don't, problems like occurred here will persist.

Edit:
So if this question were to arise again, I would ask, do you know for certain how it became a tab delimited file? Was it imported as tab delimited into Excel, or was it exported to a comma delimited or tab delimited from Excel as a text file? Cause without knowing the answers to these questions, we might make the wrong assumptions.

Message was edited by: a brody

I believe this is the root of the problem. Excel does not save tab delimited files with an xls extension. That extension is reserved for true Excel files.

Hello A. Brody and Badunit

I receive regularly documents named wxyz.xls which are in fact Tab Separated or Comma Separated Values ones.
The late one, named MBEW_W.XLS (I'm not shouting, the name was entirely in uppercase) was using a structure which I never saw before, it was a Tab Separated Values one encoded as Utf16.

There is no way to know for sure the way this late file was built except perhaps the first bytes which aren't available in text files.



FFFE is the BOM signing an Utf 16 file. Maybe the 1s, the 2 and the 3 which you may see are an application's signature but I can't say that for sure.

When I transcoded the file in Utf 8 encoding, Numbers was able to import it flawlessly.
As I wrote in the thread open by the file's owner, Pages is able to read Utf 16 documents but Numbers isn't.
Just an other example of inconsistencies in the iWork team.

For the files about which we are asked here, the only way to really help requires their availability.
If their owners refuse to send them, they will remain with unusable files.
It seems that some users are a bit paranoiac. When I receive a file to help someone, the game is to revive the file in the shortest time. I don't spend time to really read the contents. I just do my best to build a file which open flawlessly in Numbers.
I'm not working for the CIA or for the Tax Office.

Yvan KOENIG (VALLAURIS, France) dimanche 27 mars 2011 21:42:51

Maybe so Yvan, but doesn't it seem the least bit possible that someone who says they have a tab delimited xls file really means to say they have imported tab delimited into Excel at some point in the past? And if that's the case, I think that opening the file in Excel and determining its format there would be the first course of action with any XLS file. Then determining if it can be exported as tab delimited to a .txt or .tab file would allow them to make sure they have a tab delimited file. Changing the file name for an .xls file may in fact lead to a corrupted file by accident when you open it in a text editor. Of course if the document is from too new a version of Excel, that would also be a hint. But I before changing any extension it would be well to determine first the source of the statement when it has an immediate contradiction to itself. Excel files by their very nature are not normally tab delimited, though they can be exported as such.