How to convert a text file into csv using AWK

Question

I am collecting usb usage details of all users and convert it into CSV files so that I can export it into some database. The input text file is as follows:-

USB History Dump
by nabiy (c)2008 
(1) --- Kingston DataTraveler 130 USB Device 
instanceID: 0018F3D974B4A9C0E1760896&0
ParentIdPrefix: 7&b62e00e&2
Last Mounted As: \DosDevices\I:
Driver:{4D36E967-E325-11CE-BFC1-08002BE10318}\0033
Disk Stamp: 09/07/2010 15:07
Volume Stamp: 09/07/2010 15:07 
(2) --- Kingston DataTraveler 2.0 USB Device 
instanceID: 001D0F1E35B25B8C1201011B&0
ParentIdPrefix: 7&1f5848f3&0
Driver:{4D36E967-E325-11CE-BFC1-08002BE10318}\0035
Disk Stamp: 09/06/2010 15:18
Volume Stamp: 09/06/2010 15:18 
(3) --- Maxtor OneTouch III USB Device 
instanceID: 044303E5&0
ParentIdPrefix: 
Driver:{4D36E967-E325-11CE-BFC1-08002BE10318}\0032
Disk Stamp: 09/10/2010 10:09
Volume Stamp: 03/12/2010 10:42 

How can I parse this file so that I can get in following format:

hostname Devic_name instanceID ParentPrefix LastMountedAs Driver 
pcname kingston xxxx xxxxxxxxx xxxxxxxxxx xxxxxxxx
pcname maxtor 0440xxx 4 d 367 08/07/2010 xxxxxxxx
pcname kingston xxxxxxx xxxxxxx xxxxxxxxx xxxxxxxx

The pc name will be taken from hostname command.

The output desired is in CSV format for database with some batch or awk script. Any suggestion is greatly appreciated.

Answer 1

This thread may be helpful to you...

Answer 2

Using Perl, this could be handled like this, generating genuine CSV data:

use strict;
use warnings;

my @keys = ( "Device_Name", "instanceID", "ParentIdPrefix",
             "Last Mounted As", "Driver" );

my %values = ();
my $host = qx/hostname/;
chomp $host;

while (<>)
{
    chomp;
    next unless m/^\(\d+\) ---/ || m/^[\w ]+:/;
    if (m/\(\d+\) --- (\w+)/)
    {
        dump_entry(\%values);
        %values = ();
        $values{Device_Name} = $1;
    }
    else
    {
        my($key,$value) = split /:/;
        $value =~ s/^\s+//;
        $value =~ s/\s+$//;
        $values{$key} = $value if $value ne "";
    }
}
dump_entry(\%values);

sub dump_entry
{
    my($ref) = @_;
    my(%values) = %$ref;
    return if (scalar(keys %values) == 0);
    print qq%"$host"%;
    foreach my $key (@keys)
    {
        my $value = $values{$key} // "--none--";
        print qq%,"$value"%;
    }
    print "\n";
}

Example output from given data file:

"yourpcname","Kingston","0018F3D974B4A9C0E1760896&0","7&b62e00e&2","\DosDevices\I","{4D36E967-E325-11CE-BFC1-08002BE10318}\0033"
"yourpcname","Kingston","001D0F1E35B25B8C1201011B&0","7&1f5848f3&0","--none--","{4D36E967-E325-11CE-BFC1-08002BE10318}\0035"
"yourpcname","Maxtor","044303E5&0","--none--","--none--","{4D36E967-E325-11CE-BFC1-08002BE10318}\0032"

Note that the data is presented in the order read, unlike the output data in the question.