Insert blanks for missing fields and reformat to csv

Shell Programming and Scripting
1

I am trying to process inventory addition files for insertion into a MySQL database. The format convention is book UIEE. If the field for the add file has no data, the field is NOT included in the upload file, so I need to add a blank/empty for any missing fields. The I need to create a csv or tsv file for the actual insertion. I have the main conversion part down, but I am having issues with inserting the blanks. I need to check if it is present first.

Here is the code I have so far:

#!/usr/bin/env bash
#
# This is a script to process incoming add files
#
# Find the file
file_name=`find . -name "*part*"`;
# Change file permissions
chmod 744 $file_name;
# Convert to Unix format and remove ^M char (change to fromdos on server)
`dos2unix $file_name`;
# Remove the top 5 lines if User is on the first, escape existing ";" 
# and insert a temp char set on blank line at end of each record
if grep "User" $file_name; then
    sed '1,5d' $file_name | sed -e 's/;/\\;/g' -e 's/"/\\"/g' -e 's/^$/{}/g' > trimAdd;
else echo " ";
fi
# Swap a ";" for Win carriage return and swap a newline at end of records for the temp char
tr '\012' ';' < trimAdd | sed 's/{};/\n/g' > swapAdd;
# check record for missing fields and insert blanks/NULLS where necessary

