Please help me organise this csv file :)

gazj · 2009-05-28 21:48:26

I am creating a small bash program that keeps product details in a csv file, It's being kept simple, well because I can only do simple. I need some help reading the csv file to get the search output I require, I have got a simple output as this

Product search results for sec

_________________________________________________________________________________________
ID      |Cost   |Margin |Retail |In VAT |Category       |Description    |Manufacturer
________|_______|_______|_______|_______|_______________|_______________|_______________
S009    |2.00   |25     |2.50   |2.87   |2nd fix        |Double Socket  |Sector
S901    |1.00   |25     |1.25   |1.43   |2nd fix        |Single Socket  |Sector

1 - Main menu | 2 - New search | 3 - Print results

Here is my bash for the results function

function searchproduct {
    clear
    echo Search product
    echo ""
    read -p "Enter product id / description / type / manufacturer: " PRS

    soutput=`cat $DATFILE | grep -i $PRS | cut -d ',' -f 1-8 | sed $'s/,/\t|/g'`

    
    echo "Product search results for $PRS\n" > $LASTSEARCH
    echo "_________________________________________________________________________________________" >> $LASTSEARCH
    echo "ID    |Cost    |Margin    |Retail    |In VAT    |Category    |Description    |Manufacturer    " >> $LASTSEARCH
    echo "________|_______|_______|_______|_______|_______________|_______________|_______________" >> $LASTSEARCH
        echo "$soutput" >> $LASTSEARCH
        clear
    echo -e "`cat $LASTSEARCH`"
    echo ""
    read -p "1 - Main menu | 2 - New search | 3 - Print results " x
        case $x in
        1)
        mainmenu
        ;;
        
        2)
        searchproduct
        ;;
        
        3)
        printsearch
        ;;
    esac
}

Here is an example $DATFILE (csv file) with the data

S009,2.00,25,2.50,2.87,2nd fix,Double Socket,Sector,gary,1243545491
S901,1.00,25,1.25,1.43,2nd fix,Single Socket,Sector,gary,1243545541

I am trying to accomplish

Someway of keeping the table straight if the data is less than two bash tabs in fields category Description and Manufacturer. I.e If Double socket is just socket i need bash to print two \t's

If possible use the unix time stamp in the data line, compare it with the current time, if the data is older than 6 months then print the line in red.

I know there are some real clever peeps in the Arch Forums who can help me on my first serious (well for me) bit of bash scripting. I really have tried, god have I tried, for hours !!

Thanks Guys

Procyon · 2009-05-28 22:13:20

What you need is printf. The documentation is in man 3 printf, but it's a command too.

$ printf "|%-10.10s|%-10.10s|\n" item1thisisaverylongdescription item2
|item1thisi|item2     |

For the date you could then use:

if [ $(( $(date +%s) - $date )) -gt 15778463 ]; then printf "\e[31m$date\e[m"; else printf "$date"; fi

BTW printf with a format string doesn't seem to interpret the color codes.

gazj · 2009-05-28 23:11:11

Thanks now I am getting somewhere, can printf divide it's input up on commas rather than whitespace? The man pages are confusing the hell out of me.

Thanks for your help

gnud · 2009-05-29 22:19:22

Have a look at man 1 printf - thats the docs for the printf command. man 1p printf is the docs for the posix standard printf command. man 3 printf is the docs for the C library function printf.

brisbin33 · 2009-05-29 23:30:07

i have to say it... `cat $file | grep -i $word` should just be `grep -i $word $file`

now, i tried really really hard to get this all done in one awk line... but i can't figure out how to pass $PRS as the awk searchterm or how to make it case insensitive, so this'll have to do:

search() {
  file=$HOME/sample
  PRS="1243545491"

  grep -i $PRS $file | awk -F ',' '{printf "|%-10.10s|%-10.10s|\n", $1, $2}'
}

> cat sample 
S009,2.00,25,2.50,2.87,2nd fix,Double Socket,Sector,gary,1243545491
S901,1.00,25,1.25,1.43,2nd fix,Single Socket,Sector,gary,1243545541
> testing
|S009      |2.00      |

and you should be able to go from there

as you can see, awk uses the same printf syntax, you'll just have to expand it for $3-$8 and the -F defines the seperator as ',' which is desired. i'm new to awk so i'm sure someone can get it into one line using some BEGIN/END magic... that is -for now- beyond me though. good luck.

/edit: for the record

awk -v term="$PRS" -F ',' '/term/ {printf...  }' $file

(or any rearrangment thereof) does _not_ work...

Last edited by brisbin33 (2009-05-29 23:44:52)

Procyon · 2009-05-30 10:24:34

