Merging csvs

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Merging csvs

Bill West
Hello!
  I have 20 csv files of student names and scores and would like to save
them as a single file sorted by last name.  I thought this would be
relatively easy, but I have not been able to find relevant documentation.
Any ideas?

Thanks,

Bill

Reply | Threaded
Open this post in threaded view
|

Re: Merging csvs

Gareth Oakes-2
Hi Bill,

Sounds like a job for a scripting language.  This would be a very simple
task in something like Perl or (insert favourite script language here)...

Cheers,
-G

Bill West wrote:
> Hello!
>   I have 20 csv files of student names and scores and would like to save
> them as a single file sorted by last name.  I thought this would be
> relatively easy, but I have not been able to find relevant documentation.
> Any ideas?
>
> Thanks,
>
> Bill

Reply | Threaded
Open this post in threaded view
|

Re: Merging csvs

Dominic Evans
This isn't really a vim related question at all :)

cat *.csv > merged.csv
sort -t , -k NUMBER merged.csv > sorted.csv

Replace NUMBER with the location of the student name in the comma
seperated list. If the student name is there first thing to appear at
the beginning of the line then you don't even need the -t and -k
arguments to sort

Cheers,
Dom

On 20/09/05, Gareth Oakes <[hidden email]> wrote:

> Hi Bill,
>
> Sounds like a job for a scripting language.  This would be a very simple
> task in something like Perl or (insert favourite script language here)...
>
> Cheers,
> -G
>
> Bill West wrote:
> > Hello!
> >   I have 20 csv files of student names and scores and would like to save
> > them as a single file sorted by last name.  I thought this would be
> > relatively easy, but I have not been able to find relevant documentation.
> > Any ideas?
> >
> > Thanks,
> >
> > Bill
>
>
Reply | Threaded
Open this post in threaded view
|

Re: Merging csvs

Tim Chase-2
In reply to this post by Bill West
> Hello! I have 20 csv files of student names and scores and
> would like to save them as a single file sorted by last name.
> I thought this would be relatively easy, but I have not been
> able to find relevant documentation. Any ideas?

You don't give much to go on regarding the format of the file,
its existing sort order, and which column holds the last name.
Additionally, are the columns quoted, and can any of the fields
preceeding the column-of-interest contain a comma within them?
(escaped by quoting perhaps?)

If it's just the first column, you can simply do

        :%!sort

Well, if you have the GNU "sort" command at your disposal, you
can simply run your text through

        :%! sort -t',' -k3

where -k3 specifies the 1-based column in which the last name is
found.  You might want to add "-f" as a parameter to treat upper
and lower case the same.

If, however, you don't have GNU sort at your disposal, and are
perhaps hobbled with Win32's "sort" command, you'll have to jump
through some hoops to get a sortable file out of your starting
stuff.  This generally involves moving/copying the column in
question the the start of the line, running the above "%!sort"
command on it, and then optionally move-back (or remove if you
copied) the leading bit.  Something like the following (where
\{2} represents the number of columns preceeding your column of
interest, so if the LN column is #3, you'd use the above \{2})

        :%s/\([^,]*,\)\{2}\([^,]*,\)/\2&
        :%!sort

and optionally to clean it up,
       
        :%s/^[^,]*,

The above 3-step uses the copy (rather than the move) way to do
things, as it's easier to clean up...and I'm lazy like that :)

One of the above should give you a solution for one file.  If you
want to do all N files, you can then do the following

        vim *.csv
        :set hidden
        :argdo %!sort -t',' -k3
(or whichever of the above solutions applies, each prefixed with
"argdo")  This should sort each file.

If, you wanted all the results in the same file, you can either
start vim with the concatinated bit

        bash> cat *.csv | vim -
or the Dos version of it:
        c:\>copy file1.csv+file2.csv+file3.csv+... all.csv
        c:\>vim all.csv

OR you can get vim to create that for you.

        vim *.csv
        :argdo %yA
        :enew
        :put a
        :%!sort -t',' -k3

Hope this gives you some ideas to work with.

For germane sections of the help, you can check out

        :help :range!
        :he argdo
        :he :y
        :he :enew
        :he :put
        :he /\(

-tim
PS: rah, Clemson!  My wife's from upstate SC, so we're out that
way once or twice a year.