Just been working with my favourite language to change a space delimited file into a comma delimited file (CSV). With large files, even good file editors like JujuEdit take forever. For the first time I have actually crashed this program whilst searching and replacing a 250mb file. Maybe it’s me?!

To avoid this problem reoccuring, and because I have not updated my site in a while, here is a nifty perl one-liner to search and replace spaces with comma-space.

First install a perl package, I use ActivePerl. Its free and easy to install on Windows (there are many different OS versions available).

Second, after the install has completed, open up the Windows command prompt (Start > Run > cmd). Change the directory to your working directory that contains the files that you wish to search and replace. For example (in blue):

C:\>cd c:\perl\myworkingdir

Now your in your working directory, you need to type the following (in blue):

c:\perl\myworkingdir>c:\perl\bin\perl.exe -p -i.bak -e “s/ /, /g” filename.csv

Here is the breakdown:

  • c:\perl\bin\perl.exe: Calls the perl program
  • -p: Loops the search and replace through the file.
  • -i.bak: Creates a backup file incase you make a mistake.
  • -e: Makes the script directly executable.
  • “s/ /, /g”: The expression to pattern match, in this case s means start of line / / is the space, , / is the string to replace it and g is to tell perl to run it on all the file, not just the first instance.
  • filename.csv: The name of the file to search and replace.