Numbers Text Import Options

The new version of Numbers for Mac allows you to make adjustments after importing a table of data from a text file. You can import comma-separated, tab-separated or spaced text data, or use custom separators as well. This feature allows you to remove excess header rows, move spacing, deal with quotes and other options.
Video Transcript / Captions
Closed captioning for this video is available on YouTube: Numbers Text Import Options.

So a new feature in Numbers 5.0, which just came out, is the ability to import data from text files and have more options on how the data is brought in. Before if the data wasn't quite perfectly formatted then you may end up having to make lots of adjustments or maybe not even be able to use the data directly from the file. Now you can do things like this.

Let's go to Open and I'm going to select a file here that just has some census data. Just a random census file I downloaded from a government website. I'm going to open it up and it'll open up the data here. It looks pretty good actually. But in addition to seeing the data I also get Table data with imported....Adjust Settings. So here I can click Adjust Settings and I get this additional control here that allows me to do a bunch of different things.

First it shows me the input here. It looks just like it does over here. But I can now make adjustments and see them reflected here. So, for instance, I can say Separate Values not using a comma but using a tab. Of course the data here is definitely comma separated so comma was the right call. I can also select multiple ones. So I can say use tabs and commas. So you can really differentiate between different things here to get your data in perfectly. You can even indicate a Custom Delimiters if the data isn't using tabs or commas or anything like that.

There's an Advanced Settings section and I can set a starting row. So maybe if there was a bunch of data at the beginning of a file I can ignore the first few rows of data. Treat consecutive delimiters as one so multiple commas or multiple tabs. I can deal with decimal separators a little bit better if it's a comma instead of a period. Thousands separator as well. You can see it is automatic when there's nothing in there. Text Qualifier which, you know, if quotes are around a number it's taken as text rather than converted to an actual number. You can do a single quote or None for that.

So you can deal with all of that and whatever you decide you can update the table. So, for instance, if I say I don't want the first few rows I can Update Table. You can see now it changes it. Of course now it's wrong because it got it right the first time.

Let's look at another example. I'm going to open up what is a text file here and I'm going to get the same thing here of Adjust Settings. It brings it in and it seems like it's okay although I can see one error right here in the name of the person in that third row. Adjust Settings I can see that this is a fixed width file. In other words the text file itself has fixed width. I can open it up in TextEdit. You can see here instead of tabs these are just spaces to line things up. If you get a file like that it will try to convert it and you can see it's slightly wrong. But with Fixed Width selected I can actually adjust the lines to get it just right. So I can move that line there just where it should be.I have Advanced Settings for this as well. I can update the table and you can see now it corrected the error. So it's nice to be able to have that. I can go back to Adjust Settings if I wanted to continue working with it as well.

You can also drag and drop as well instead of using File Open. So I can, for instance, take this file which has a tab separated columns rather than comma and I can drag and drop it onto Numbers and it will open it up in the same way. You can see it gets it right there. I can still hit Adjust Settings and work on it. You can see it figured out that this was tab separated, not comma separated, and I can continue to adjust settings and see the results before I hit Update Table.

So this is a really handy new feature especially for people that have to work with data sets a lot. It works with comma separated, tab separated, you know lined up text documents with spaces, and just about any kind of text formats. It's not meant to work with anything like excel documents which can be imported in much more directly since they deal with all the same spreadsheet items that Numbers deals with.