Sed script to edit csv file Or Python

Question

In our project we need to import the csv file to postgres. There are multiple types of files meaning the length of the file changes as some files are with fewer columns and some with all of them.

We need a fast way to import this file to postgres. I want to use COPY FROM of the postgres since the speed requirement of the processing are very high(almost 150 files per minute with 20K file size each).

Since the file columns numbers are not fixed, I need to pre-process the file before I pass it to the postgres procedure. The pre-processing is simply to add extra commas in the csv for columns, which are not there in the file.

There are two options for me to preprocess the file - use python or use Sed.

My first question is, what would be the fastest way of pre-process the file?

Second question is, If I use sed how would I insert a comma after say 4th, 5th comma fields?
e.g. if file has entries like 1,23,56,we,89,2009-12-06 and I need to edit the file with final output like: 1,23,56,we,,89,,2009-12-06

Answer 1

To answer your first question, sed would have less overhead, but might be painful. awk would be a little better (it's more powerful). Perl or Python have more overhead, but would be easier to work with (regarding Perl, that's maybe a little subjective ;). Personally, I'd use Perl).

As far as the second question, I think the problem might be a little more complex. For example, don't you need to examine the string to figure out what fields are actually missing? Or is it guaranteed that it will always be the 4th and 5th? If it's the first case case, it would be way easier to do this in Python or Perl rather than in sed. Otherwise:

echo "1,23,56,we,89,2009-12-06" | sed -e 's/\([^,]\+\),\([^,]\+\),\([^,]\+\),\([^,]\+\),\([^,]\+\),/\1,\2,\3,\4,,\5,,/'

or (easier on the eyes):

echo "1,23,56,we,89,2009-12-06" | sed -e 's/\(\([^,]\+,\)\{3\}\)\([^,]\+\),\([^,]\+\),/\1,\3,,\4,,/'

This will add a comma after the 5th and 4th columns assuming there are no other commas in the text.

Or you can use two seds for something that's a little less ugly (only slightly, though):

echo "1,23,56,we,89,2009-12-06" | sed -e 's/\(\([^,]*,\)\{4\}\)/\1,/' | sed -e 's/\(\([^,]*,\)\{6\}\)/\1,/'

Answer 2

sed 's/^([^,]*,){4}/&,/' <original.csv >output.csv

Will add a comma after the 4th comma separated field (by matching 4 repetitions of <anything>, and then adding a comma after that). Note that there is a catch; make sure none of these values are quoted strings with commas in them.

You could chain multiple replacements via pipes if necessary, or modify the regex to add in any needed commas at the same time (though that gets more complex; you'd need to use subgroup captures in your replacement text).

Answer 3

Don't know regarding speed, but here is sed expr that should do the job:

sed -i 's/\(\([^,]*,\)\{4\}\)/\1,/' file_name

Just replace 4 by desured number of columns

Answer 4

Depending on your requirements, consider using ETL software for this and future tasks. Tools like Pentaho and Talend offer you a great deal of flexibility and you don't have to write a single line of code.

Answer 5

Are you aware of the fact that COPY FROM lets you specify which columns (as well as in which order they) are to be imported?

COPY tablename ( column1, column2, ... ) FROM ...

Specifying directly, at the Postgres level, which columns to import and in what order, will typically be the fastest and most efficient import method.

This having been said, there is a much simpler (and portable) way of using sed (than what has been presented in other posts) to replace an n th occurrence, e.g. replace the 4th and 5th occurrences of a comma with double commas:

echo '1,23,56,we,89,2009-12-06' | sed -e 's/,/,,/5;s/,/,,/4'

produces:

1,23,56,we,,89,,2009-12-06

Notice that I replaced the rightmost fields (#5) first.

I see that you have also tagged your question as perl-related, although you make no explicit reference to perl in the body of the question; here would be one possible implementation which gives you the flexibility of also reordering or otherwise processing fields:

echo '1,23,56,we,89,2009-12-06' |
  perl -F/,/ -nae 'print "$F[0],$F[1],$F[2],$F[3],,$F[4],,$F[5]"'

also produces:

1,23,56,we,,89,,2009-12-06

Very similarly with awk, for the record:

echo '1,23,56,we,89,2009-12-06' |
  awk -F, '{print $1","$2","$3","$4",,"$5",,"$6}'

I will leave Python to someone else. :)

Small note on the Perl example: I am using the -a and -F options to autosplit so I have a shorter command string; however, this leaves the newline embedded in the last field ($F[5]) which is fine as long as that field doesn't have to be reordered somewhere else. Should that situation arise, slightly more typing would be needed in order to zap the newline via chomp, then split by hand and finally print our own newline character \n (the awk example above does not have this problem):

perl -ne 'chomp;@F=split/,/;print "$F[0],$F[1],$F[2],$F[3],,$F[4],,$F[5]\n"'

Cheers, V.

EDIT (an idea inspired by Vivin):

COMMAS_TO_DOUBLE="1 4 5"
echo '1,23,56,we,89,2009-12-06' |
  sed -e `for f in $COMMAS_TO_DOUBLE ; do echo "s/,/,,/$f" ; done |
    sort -t/ -k4,4nr | paste -s -d ';'`

1,,23,56,we,,89,,2009-12-06

Sorry, couldn't resist it. :)

Answer 6

@OP, you are processing a csv file, which have distinct fields and delimiters. Use a tool that can split on delimiters and give you fields to work with easily. sed is not one of them, although it can be done, as some of the answers suggested, but you will get sed regex that is hard to read when it gets complicated. Use tools like awk/Python/Perl where they work with fields and delimiters easily, best of all, modules that specifically tailored to processing csv is available. For your example, a simple Python approach (without the use of csv module which ideally you should try to use it)

for line in open("file"):
    line=line.rstrip() #strip new lines
    sline=line.split(",")
    if len(sline) < 8: # you want exact 8 fields
        sline.insert(4,"")
        sline.insert(6,"")
        line=','.join(sline)
    print line

output

$ more file
1,23,56,we,89,2009-12-06

$ ./python.py
1,23,56,we,,89,,2009-12-06