Take the 2-minute tour ×
Ask Ubuntu is a question and answer site for Ubuntu users and developers. It's 100% free, no registration required.

In a large (>1 gb) csv file I have something like

"34432", "name", "0", "very long description"

but instead of that I'd like to have

34432, "name", 0, "very long description".

I was looking at sed but this task is out of my scope.

Any advice how to achieve this?

share|improve this question
1  
Maybe unnecessary. Just load that csv into a database table where all columns are strings, then change the definition of the 1st and 3rd columns to integer after all the data is there. It should retain the values. –  Sam Apr 9 at 0:20
    
Interesting suggestion @Sam! –  Balázs Mária Németh Apr 9 at 8:02

3 Answers 3

up vote 4 down vote accepted

Using perl:

perl -ne 's/"(\d+)"/$1/g; print' file.csv > new_file.txt

All the work is done by s/"(\d+)"/$1/g where

  • s/patternA/patternB/ is used to replace patternA by patternB
  • then perl looks for one or more digits \d+ surrounded by double quotes.
  • the parenthesis around (\d+) are used to capture the digit(s) and reuse them as a replacement pattern with perl special variable $1.
share|improve this answer
    
+1 for explanation. –  Avinash Raj Apr 8 at 16:02

A GNU sed regex that should work for this case is

sed -r 's/"([0-9]+)"/\1/g'    

For pure sed you need to escape the grouping parentheses and + modifier

sed 's/"\([0-9]\+\)"/\1/g'

You can perform the substitution in-place with some versions of sed e.g.

sed -ri 's/"([0-9]+)"/\1/g' file.csv

You could also use the POSIX class [[:digit:]] in place of character range [0-9]

share|improve this answer

Your description of the problem is not very specific. I am assuming you want to remove the double quotes around the 1st and 3rd fields only. If so, any of these should work:

  1. sed

    sed -r 's/^"([^"]+)"(\s*,\s*[^,]+)\s*,\s*"([^"]+)"/\1\2, \3/' file.csv
    

    Explanation

    The -r enables extended regular expressions, allowing us to use parentheses to capture patterns without needing to escape them. So, we match a quote at the beginning of the line (^"), followed by one or more non-quote characters ([^"]+), then the closing quote, followed by 0 or more spaces, a comma, then 0 or more spaces again (\s*,\s*), then a stretch of non-commas until the next comma (this defines the 2nd field). Finally, we look for 0 or more spaces, a comma, and replace that with the 1st captured pattern (\1), then the 2nd (\2), a comma, a space and the 3rd.

  2. Perl

    perl -pe 's/^"([^"]+)"(\s*,\s*[^,]+)\s*,\s*"([^"]+)"/$1$2, $3/; ' file.csv
    

    Explanation

    The -p means print every line after applying the script passed by -e. The script itself is basically the same regex as in the sed above. Only here, the captured patterns is $1.

  3. awk

    awk -F, -v OFS="," '{gsub("\"","",$1)0gsub("\"","",$3);}1;' file.csv 
    

    Explanation

    The -F sets the field separator to ,. OFS is the output field separator which is also set to , so that the lines are printed correctly. The gsub makes the substitution, replacing all " with nothing since we run it on the 1st ($1) and 3rd fields ($3) it will only remove the quotes from those fields. The 1; is just awk shorthand for "print the line".

share|improve this answer
    
@AvinashRaj thanks, answer edited. –  terdon Apr 8 at 16:06
    
I wanted to trim the integers. –  Balázs Mária Németh Apr 8 at 16:09
    
@BalázsMáriaNémeth this will trim the quotes from the 1st and 3rd fields. It works no matter what you have in those fields, integer or anything else. –  terdon Apr 8 at 16:10
    
Ok, thanks for your effort. At least I know how to clean the columns in the specific positions but actually I needed that regexp/capture thing on the integers. –  Balázs Mária Németh Apr 8 at 16:11
    
@BalázsMáriaNémeth in that case use the accepted answer but careful, \d does not only match integers, it will also match numbers like 0.5 and depending on your locale settings more obscure stuff as well. –  terdon Apr 8 at 16:13

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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