0

I've got a bunch of names (around 300), they were filled out as standard text in Excel. However in order to process this data I need to turn it into a list of names separated by commas (a csv). I can figure out how to do it the other way round (turn csv data into column data) but not column to csv way around

The data I have is already in columns, however I need the data as one long string of names separated by commas (How a CSV normally looks before organising the data into columns)

Saving as .csv although changes the file to be .csv the data inside is still in columns

Improve this question
4
  • Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer.
    – Community Bot
    Commented Jul 20, 2022 at 13:39
  • 1
    Saving as .csv does save it with commas (open the CSV file in a text editor - like Notepad - to see) it's just that if you reopen it in Excel it is going to know to use the commas to display it in columns.
    – Yisroel Tech
    Commented Jul 20, 2022 at 13:42
  • Yeah i noticed that just now, it's me being a little bit dense
    – Tomsta
    Commented Jul 20, 2022 at 13:48
  • using Python and the Pandas module you can read that Excel file straight out, extract the list of names and process it - quite quickly that far at least. Add your "processing" on top of it. You may well just export it as "csv", if your processing is implemented elsewhere (i.e. some other fixed software).
    – Hannu
    Commented Jul 21, 2022 at 17:05

2 Answers 2

Reset to default
3

Convert to / from CSV is workbook oriented.

Assuming you wish to maintain the original workbook, then copy the column to a new worksheet in a new workbook and save.

Now convert the new workbook to CSV.

If you do not need to keep the original data, then just convert the workbook to CSV.

This works.

Improve this answer
3
  • I don't understand how this is different from simply saving a file as .csv, which OP (incorrectly) says will not work
    – Alex M
    Commented Jul 20, 2022 at 23:43
  • Since only one column was wanted, that is why I suggested making a new workbook of it so as to maintain the original file. If the whole workbook can be converted then you can just save the workbook. My post was to ensure nothing was lost if just part of the file needed to be converted.
    – anon
    Commented Jul 20, 2022 at 23:47
  • Gotcha. Appreciate the clarification. The edit improves the answer IMO. +1
    – Alex M
    Commented Jul 22, 2022 at 17:09
0

"CSV" means "comma separated values", i.e., each item (value) in a row is followed by a comma, and the last item in a row is followed by the character to mark then end of a line (in Windows OS, CR & LF, \r\n are used for them in regular expressions, regex). To allow commas within a line, e.g., a name such as "H. Alger, Jr.", the value may be surrounded by the double-quotation mark.

To easily transform this list of names to CSV, assuming each name (i.e., record) is on a separate line:

  • Paste the list (or open a file with that list) into a text editor such as Notepad++.
  • Search, using regex, for \r\n.
  • Replace all instances of that expression with "\r\n"
  • Add a double quote at the beginning of the first line.
  • Save the file with extension .csv.

For the general case, use search and replace as below for two records:

  "field1","field2","field3"\r\n
  "field1","field2","field3"\r\n

Editing the text directly ensures that Excel, Calc, or other spreadsheet does not insert unwanted formatting.

N.B., this also the basis for writing a CSV file directly from your own application, in any programming language.

Improve this answer
4
  • Not sure who downvoted this. I would do it this way, too. Or with a macro to append each line with a ,
    – spikey_richie
    Commented Jul 20, 2022 at 14:10
  • I assume you MEANT: * Replace all instances of that expression with ", "
    – Hannu
    Commented Jul 20, 2022 at 16:36
  • @Hannu, no, since there is only a single field, in this example, there is no need to add any commas. The CR+LF, \r\n, mark both the end of a field and end of record. Thanks for pointing that out, for the general case; I'll include that in the answer.
    – DrMoishe Pippik
    Commented Jul 20, 2022 at 17:35
  • 1
    Incidentally, RFC 4180 specifies CRLF as the record separator, so a conforming CSV file will be platform-independent.
    – Andrew Morton
    Commented Jul 20, 2022 at 18:07

You must log in to answer this question.

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