Here is a sample of the file I am processing (routinely contains 1200+ lines and yes the last line is empty:

User
BOOKS
2009-08-06
14:16:52

UR|007815
TI|Vintage Motorsport : 1995 Jan/Feb
PR|17.50
BD|Soft Cover
NT| 82 pgs; magazine format
CO|1
SD|2009-08-06 14:04:20
CA|Transportation
MT|Transportation
DP|1995
JK|No Jacket
XA|4
XB|1
XC|BO
XD|S

UR|007816
TI|Vintage Motorsport : 1992 Nov/Dec
PR|17.50
BD|Soft Cover
NT| 82 pgs; magazine format
CO|1
SD|2009-08-06 14:04:20
CA|Transportation
MT|Transportation
DP|1992
JK|No Jacket
XA|4
XB|1
XC|BO
XD|S

UR|007817
TI|Vintage Motorsport : 1995 Mar/Apr
PR|17.50
BD|Soft Cover
NT| 82 pgs; magazine format
CO|1
SD|2009-08-06 14:04:20
CA|Transportation
MT|Transportation
DP|1995
JK|No Jacket
XA|4
XB|1
XC|BO
XD|S

UR|007818
TI|Vintage Motorsport : 1993 Nov/Dec
PR|17.50
BD|Soft Cover
NT| 82 pgs; magazine format
CO|1
SD|2009-08-06 14:04:20
CA|Transportation
MT|Transportation
DP|1993
JK|No Jacket
XA|4
XB|1
XC|BO
XD|S

UR|007819
TI|Vintage Motorsport : 1995 Jul/Aug
PR|17.50
BD|Soft Cover
NT| 82 pgs; magazine format
CO|1
SD|2009-08-06 14:04:20
CA|Transportation
MT|Transportation
DP|1995
JK|No Jacket
XA|4
XB|1
XC|BO
XD|S

Any help, even it is to point into a better direction is greatly appreciated. I am not familiar with awk, but I do know sed.

---------- Post updated 08-17-09 at 05:56 PM ---------- Previous update was 08-16-09 at 10:34 PM ----------

Here is another failed attempt, but perhaps it will give a better idea of what I am trying to accomplish:

#!/usr/bin/env bash
# Find the file
file_name=`find . -name "*part*"`;
# Change file permissions
chmod 744 $file_name;
# Convert to Unix format and remove ^M char
`dos2unix $file_name`;
# Remove the top 5 lines if User is on the first, escape existing ";" 
# and insert a temp char set on blank line at end of each record
if grep "User" $file_name; then
    sed '1,5d' $file_name | sed -e 's/,/\\,/g' -e 's/"/\\"/g' -e 's/^$/{}/g' > trimAdd;
else echo " ";
fi
# Swap a ";" for Win carriage return and swap a newline at end of records for the temp char
tr '\012' ',' < trimAdd | sed 's/{},/\n/g' > swapAdd;
sed 's/.\*/UR\|.\*;TI\|.\*;PR\|.\*;AA\|.\*;BN\|.\*;BD\|.\*;NT\|.\*;CO\|.\*;LO\|.\*;PC\|.\*;SD\|.\*;CN\|.\*;CA\|.\*;MT\|.\*;PP\|.\*;DP\|.\*;KE\|.\*;JK\|.\*;SG\|.\*;S1\|.\*;ED\|.\*;PU\|.\*;XA\|.\*;XB\|.\*;XC\|.\*;XD\|.\*;/g' swapAdd > finAdd;

Again, any help, even if to only point out errors, might help me get past this sticking point.

Thanks in advance!

2

it would help if you provided a desired output given a sample you provided with the detailed explanation.

3

Thanks for the reply vgersh99, here is what I am looking for as the output:

UR|007815,TI|Vintage Motorsport : 1995 Jan/Feb,PR|17.50,,,BD|Soft Cover,NT| 82 pgs; magazine format,CO|1,,,SD|2009-08-06 14:04:20,,CA|Transportation,MT|Transportation,,DP|1995,,JK|No Jacket,,,,,XA|4,XB|1,XC|BO,XD|S
UR|007816,TI|Vintage Motorsport : 1992 Nov/Dec,PR|17.50,,,BD|Soft Cover,NT| 82 pgs; magazine format,CO|1,,,SD|2009-08-06 14:04:20,,CA|Transportation,MT|Transportation,,DP|1992,,JK|No Jacket,,,,,XA|4,XB|1,XC|BO,XD|S
UR|007817,TI|Vintage Motorsport : 1995 Mar/Apr,PR|17.50,,,BD|Soft Cover,NT| 82 pgs; magazine format,CO|1,,,SD|2009-08-06 14:04:20,,CA|Transportation,MT|Transportation,,DP|1995,,JK|No Jacket,,,,,XA|4,XB|1,XC|BO,XD|S
UR|007818,TI|Vintage Motorsport : 1993 Nov/Dec,PR|17.50,,,BD|Soft Cover,NT| 82 pgs; magazine format,CO|1,,,SD|2009-08-06 14:04:20,,CA|Transportation,MT|Transportation,,DP|1993,,JK|No Jacket,,,,,XA|4,XB|1,XC|BO,XD|S
UR|007819,TI|Vintage Motorsport : 1995 Jul/Aug,PR|17.50,,,BD|Soft Cover,NT| 82 pgs; magazine format,CO|1,,,SD|2009-08-06 14:04:20,,CA|Transportation,MT|Transportation,,DP|1995,,JK|No Jacket,,,,,XA|4,XB|1,XC|BO,XD|S
4

Try this .. it's an awk program....

BEGIN{
# set the record seperator to |
FS="|"
}

function clear_data()
{
# init data array to what to put if field has no data
        value["UR"] = ","
        value["TI"] = ","
        value["PR"] = ","
        value["BD"] = ","
        value["NT"] = ","
        value["CO"] = ","
        value["SD"] = ","
        value["CA"] = ","
        value["MT"] = ","
        value["DP"] = ","
        value["JK"] = ","
        value["XA"] = ","
        value["XB"] = ","
        value["XC"] = ","
        value["XD"] = " "
}


# skip the first five records
(FNR < 6) {
        getline
        getline
        getline
        getline
        getline
        clear_data()
}

# process record
($1 == "UR") {
# clear the record data

        clear_data()
#consume the record values
        while ($0 != "") {
                value[$1]=($1 != "XD" ? ($0 ",") : $0 )
                if( getline <= 0)
                        exit
        }
# print the new output
        printf("%s%s%s,,%s%s,,%s,%s,%s%s,,,%s%s%s%s%s\n",value["UR"],value["TI"],value["PR"],value["BD"], value["CO"],value["SD"], value["CA"],value["MT"], value["DP"], value["JK"], value["XA"], value["XB"], value["XC"], value["XD"])
}
END {
        print
}
5

Will this work for you?

grep '|' file_name | tr '\n' ',' | sed -e 's/,\(UR|[0-9]\{6\},TI\)/\n\1/g; s/,$//'

Assumed:
'UR|' will always be followed by 6 numbers and a comma.
If not reduce it as required.
You have not mentioned what part is constant.

6

jp2542a: Thanks, I will give that a try this morning and see what results I get. I'm not familiar with awk, but plan to learn it so this may be an opportunity.

edidataguy: Thanks for pointing that out! The only constant is that all will begin with UR and all will end with XD and then an empty (after removal of ^M) line. This is obviously generated on a Win machine, buy I am working entirely on Linux desktop and a Linux server.

That said, it may help if I state that I will then use this to insert and delete records in a MySQL database and run as a cron job. Thanks for the help!

7 
nawk '
  BEGIN {
    FS=RS=""
    OFS=","
  }
  FNR>1 {$3=$3 OFS OFS; $6=$6 OFS OFS;$7=$7 OFS;$9=$9 OFS;$10=$10 OFS;$11=$11 OFS OFS OFS OFS; print}' myFile
8

One way of doing it with Perl:

perl -lne 'chomp; /\|/ && push @x,$_;
           if(/^$/ && defined @x){print join ",",@x; @x=()}
           END{print join ",",@x}' file

tyler_durden

9

Thanks everyone! jp's does exactly what I want!

Thanks again!

10 
grep '|' reorder.txt | tr '\n' ',' | sed -e 's/,\(UR|\)/\n\1/g; s/,$//'

There are a few pit falls in this. Assumed there will be no ",UR|" anywhere else in the data.