Writing out comma separated values in a single cell in spreadsheet

11233
21
Jump to solution
04-24-2015 10:56 AM
benberman
Occasional Contributor

Please excuse the long subject header. I have gotten pretty good at writing data out to csv files but i seem to have stumbled into this block. Anytime a ',' (comma) is used to join values, it shifts over to the next cell in the spreadsheet. What I want to accomplished is for example:

Inside a single cell, write out cat,dog,mouse

Already checked the csv module in python docs and nothing immediately jumped out at me.

any ideas?

Tags (3)
1 Solution

Accepted Solutions
RobertScheitlin__GISP
MVP Emeritus

...,

   Wrap the string in quotes if it has a comma in the value.

View solution in original post

21 Replies
RobertScheitlin__GISP
MVP Emeritus

...,

   Wrap the string in quotes if it has a comma in the value.

JeffWard
Occasional Contributor III

You're faster at the typing Robert.

Jeff Ward
Summit County, Utah
JeffWard
Occasional Contributor III

You could make the output tab delimited instead of comma delimited - ('\t' in python).  Or maybe enclose that whole string in quotes.

Jeff Ward
Summit County, Utah
JamesCrandall
MVP Frequent Contributor

If it's data derived internal to your organization, then I would suggest that you need to address the way that data is getting generated, apply some business rules to that process so that you don't run into issues just like this later on down the road.

0 Kudos
benberman
Occasional Contributor

The string will not have commas in it. I am trying to insert commas into the string to separate out the values, without shifting the values into consecutive cells. So, the string will be something like this:

e.g. catdogmouse

I'm trying to insert a comma between each word within the same cell. Make sense?

0 Kudos
JeffWard
Occasional Contributor III

Enclosing in double quotes doesn't work? output = '"cat,dog,mouse"' or single quote double quote cat,dog,mouse double quote single quote.

Are you using python to parse?

Jeff Ward
Summit County, Utah
JamesCrandall
MVP Frequent Contributor

ah...I misunderstood.

So, how do you determine what is a word?

Codewise, "catdogmouse" is a word.  I'm not aware of any methods you could implement that would just pick out "cat", "dog" or "mouse" from a string.  We do that as humans but programmatically I would think you'd need a library/dictionary of possible words that had those three included to test each value.

0 Kudos
DanPatterson_Retired
MVP Emeritus

grief...read Robert's initial post

>>> import string
>>> a = ' "cat, mouse, dog" '  # a double-quoted string, surrounded with single quotes...or equivalent
>>> b = string.split(a,",")
>>> b                          # yields a list with a single entry
[' "cat', ' mouse', ' dog" ']
>>> c = "cat, mouse, dog"
>>> d = string.split(c,",")    # yields a list with 3 entries
>>> d
['cat', ' mouse', ' dog']

Examine the csv module, it handles these cases quite well.

JamesCrandall
MVP Frequent Contributor

I see the OP's question differently...

e.g. "catdogmouse"

I'm trying to insert a comma between each word within the same cell. Make sense?

So, how are we supposed to know what is "a word" within the string?  I mean, sure it's easy to add in a "," between words, but you will have to have a dictionary of words to find within the string "catdogmouse".  To the computer "catdogmouse" is a word.  The computer doesn't know "dog" is a word and you will need some way to tell it what those words are.

0 Kudos