1

I have a comma separated list of email adresses for example "friend1@example.xyz, friend2@example.xyz, friend3@example.xyz"

But I would need to convert it to proper CSV format. So replacing the spaces, I assume.

But how can this be done? Do I need to use 'search and replace' with regex commands? And if so, how to?

Thank you in advance

  • Please edit and supply before and after example text. – DavidPostill Feb 14 '17 at 10:11
0

If the file contains no other space characters than the ones after the commas, there's no real need for regex in search and replace. You can just open the Replace... dialog and enter a space character in the Find what: box and make sure there's nothing in the Replace with: box.

Make sure to rename the file to .csv as well if it is not already. If you have Hide extensions for known file types enabled (Default) in Windows Explorer you will need to either re-save the file with the new file extension; disable the Windows Explorer setting or rename the file via the command line.

  • thank you!, I thought I needed regex, but it works also without :) – mravec Feb 14 '17 at 10:57
3

The CSV file format is not fully standardized even there exist RFC4180
(this standard only specifies handling of text-based fields).

RFC4180 requirements:

  • MS-DOS-style lines that end with (CR/LF) characters (optional for the last line).
  • An optional header record (there is no sure way to detect whether it is present, so care is required when importing).
  • Each record "should" contain the same number of comma-separated fields.
  • Any field may be quoted (with double quotes).
  • Fields containing a line-break, double-quote, and/or commas should be quoted. (If they are not, the file will likely be impossible to process correctly).
  • A (double) quote character in a field must be represented by two (double) quote characters.

Back to your question:

I have a comma separated list of email addresses for example
friend1@example.xyz, friend2@example.xyz, friend3@example.xyz

But I would need to convert it to proper CSV format. So replacing the spaces, I assume

To remove spaces:

  • Press Ctrl+H.
  • In the field Find what type [ \t]+
  • The field Replace withshould be empty.
  • In the Search mode section switch radio button to regular expression
    and hit button Replace All

When you cleared text from spaces, you may want to enclose column data in quotes:

  • Press Ctrl+H.
  • In the field Find what type ([^,\r\n]+)
  • In the field Replace withtype \"\1\"
  • In the Search mode section switch radio button to regular expression
    and hit button Replace All

Note: this (surrounding data in quotes) applies to the text saved in DOS/Windows format, if you processing data from Linux, then remove \r form Find what field.

  • thank you for the explanation, it works (also without regex) :) – mravec Feb 14 '17 at 10:55
  • @mravec It depend on CSV reader, if it would be default settings in Excel or LibreOffice, they would pick column data with spaces – Alex Feb 14 '17 at 11:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.