advertisement
Forums

The Forum is sponsored by 
 

AAPL stock: Click Here

You are currently viewing the Tips and Deals forum
Excel CSV file not actually comma separated?
Posted by: mikebw
Date: August 10, 2022 11:00AM
In Excel on macOS, I've selected and copied 11 cells from a column, made a new file and then saved it as a .csv file.

When I open that file in TextEdit I see same 11 items but each on a new line, NO commas.

What gives?
Options:  Reply • Quote
Re: Excel CSV file not actually comma separated?
Posted by: GGD
Date: August 10, 2022 11:14AM
The commas separate cell values within the same ROW. You said that they were all in the same COLUMN, so you're probably seeing 11 lines in the file with one value per line/row, so no commas are needed.
Options:  Reply • Quote
Re: Excel CSV file not actually comma separated?
Posted by: Paul F.
Date: August 10, 2022 11:25AM
Yeah... what GGD said...

If you only have one column of information, say, first name, then a csv file will look like;

paul
john
steve
gail
angela

If you have a .CSV file of two columns, say, first name and last name, then your CSV will look like;

paul, first
john, second
steve, turd
gail, too
angela, also

The separation between the columns becomes the comma.



Paul F.
-----
A sword never kills anybody; it is a tool in the killer's hand. - Lucius Annaeus Seneca c. 5 BC - 65 AD
----
Good is the enemy of Excellent. Talent is not necessary for Excellence.
Persistence is necessary for Excellence. And Persistence is a Decision.

--

--

--
Eureka, CA
Options:  Reply • Quote
Re: Excel CSV file not actually comma separated?
Posted by: mikebw
Date: August 10, 2022 11:26AM
Quote
GGD
The commas separate cell values within the same ROW. You said that they were all in the same COLUMN, so you're probably seeing 11 lines in the file with one value per line/row, so no commas are needed.

Ahh that does make sense. OK so it looks like I could use the TEXTJOIN function to get that working the way I want. Thanks.

=TEXTJOIN(",",1,C84:C94), for example
[www.exceldemy.com]
Options:  Reply • Quote
Re: Excel CSV file not actually comma separated?
Posted by: Paul F.
Date: August 10, 2022 11:49AM
Quote
mikebw
Quote
GGD
The commas separate cell values within the same ROW. You said that they were all in the same COLUMN, so you're probably seeing 11 lines in the file with one value per line/row, so no commas are needed.

Ahh that does make sense. OK so it looks like I could use the TEXTJOIN function to get that working the way I want. Thanks.

=TEXTJOIN(",",1,C84:C94), for example
[www.exceldemy.com]

If you're trying to get;
Paul, steve, gail, angela, (etc)
Then you can also just do what you're doing (copy/paste), then do a find and replace in the text file.. find a return, replace it with a comma and a space.
It would take me longer to look up the TEXTJOIN function than to do a Find/Replace.



Paul F.
-----
A sword never kills anybody; it is a tool in the killer's hand. - Lucius Annaeus Seneca c. 5 BC - 65 AD
----
Good is the enemy of Excellent. Talent is not necessary for Excellence.
Persistence is necessary for Excellence. And Persistence is a Decision.

--

--

--
Eureka, CA
Options:  Reply • Quote
Re: Excel CSV file not actually comma separated?
Posted by: mikebw
Date: August 10, 2022 11:59AM
smiley-score010
Quote
Paul F.
Quote
mikebw
Quote
GGD
The commas separate cell values within the same ROW. You said that they were all in the same COLUMN, so you're probably seeing 11 lines in the file with one value per line/row, so no commas are needed.

Ahh that does make sense. OK so it looks like I could use the TEXTJOIN function to get that working the way I want. Thanks.

=TEXTJOIN(",",1,C84:C94), for example
[www.exceldemy.com]

If you're trying to get;
Paul, steve, gail, angela, (etc)
Then you can also just do what you're doing (copy/paste), then do a find and replace in the text file.. find a return, replace it with a comma and a space.
It would take me longer to look up the TEXTJOIN function than to do a Find/Replace.
Options:  Reply • Quote
Re: Excel CSV file not actually comma separated?
Posted by: TheTominator
Date: August 10, 2022 01:18PM
Copy the column in your Excel spreadsheet or anywhere else that has each item on a separate line.

Then in Terminal...


pbpaste | paste -sd, - > myfile.csv




Edit:
That was kind of interesting. I knew about "pbpaste", but I didn't know that the "paste" function existed as a BSD command.



Edited 1 time(s). Last edit at 08/10/2022 01:20PM by TheTominator.
Options:  Reply • Quote
Re: Excel CSV file not actually comma separated?
Posted by: bazookaman
Date: August 10, 2022 02:27PM
If you want your column as a row, just copy the column, then past special and choose, I want to say Transpose. But I'm not looking at Excel right now. THEN save as a CSV and you'd get a comma-separated text row.



Options:  Reply • Quote
Re: Excel CSV file not actually comma separated?
Posted by: wowzer
Date: August 11, 2022 10:08PM
In word, find: ^p

Replace all: ,



All I ever really needed to know, I learned from watching Star Trek.
Options:  Reply • Quote
Re: Excel CSV file not actually comma separated?
Posted by: mikebw
Date: August 12, 2022 09:07AM
hail smiley
Options:  Reply • Quote
Sorry, only registered users may post in this forum.

Click here to login

Online Users

Guests: 120
Record Number of Users: 186 on February 20, 2020
Record Number of Guests: 5122 on October 03, 2020