Tutorial :vim & csv file: put header info into a new column



Question:

I have a large number of csv files that look like this below:


xxxxxxxx
xxxxx
Shipment,YD564n
xxxxxxxxx
xxxxx

1,RR1760
2,HI3503
3,HI4084
4,HI1824


I need to make them look like the following:


xxxxxxxx
xxxxx
Shipment,YD564n
xxxxxxxxx
xxxxx

YD564n,1,RR1760
YD564n,2,HI3503
YD564n,3,HI4084
YD564n,4,HI1824


YD564n is a shipment number and will be different for every csv file. But it always comes right after "Shipment,".

What vim command(s) can I use?


Solution:1

You can do this using a macro, and applying it over several files.

Here's one example. Type the following in as is:

3gg$"ayiw:6,$s/^/<C-R>a/<CR>:w<CR>:bn<CR>  

Now that looks horrendous. Let me see if I can explain that a bit better.

3gg$ : Go to the end of the third line.

"ayiw : Copy the last word into the register a.

:6,$s/^/<C-R>a/<CR> : In every line from the 6th onwards, replace at the beginning whatever is in register a.

:w<CR>:bn<CR> : Save and go to the next buffer.

Now you can map this to a key, by

:nnoremap <C-A> 3gg$"ayiw:6,$s/^/<C-R>a/<CR>:w<CR>:bn<CR>  

Then if you have say 200 csv files, you open vim as

vim *.csv  

and then

200<C-A>  

Where you type Ctrl-A there, and it should be all done.

That said, I'd definitely be more comfortable doing this in a proper scripting language, it'd be much more straightforward.


Solution:2

In one file type the following in normal mode:

qqgg/^Shipment,<CR>ww"ay$}j:.,$s/^/<C-R>a,<CR>q  

Note that <CR> is the ENTER key, and <C-R> is CTRL-R.

This will update that file and recrd the commands in register q.

Then in each other file type @q (also in normal mode). (this will play back register q)


Solution:3

This could be done as a Perl one-liner:

perl -i.bak -e' $c = do {local $/; <>};                  ($n) = ($c =~ /Shipment,(\w+)/);                  $c =~ s/^(\d+,)/$n,$1/gm;                  print $c' shipment.csv  

This will read contents of shipment.csv into $c, extract the shipment ID into $n, and prepend every CSV line with the shipment number. The file will be modified in-place with a backup saved to shipment.csv.bak.

To do this from within Vim, adapt it as a filter:

:%!perl -e' $c = do {local $/; <>}; ($n) = ($c =~ /Shipment,(\w+)/); $c =~ s/^(\d+,)/$n,$1/gm; print $c'  


Solution:4

Well, don't bash me, but... you could consider: Don't do this in vim!!

This is a classic usage example for scripting languages. Take a basic python, perl or ruby tutorial. The solution for this would be in it.

The regex for this might not be too difficult and it is doable in vim. But there are much easier alternatives out there. And much more flexible ones.


Solution:5

Why vim?

Try this shell script:

#!/bin/sh    input=$1    shipment=`grep Shipment $input|awk -F, '{print $2}'`    mv $input $input.orig    sed -e "s/^\([0-9]\)/$shipment,\1/" $input.orig > $input  

You could iterate through specific files:

for input in *.txt  do      script.sh $i  done  


Solution:6

I also think this isn't well suited for vim, how about in Bash instead?

FILENAME='filename.csv' && SHIPMENT=`grep Shipment $FILENAME | sed 's/^Shipment,//'` && cat $FILENAME | sed "s/^[0-9]/$SHIPMENT,&/" > $FILENAME  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »