0

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!

2

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" |

5
  • 2
    Maybe it's worth of mentioning that >> appends to the file if it exists and > overwrites. It could also be beneficial to describe the <as well – Aivar Paalberg Jan 18 at 10:47
  • 1
    Needless use of cat! Simply use: tr ',' '\n' < infile > outfile – user3439894 Jan 18 at 15:47
  • 1
    What a wonderful answer! I would have been happy with just the terminal command but I really appreciate the explanation. I'm familiar with the Terminal, I know how to cd where I need to go and to ls to look around, but I didn't even know the language was called bash. Now that I know tr I can change all sorts of things in txt files! I have grown more powerful in my problem solving. Thank you! – RanLearns Jan 18 at 17:38
  • RE: "The technically word for it is called "unix/linux shell scripting" and the language being used is "bash"." -- It's 'technical' not "technically" and under macOS there is no Linux about it! My shell is zsh, the default in clean installs since macOS Mojave, so the language being used is is not bash if your shell is zsh! – user3439894 Jan 18 at 17:47
  • 1
    @RanLearns RE: “Now that I know tr I can change all sorts of things in txt files! I have grown more powerful in my problem solving.” — While tr is definitely a valuable tool and is excellent at what it does, you may want to look into using sed for manipulating text also. – wch1zpink Jan 18 at 23:19

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .