Shell Programming and Scripting

Column1|Column2|Column3|Column4|NEWLINE
Address Type|some descriptive text|"Mailing
Physical
Shipping"|some text here|NEWLINE
Street Number/Name|some description for rec 2|None|some text here|NEWLINE
City|description for rec 3|None|some text here too|NEWLINE
Zip|description for rec 4|text here|sdfla sdflsdhlsdfsk|NEWLINE
Record 5|description|"Main - Main record
Administrative - Administrative phone number
HR - HR phone number
Services - Services line
Fax - Facsimile phone number
Other"|some text here|NEWLINE
Grades Offered|description here|"IT - Infant/toddler
PR - Preschool
PK - Prekindergarten
TK - Transitional Kindergarten
KG - Kindergarten
01 - First grade
02 - Second grade
UG - Ungraded
Other"|some text here|NEWLINE

insert into MyTBl values ( Column1,Column2,Column3,Column4,NEWLINE);
insert into MyTBl values ( 'Address Type','some descriptive text','Mailing \nMailing \n Shipping' ,'some text here','NEWLINE');
insert into MyTBl values ( 'Street Number/Name','some description for rec 2','None','some text here','NEWLINE');
insert into MyTBl values ( 'City','description for rec 3','None','some text here too','NEWLINE');
insert into MyTBl values ( 'Zip','description for rec 4','text here','sdfla sdflsdhlsdfsk','NEWLINE');
insert into MyTBl values ( 'Record 5','description','Main - Main record \nAdministrative - Administrative phone number \nHR - HR phone number \nServices - Services line \nFax - Facsimile phone number \nOther','some text here','NEWLINE');
insert into MyTBl values ( 'Grades Offered','description here','IT - Infant/toddler \nPR - Preschool \nPK - Prekindergarten \nTK - Transitional Kindergarten \nKG - Kindergarten \n01 - First grade \n02 - Second grade \nUG - Ungraded \nOther','some text here','NEWLINE');

Column1|Column2|Column3|Column4|NEWLINE

BEGIN { FS="|"; OFS="," }
# Append 'short' lines into one longer line
NF<5 { if(S){ $0="\\n" $0; } S=S $0; $0=S }
# When it fially ends in "NEWLINE", we know we have an entire record
$NF == "NEWLINE" {
        for(N=1; N<=NF; N++)
        # Add single quotes to things lacking double quotes
        if(substr($N,1,1) != "\"")
                $N = "'" $N "'";

        print "insert into MyTBI values("$1,$2,$3,$4,$5");";
        S="";
}

$ awk -f myawk.txt < data
insert into MyTBI values('Column1','Column2','Column3','Column4','NEWLINE');
insert into MyTBI values('Address Type','some descriptive text',"Mailing\nPhysical\nShipping",'Unchanged from Version 1','NEWLINE');
insert into MyTBI values('Street Number/Name','some description for rec 2','None','some text here','NEWLINE');
insert into MyTBI values('City','description for rec 3','None','some text here too','NEWLINE');
insert into MyTBI values('Zip','description for rec 4','text here','sdfla sdflsdhlsdfsk','NEWLINE');
insert into MyTBI values('Record 5','description',"Main - Main record\nAdministrative - Administrative phone number\nHR - HR phone number\nServices - Services line\nFax - Facsimile phone number\nOther",'Unchanged from Version 1','NEWLINE');
insert into MyTBI values('Grades Offered','description here',"IT - Infant/toddler\nPR - Preschool\nPK - Prekindergarten\nTK - Transitional Kindergarten\nKG - Kindergarten\n01 - First grade\n02 - Second grade\nUG - Ungraded\nOther",'Unchanged from Version 1','NEWLINE');
$