0

I have a bash script that takes a bunch of command line arguments. The only one that matters in this context is the first, $1, which is a text file.

The header is very long, below are examples of some of the fields.

COL0___LINE_NUMBER
COL1_AFF_ID
COL2_FULL_NAME
COL3_ADDRESS
BDID
BEST_STATE
COL48_LATITUDE   
COL49_LONGITUDE

I need to alter the header row, which I am able to do using the below code. This does accomplish what I want, however any stylistic changes etc. that preserve the variable as in the output below are welcome considering this is my first time bash scripting.

columns=`cat $1 | head -1 |sed 's/-/_/g' |  sed 's/ /_/g' |
    sed 's/COL[0-9]\+_BDID/DROP_BDID/g' | sed 's/COL[0-9]\+_//g' |
    tr '\t' '\n' | tr  "[:lower:]" "[:upper:]"`

Note: The tabs to newlines is formatted as such purely as an attempt at aesthetics when the columns header is echoed. This is both for readability for me and the users of the script for whom the vertica create table statement is echoed.

Anyway, now I want to make the columns variable the header row of my text file so that I can work with the new version inside the script. So, I would like the complete original text file without it's original header row, and with the one I created so that the following, for example, are referring to the edited version of my file,

col_arr=($columns)
cut_cols = ""

for i in ${!col_arr[@]}; do
    if [[ "${col_arr[$i]}" =~ ^(__LINE_NUMBER|CONFIDENCE|DROP_BDID|LINE_NUMBER|ZIP9|ZIP9|ZIP9MATCH)$ ]]; then
            echo "$i"
            #haven't written yet, but this will add to cut_cols so that 
            #I can remove the above listed columns in the text file 
            #based on their index.
    fi
done
/opt/vertica/bin/vsql -U ${4} -w ${5} -h ${database} \
    -c "copy $schema.$table from STDIN delimiter E'\t' direct no escape;"
  • We can get a better answer quicker with some of the input and output data, especially the input header line. Good question, thanks! – RobertL Nov 6 '15 at 22:11
  • You are taking a needlessly complex approach here. Unfortunately, I can't help you simplify it unless you edit your question and show us i) your input and ii) your desired output. For a start, consider using columns=head -n1 $1 | sed 's/[- ]/_/g' | .... – terdon Nov 6 '15 at 22:36
  • Thanks for the update. Not sure what you mean by "so that the following, for example, are referring to the edited version of my file". I think you're saying that at some point you will transform the data in $1, adding/removing columns and so forth, and then send the header for the new format and the matching transformed file to vsql? So in the end, to match the new contents of the file, the header will contain a different number and/or different strings than you started with in the first columns=? – RobertL Nov 8 '15 at 21:04
2

We can combine all of the commands from the original columns= shell pipeline into one sed script. This sed script modifies only the first line of the input and then exits. The following does exactly the same thing as the columns= in the original question:

columns=$(
    sed '               
        1 {                                   # execute block on line 1
            s/-/_/g     
            s/ /_/g     
            s/COL[0-9]\+_BDID/DROP_BDID/g
            s/COL[0-9]\+_//g
            s/\t/\n/g   
            y/abcdefghijklmnopqrstuv/ABCDEFGHIJKLMNOPQRSTUV/
            q                                 # quit after line 1
        }
    ' "$1"
)

# . . .

I prefer the multi-line format also for readability. Even though the original statement was on one line, it was much less efficient, and in my opinion, more difficult to read. yomd

Now you've got the headers from the input file (arg 1), stored in the variable columns separated by newlines. You can iterate over the strings in $columns with a for loop, this will separate the the column names in cut_cols by newlines:

cut_cols="$(
    for col in $columns
    do
        case $col in
        (*__LINE_NUMBER*|*CONFIDENCE*|*DROP_BDID*|*LINE_NUMBER*|*ZIP9*|*ZIP9MATCH*)
                echo "$col"
                ;;
        esac
    done
)"

Depending on your preferences, this does the same thing:

cut_cols=
for col in $columns
do
    case $col in
        (*__LINE_NUMBER*|*CONFIDENCE*|*DROP_BDID*|*LINE_NUMBER*|*ZIP9*|*ZIP9MATCH*)
            cut_cols="$cut_cols $col"
            ;;
    esac
done
cut_cols=$(echo "$cut_cols" | sed 's/^ *//; s/ /\n/g')

I didn't test your array loop for cut_cols because I don't use shell arrays. The above method of iterating over $columns is the more universal and traditional method. Arrays are an extension, not available in every shell.

After you have assigned to cut_cols, you can iterate over it the same as $columns.

To send a new header with the original file data, print the new header, and then print all but the first line of the original file. Do this in a command group (between { and }) so you can redirect the output of both commands together as if they were one program.

