10

I have a CSV file as

input.csv

"1_1_0_0_76"
"1_1_0_0_77"
"1_1_0_0_78"
"1_1_0_0_79"
"1_1_0_0_80"
"1_1_0_0_81"
"1_1_0_0_82"
"1_1_0_0_83"
"1_1_0_0_84"
"1_1_0_0_85"

............. and so on.

I need to convert this CSV file into

result.csv 

1,1,0,0,76
1,1,0,0,77
1,1,0,0,78
1,1,0,0,79
1,1,0,0,80
1,1,0,0,81
1,1,0,0,82
1,1,0,0,83
1,1,0,0,84
1,1,0,0,85
24

Far simpler way is to use tr

$ tr '_' ',' < input.csv | tr -d '"'                  
1,1,0,0,76
1,1,0,0,77
1,1,0,0,78

The way this works is that tr takes two arguments - set of characters to be replaced, and their replacement. In this case we only have sets of 1 character. We redirect input.csv input tr's stdin stream via < shell operator, and pipe the resulting output to tr -d '"' to delete double quotes.

But awk can do it too.

$ cat input.csv
"1_1_0_0_76"
"1_1_0_0_77"
"1_1_0_0_78"
$ awk '{gsub(/_/,",");gsub(/\"/,"")};1' input.csv
1,1,0,0,76
1,1,0,0,77
1,1,0,0,78

The way this works is slightly different: awk reads each file line by line, each in-line script being /Pattern match/{ codeblock}/Another pattern/{code block for this pattern}. Here we don't have a pattern, so it means to execute codeblock for each line. gsub() function is used for global substitution within a line, thus we use it to replace underscores with commas, and double quotes with a null string (effectively deleting the character). The 1 is in place of the pattern match with missing code block, which defaults simply to printing the line; in other words the codeblock with gsub() does the job and 1 prints the result.

Use the shell redirection (>) to send output to a new file:

 awk '{gsub(/_/,",");gsub(/\"/,"")};1' input.csv > output.csv
  • Apologies.I also wanted to remove the inverted commas too.I updated the question – RKR Jan 19 '17 at 2:39
  • @RKR Answer updated accordingly, Ian's answer is also updated – Sergiy Kolodyazhnyy Jan 19 '17 at 3:39
13

Just as an alternative, you can also use this sed command:

$ sed -e 's/_/,/g' -e 's/"//g' input.csv
1,1,0,0,76
1,1,0,0,77
1,1,0,0,78
  • 1
    Within single quotes, you don't need to escape a double quote. – glenn jackman Jan 20 '17 at 17:47
  • Indeed @glennjackman! I just removed the escaping backslash – IanC Jan 21 '17 at 4:01
10

Perl, the "Swiss army chainsaw" of command-line text processing, can also do this. The syntax is (not coincidentally) quite similar to the tr and sed examples:

perl -pe 'tr/_"/,/d' input.csv > result.csv

or:

perl -pe 's/_/,/g; s/"//g' input.csv > result.csv

But honestly, if you don't want to take the time to learn a new programming language (which is really what awk, Perl and sed and other tools like them are) just for this basic task, you could just as well do it in any text editor that supports search and replace:

  1. Open the CSV file in your favorite text editor (such as gedit, kate, mousepad, etc.; even plain old Notepad or Wordpad on Windows can do this).

  2. Select "Search and Replace" from the menu (typically found under "Edit", if there isn't a separate "Search" menu).

  3. Enter _ into the search box, and , into the replacement box.

  4. Click "Replace All".

  5. Repeat with " in the search box and nothing in the replacement box.

  6. Save the file.

Now, if you need to do this for 100 or 1000 files instead of just one, then learning a new command-line tool starts to make sense. And, of course, once you do know how to use Perl or sed or whatever, then you'll save a lot of time and effort with similar tasks later. But for just a one-off job that you don't expect to need to do again, sometimes a basic interactive tool like a text editor is the simplest solution.

3

You could do this with vim as well.

Open the file: vim input.csv, then use vim's advanced search tool s. Type colon (:) to get into command mode, and run commands like this:

:%s's/_/,/g'  -- Replaces all occurrences of _ with , in the current file.
:s/\"//g -- Replaces all occurrences of " with nothing in the current file.

Pretty much the same commands as in IanC's answer, but inside of vim instead of using sed.

2

Why not just change the default values of input and output Separator values

awk -F "_" 'BEGIN { OFS="," }; {gsub(/\"/,""); print $1,$2,$3,$4,$5}' input.csv

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.