5

I have several huge CSV files in which I want to swap two column names.

I do not want to modify/copy/rewrite the data.

The operation is very cheap in C: fopen the file, fgets the header, fseek or rewind, manipulate the header (preserving its length), fputs the new header, fclose the file.

This can also be done in ANSI Common Lisp (CLISP, SBCL or GCL):

 (with-open-file (csv "foo.csv" :direction :io
                      :if-exists :overwrite)
   (let ((header (read-line csv)))
     (print header)
     (file-position csv 0)
     (write-line (string-upcase header) csv)
     (file-position csv 0)
     (read-line csv)))

and takes a fraction of a second (sed takes a few minutes because it reads and re-writes the whole file even it you tell it to modify just the first line, ignoring the crucial information that the size of the header did not change).

How do I do that with the "standard unix tools" (e.g., perl)?

4 Answers 4

6

If you do not know the length of the header, head -n1 seems like a reasonable way to get the first line.

To write it in-place back to the head of the file, you can use dd:

head -n1 file.csv | ./do-some-processing | dd of=file.csv bs=1 conv=notrunc

the conv=notrunc is critical to leave the rest of the file intact, and bs=1 is to stop on byte boundary.

1
  • Nice, I never knew about notrunc. But note that ./do-some-processing must preserve the length of the header (as specified by the OP.) Just a warning for tl;dr folks (like me :)
    – Owen
    May 8, 2015 at 19:41
2

I would suggest sed for this, you can specify to only make the substitution on the first line such as 1s/foo/bar/:

$ cat file
col1,col2,col3
1,2,3
3,2,1
...

$ sed -e '1s/col1/tmp/' -e '1s/col3/col1/'  -e '1s/tmp/col3/' file
col3,col2,col1
1,2,3
3,2,1
...

Use -i to store the change back to the file:

$ sed -i -e '1s/col1/tmp/' -e '1s/col3/col1/'  -e '1s/tmp/col3/' file
1
  • 1
    This does modify the file as required, but this re-writes the data, i.e., time shows that the sed command takes about the same time (few minutes) as cp.
    – sds
    Dec 18, 2012 at 15:01
0

If all you want is to swap two words, then all you need is in-place rewriting of a few bytes.

This is an easy task for a commandline hexadecimal editor.

I would recommend hexedit which I just used to edit a 30 Gb .csv file. The time spent on opening/saving the file was negligible (less than a second). In fact, my time was mostly spent looking up its keyboard shortcuts... (TAB to switch to ASCII display, Ctrl-X to save and exit).

1
  • The idea was to use a batch, not an interactive, process.
    – sds
    Jul 12, 2017 at 14:14
-1

Or maybe "head" the file to remove the first line to a separate file.

Then change the heading file and merge the two back together.

4
  • this copies the data, i.e., the time spent is proportional to the data size.
    – sds
    Dec 18, 2012 at 14:37
  • So why mark this down and accept the same answer above? Aug 12, 2014 at 16:00
  • because the answer above is constant in time and this answer is not
    – sds
    Aug 12, 2014 at 17:22
  • In all fairness, you didn't explain how to merge the files together. Using dd was the non-trivial insight.
    – b0fh
    Sep 24, 2014 at 10:29

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .