Numbers: Possible to transpose Rows and Columns during Import of txt file

findernumberssorttext;

I have a txt file which has 5,000 data points separated by commas.

i.e. data123,data456,data17,data918,etc,etc

If I open this txt file in Numbers, it creates each item as its own column, whereas I want each item to be its own row.

I can use the Table > Transpose Rows and Columns option, but Numbers is only able to have a maximum of 1,000 columns, and so there are 4,000 points of data that are missing from my spreadsheet using this method.

Is there a way that I can open the txt file in Numbers with the comma separated values each being created as a row instead of a column to begin with?

Thank you so much for any help!

Best Answer

An easy way

Open the Terminal application and navigate to the folder containing your .txt file. If you know how to use cd, use that. If you don't then open Finder and click on the folder containing your file and press ⌥⌘C. This will copy the path to the folder to your clipboard. Then in Terminal type, (pasting the clipboard ⌘V where it says <paste_here>):

cd '<paste_here>'

and run the following command, replacing <your_file_name> with the name of your file and <new_file_name> for the name you want to give the transposed output.

cat <your_file_name> | tr ',' '\n' > <new_file_name>

In case you aren't sure what's going on here:
cat takes one or more file names and outputs their contents
| takes the output of the thing on the left and sends it to the thing on the right
tr replaces any instances of the first character (, in this case) and replaces it with the second character (\n means a new line)

Learning how to use Terminal will help you solve these kinds of problems very easily. If you want to learn more about it, google "unix/linux shell scripting" and the language being used is "bash".

A better way

tr ',' '\n' < infile > outfile

I gave the first answer because I felt it was easier to understand for someone who is new to shell scripting. But we can optimize this command by using file redirection (command < filename) instead of cat. Cat is an external program, so we should get rid of it in the command if there is no reason to load it. tr doesn't accept filename arguments but it does accept standard input. Here we redirect "infile" to standard input (< infile) which is read by the command (tr ',' '\n'), then we write the output to outfile (> outfile).

You can also write this as: \

< infile tr ',' '\n' > outfile

I prefer writing it this way because it follows the same order as the commands are executed:
| Command | Action | | ------------- | ---------------------------------------------- | | < infile | redirect file named "infile" to standard input | | tr ',' '\n' | replace commas with newlines | | > outfile | write standard out to filename "outfile" |

Related Question