-1

I have a text file that I wish to turn into a CSV. The pattern of its contents are as follows:

1 mm/dd/yyyy
LastName, FirstName MiddleName
ID-NUMBER-HERE
GENDER
2 mm/dd/yyyy
LastName, FirstName MiddleName
ID-NUMBER-HERE
GENDER
  • Number's 1 and 2 are line numbering of each set. I want to remove it.
  • The dates are birthdays. Is there a way to change it to another format?
  • Names: What if the person have two (2) first names or have a prefix? I want them all in a single column. Same with middle names and last names.

The file is 26MB in size.

The resulting format that I want is:

'yyyy-mm-dd','LastName','FirstName','MiddleName','ID-NUMBER','GENDER'

Is there a way to do this with SED or other tools?

I just learned about SED and it would take me longer if I try to do it myself.

Can anyone help out?

I'm on a mac btw.

Thanks a lot!

Alvin

  • it's not possible to detect First and Middle names that have more than one word as there is no way to distinguish between them in the input format. Lastname field is separated from the rest by a comma so can have multiple words. e.g. with `Smith, John Tarquin Percival" is "John" the First name? or is it "John Tarquin". There's no way to tell. – cas Nov 8 '15 at 7:32
  • i've modified the script so that if there is more than one name for First and Middle names, it is assumed that First has only one name and all the rest are part of Middle name – cas Nov 8 '15 at 7:44
0

Here's a perl script that reads in each line, splits them into fields (in the @line array), and adds them to the @out array. When there are 6 fields in @out, it prints it out.

Note: this is very basic CSV without quoting of strings etc - quotes aren't needed unless the fields contain the field-separator (i.e. a comma, ,). For more advanced CSV, it needs to be modified to use the Text::CSV or DBD::CSV perl modules.

Note also that it assumes that Last, First, and Middle names are only one word each.

#! /usr/bin/perl

use strict;

my @out = ();
while(<>) {
    my @line = split /,?\s+/;
    my $numfields=@line - 1;
    if ($line[0] =~ m/^\d+$/) {
        push @out, $line[1];
    } elsif ($numfields > 1) {
        push @out, (@line[0..1], join(" ",@line[2..$numfields]));
    } else {
        push @out, @line
    }

    if ( @out == 6 ) { print join(",",@out),"\n" ; @out=() } ;
}

Sample output (using your sample input saved as input.txt:

$ ./convert-to-csv.pl input.txt 
mm/dd/yyyy,LastName,FirstName,MiddleName,ID-NUMBER-HERE,GENDER
mm/dd/yyyy,LastName,FirstName,MiddleName,ID-NUMBER-HERE,GENDER
0

Using paste:

$ paste -d '|' - - - - <data.in >data.tmp1
$ cat data.tmp1
1 mm/dd/yyyy|LastName, FirstName MiddleName|ID-NUMBER-HERE|GENDER
2 mm/dd/yyyy|LastName, FirstName MiddleName|ID-NUMBER-HERE|GENDER

Now when we have this, we may filter out the numbers from the first field and replace any comma or space in the second field by |:

$ awk -F '|' 'BEGIN { OFS=FS } { gsub("^[0-9]* ", "", $1); gsub("[, ]+", "|", $2); print }' data.tmp1 >data.tmp2
$ cat data.tmp2
mm/dd/yyyy|LastName|FirstName|MiddleName|ID-NUMBER-HERE|GENDER
mm/dd/yyyy|LastName|FirstName|MiddleName|ID-NUMBER-HERE|GENDER

To reformat the dates:

$ awk -F'/' '{ printf("%s-%s-%s\n", $3, $1, $2) }' <( cut -d'|' -f1 data.tmp2 )
yyyy-mm-dd
yyyy-mm-dd

This may be replaced into the data:

$ paste -d '|' \
    <( awk -F'/' '{ printf("%s-%s-%s\n", $3, $1, $2) }' <( cut -d'|' -f1 data.tmp2 ) ) \
    <( cut -d '|' -f 2- data.tmp2 ) >data.tmp3
$ cat data.tmp3
yyyy-mm-dd|LastName|FirstName|MiddleName|ID-NUMBER-HERE|GENDER
yyyy-mm-dd|LastName|FirstName|MiddleName|ID-NUMBER-HERE|GENDER

If you have csvkit, this may be turned into propely quoted and comma-separated CSV:

$ csvformat -d '|' -D ',' -U 2 <data.tmp3
"yyyy-mm-dd","LastName","FirstName","MiddleName","ID-NUMBER-HERE","GENDER"
"yyyy-mm-dd","LastName","FirstName","MiddleName","ID-NUMBER-HERE","GENDER"

The above solution requires a shell that understands process substitutions with <(...).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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