The following produces the complete original text file without it's original header row, and with the one you created, and sends it to the stdin of vsql:

# . . .

{                                   # start command group

    echo "$columns" | tr '\n' '\t'; # print with tabs instead of newlines
    echo                            # add newline record separator
    sed 1d "$1"                     # print all but 1st line of "$1"

} |                                 # pipe as one file to vsql

/opt/vertica/bin/vsql -U ${4} -w ${5} -h ${database} \
    -c "copy $schema.$table from STDIN delimiter E'\t' direct no escape;"
  • This is the very beginning and very end of a longer bash script. The newlines do serve a purpose, as does the assignation of the columns to a variable. I will edit the question soon for more clarity. – Audrey Nov 6 '15 at 23:04
  • I've updated this answer based on your comment. – RobertL Nov 7 '15 at 16:39
  • About to try this. Will let you know if it works out. I've also edited my question. Please take a look and let me know if this is sufficiently clear for any other ideas you may want to offer. – Audrey Nov 8 '15 at 18:27
  • I think the information I don't have is how you'll transform the file. Will that be done with script commands or database commands? – RobertL Nov 8 '15 at 21:06
  • @Audrey I fixed two main problems. First, the pattern matching in the case statement was incorrect. Asterisks * are needed to match the other parts of the header fields. The other problems I fixed are mostly the result of using the newline for the field separator. In shell programming, the convention is that newlines separate records and non-newline characters separate fields. Not that your way is wrong, but not usually done that way. In general, I'd suggest keeping the fields in the variables space or tab separated, and convert them to newlines only when necessary for output, etc. – RobertL Nov 10 '15 at 20:37
1

I really don't understand a lot of this question (especially the cause for editing only the column header row in a file - what happens to all of the rows it used to identify afterward?), but this part makes sense:

        #haven't written yet, but this will add to cut_cols so that 
        #I can remove the above listed columns in the text file 
        #based on their index.

That I understand. Here are a few sed tricks for extracting specific fields from a file:

printf 'one    two three' |
sed    's|[^ ]*||5'

one     three

That looks weird, right? Here sed removes the 5th possible sequence of not-space characters, which works out to count any length sequence of not-space chars as a single field - to include a zero-length sequence. And so one is the first field, next is the null-string between the following-space and the space that follows it, and ditto for fields 3 and four, and the fifth field is 4 spaces in. Pretty gnarly, I know.

printf 'one    two three' |
sed    's|[^ ][^ ]*||2'

one     three

There I include a definite match for at least one not-space char per field, and so sed behaves more like some other programs might. The handy thing about regular expressions, though, and especially when applied to edits, is that you can very specifically tailor the behavior of your output, and handling null-strings is all just a part of that.

  • 1
    I think your confusion about why I would want to edit the column header may be answered in my response below. I wouldn't want my regular expressions to catch anything inside the text file other than the header row and I need the header as a variable to formulate my create statement. Thanks for your response! – Audrey Nov 10 '15 at 20:48
0

Ok, so I figured this out. The question, which confused some, was how do I take my header row, edit out some eccentricities in field names, and prepend back onto the file.

What I ended up doing:

  1. Edit header row and assign to variable.
  2. Keep the header row and remaining text file separate the whole time.

This solution is largely due to the nature of the script as a loader tool for a Vertica table. As long as the same fields are cut from the header row and the file, it does not matter if they are ever one file again. I had mostly wanted to reunite the edited header with its original content so that I could save a text file with the correct header row in my directory and so that I did not have to cut the header row and content separately. However, I did end up cutting them separately like so,

col_arr=($columns)
cut_cols=""

for i in ${!col_arr[@]}; do
    if ! [[ "${col_arr[$i]}" =~ ^(__LINE_NUMBER|CONFIDENCE|DROP_BDID|LINE_NUMBER|ZIP9|ZIP9|ZIP9MATCH)$ ]]; then
            ind=$(($i+1))
            cut_cols="$cut_cols,$ind"
    fi
done

cut_cols=$(echo $cut_cols | sed s/^,//g)
columns=$(echo "$columns" | cut -f "$cut_cols")
cut -f ${cut_cols} ${1}>member_temp.txt
sed -i 1d member_temp.txt

My decision to maintain a variable for columns comes from the use of this script as a loader. Creating a table in Vertica requires a statement that identifies each field and its data type. I do this by running the columns variable (header row) through some if statements that populate a variable with fields and datatypes in a string to be used in the syntax for a create statement.

I then just loaded member_temp.txt to the previously created table. It does not matter that there's no header row because I would just strip it off anyway, as I don't want it stored in my table.

cat member_temp.txt | /opt/vertica/bin/vsql -U ${4} -w ${5} -h ${database} \
-c "copy $schema.$table from STDIN delimiter E'\t' direct no escape;"

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.