-v seems to need reassignment in the BEGIN block, but I'm not sure how to get a variable to work in a regex. You can always switch from single quote to double quote and let bash insert it. In the BEGIN block you should also add IGNORECASE=1

brisbin33 · 2009-05-30 15:35:28

Procyon wrote:

-v seems to need reassignment in the BEGIN block, but I'm not sure how to get a variable to work in a regex. You can always switch from single quote to double quote and let bash insert it. In the BEGIN block you should also add IGNORECASE=1

i did try the double quotes just like you mention... but then $1, $2 are expanded by bash to the scripts positional arguments seems i can't have it both ways, i could be doing something wrong there though... i'm happy enough to quit with the grep | awk line as is.

thanks for the tips

Procyon · 2009-05-30 16:03:36

I mean switching quoting style like this: awk 'BEGIN {IGNORECASE=1} '"/$PRS/"' {print $0}'

gazj · 2009-05-30 16:55:51

Thenks guys, the awk line is working an absoloute treat I really appreciate all you efforts, If i could just borrow your expertise a little more for highlighting lines older than 6 months is some way that would be fantastic.

Thank you all so much

Procyon · 2009-05-30 17:52:49

I mentioned something about that in the first reply.

gazj · 2009-05-31 16:08:30

I know you did, but I am still struggling to find a way of reading every line a colring it accordingly :S

code as it stands at the moment. Some previous attempts commented

function searchproduct {
    clear
    echo Search product
    echo ""
    read -p "Enter product id / description / type / manufacturer: " PRS

    #grep -i $PRS $DATFILE > $TEMPSEARCH
    #
    #if [ -f $TEMPSEARCH2 ]; then rm $TEMPSEARCH2; touch $TEMPSEARCH2; fi
    #
    #x=0
    #Ins=`wc =l $TEMPSEARCH`
    #y=`expr "$Ins":'\([0-9]*\)'
    #while ["$x" -lt "$y"]
    #do
    #    date=`echo $line | cut -d ' ' -f 10`
    #    if [ $(( $(date +%s) - $date )) -gt 15778463 ]; then fdate="\e[31m$date\e[m"; else fdate="$date"; fi
    #    newline=`sed -e s/$date/$fdate/ $line`
    #    cat $newline >> $TEMPSEARCH2
    #    done
    
    #cat $TEMPSEARCH2 > $TEMPSEARCH
    grep -i $PRS $DATFILE | awk -F ',' '{printf "|%-7.7s|%-7.7s|%-7.7s|%-7.7s|%-7.7s|%-15.15s|%-15.15s|%-15.15s|\n", $1, $2, $3, $4, $5, $6, $7, $8}' >> $TEMPSEARCH
    
    echo "Product search results for $PRS\n" > $LASTSEARCH
    echo "_________________________________________________________________________________________" >> $LASTSEARCH
    echo "|ID    |Cost    |Margin    |Retail    |In VAT    |Category    |Description    |Manufacturer    |" >> $LASTSEARCH
    echo "|_______|_______|_______|_______|_______|_______________|_______________|_______________|" >> $LASTSEARCH

        echo -e "`cat $TEMPSEARCH`" >> $LASTSEARCH
        clear
    echo -e "`cat $LASTSEARCH`"
    echo ""
    read -p "1 - Main menu | 2 - New search | 3 - Print results " x

byte · 2009-05-31 16:49:33

I must admit not having read the whole thread, but just regarding the formatting: 'man column'

Procyon · 2009-05-31 17:27:56

@gazj: I understand it a bit better now, you just have to put something before and after the printf command that changes the color.

For example:
echo 500 | awk '{"date +%s" | getline curtime; if ( curtime - 15778463 > $1 ) {printf "\x1b[31m"}; printf "something"; printf "\x1b[m\n"}'

Arch Linux

#1 2009-05-28 21:48:26

Please help me organise this csv file :)

#2 2009-05-28 22:13:20

Re: Please help me organise this csv file :)

#3 2009-05-28 23:11:11

Re: Please help me organise this csv file :)

#4 2009-05-29 22:19:22

Re: Please help me organise this csv file :)

#5 2009-05-29 23:30:07

Re: Please help me organise this csv file :)

#6 2009-05-30 10:24:34

Re: Please help me organise this csv file :)

#7 2009-05-30 15:35:28

Re: Please help me organise this csv file :)

#8 2009-05-30 16:03:36

Re: Please help me organise this csv file :)

#9 2009-05-30 16:55:51

Re: Please help me organise this csv file :)

#10 2009-05-30 17:52:49

Re: Please help me organise this csv file :)

#11 2009-05-31 16:08:30

Re: Please help me organise this csv file :)

#12 2009-05-31 16:49:33

Re: Please help me organise this csv file :)

#13 2009-05-31 17:27:56

Re: Please help me organise this csv file :)

Board footer