Need help parsing file/writing script

Question

Hey all, I have been doing nothing but web development over the last few years and haven't written any Java or C++ in what feels like forever. I don't necessarily need to use these languages, so I'm entirely open to suggestion. I was given an email list by a client to import into their mailchimp account yesterday and unfortunately, Mailchimp couldn't read the file. It's a text file, but I don't believe it's tab delimited (which would make this much, much easier for me).

A small portion of the file (I've changed last names and email addresses) can be viewed here: http://sparktoignite.com/patients.txt

If anyone has suggestions on how I can get this into a Mailchimp readable format (csv, tab delimited txt, excel) please let me know. I feel like 3 years ago I would've been able to do this in a matter of minutes, but given that I haven't touched anything other than RoR, PHP, and jQuery for the last few years, I don't know where to start.

Thanks!

Answer 1

if you are on *nix, you can use tools like awk

awk -F"|"  'NR>2{$1=$1}1' OFS=","  file > newfile.xls

however, you stated that you know PHP, so why not stick to something you know. you can use fgetcsv()/fputcsv() function

$output=fopen("out.csv","w");
$handle = fopen("file", "r");
if ($handle ) {
    $line=fgetcsv($handle, 2048, "|");
    $line=fgetcsv($handle, 2048, "|");
    while (($data = fgetcsv($handle, 2048, "|")) !== FALSE) {
        $num = count($data);
        fputcsv($output,$data,',');
    }
    fclose($handle);
    fclose($output);
}

Answer 2

In bash, outputs TAB delimited file:

cat patients.txt | tr -d [[:blank:]] | tr "|" "\t" > output.txt

If you prefer csv, just change the last "\t" to ",":

cat patients.txt | tr -d [[:blank:]] | tr "|" "\t" > output.txt

It messes up the header though. If you need to preserve header, first couple of lines need to be skipped:

head -n2 > output.txt
tail -n+3 | tr -d [[:blank:]] | tr "|" "\t" >> output.txt