Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: Create a pivot table from CSV file
Top Forums Shell Programming and Scripting Create a pivot table from CSV file Post 302938417 by jiam912 on Monday 16th of March 2015 04:55:15 AM
Old 03-16-2015
Hi RudiC,

Thanks a lot for your help.. it works

I have modify little your script to get my desired output.

Code:
awk     'NR>1   {LN[$28]; HD[$1]; MX[$28,$1]+=$21; SUM[$28]+=$21; TOT[$1]+=$21; TOTAL+=$21}
         END    {               printf "%9s", "";      for (i in HD) printf "%9s", i;         printf "     Total\n";
                 for (j in LN) {printf "%9s",j;        for (i in HD) printf "%9s", MX[j,i]+0; printf "%9s\n", SUM[j]}
                                printf "%9s", "Total"; for (i in HD) printf "%9s", TOT[i] ;   printf "%9s\n", TOTAL;
                }
        ' FS="," $file | sort -k1n | awk 'NR==1{s=$0;next}1;END{print s}' > tmp1

sed -i '1iREPORT\' tmp1
sed -i '2i-----------------------------------------------------------------------------------------------------------------------------------' tmp1
sed -i '4i-----------------------------------------------------------------------------------------------------------------------------------' tmp1
sed -i '$i-----------------------------------------------------------------------------------------------------------------------------------' tmp1
sed -i '$a-----------------------------------------------------------------------------------------------------------------------------------' tmp1
cat tmp1

Then, I get this:

Code:
REPORT
-----------------------------------------------------------------------------------------------------------------------------------
                 4        5        6        7        8        9       10       11       12        1        2        3     Total
-----------------------------------------------------------------------------------------------------------------------------------
       60        0        0        0        0        0        0       12        0        1        0        0        0       13
       61        0        1        0        0        0        0       27        5        8        1        6       11       59
       62        0        0        1        0        0        0       21        5        0        0        1        4       32
-----------------------------------------------------------------------------------------------------------------------------------
    Total        0        1        1        0        0        0       60       10        9        1        7       15      104
-----------------------------------------------------------------------------------------------------------------------------------

Is there the chance to get this:

Code:
REPORT
-----------------------------------------------------------------------------------------------------------------------------------
                 1        2        3        4        5        6        7        8        9       10       11       12     Total  
-----------------------------------------------------------------------------------------------------------------------------------
       60        0        0        0        0        0        0        0        0        0       12        0        1       13   
       61        1        6       11        0        1        0        0        0        0       27        5        8       59   
       62        0        1        4        0        0        1        0        0        0       21        5        0       32   
-----------------------------------------------------------------------------------------------------------------------------------
    Total        1       7        15        0        1        1        0        0        0       60       10        9      104
-----------------------------------------------------------------------------------------------------------------------------------

Thanks
 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Pivot table

Hello everyone, I would like to use awk to parse a file with three columns in, like: monday 0 1 monday 1 1 monday 2 1 monday 3 1 monday 4 1 monday 5 1 tuesday 0 5 tuesday 1 1 tuesday 2 1 tuesday 3 1 tuesday 4 1 wednesday 0 1 monday 5 25 they represent the day the hour and the... (2 Replies)
Discussion started by: gio001
2 Replies

2. Shell Programming and Scripting

to create an output file as a table

Hi, I have four input files and would like to create an output file as a table. Please check the example below. File 1. 111111 222222 333333 444444 File 2. 555555 666666 777777 888888 File 3. aaaaa bbbbb ccccc ddddd (2 Replies)
Discussion started by: marcelus
2 Replies

3. Shell Programming and Scripting

Store table contents in csv file

I need to write a script to store the contents of a table in a csv file I'm using Toad, it's a Oracle database. (5 Replies)
Discussion started by: ladyAnne
5 Replies

4. UNIX for Dummies Questions & Answers

Storing data from a table into a csv file

Hi I need to write a bash script to take the data stored in 3 oracle tables .. and filter them and store the results in a csv file. It is an Oracle database Thank you (1 Reply)
Discussion started by: ladyAnne
1 Replies

5. Shell Programming and Scripting

Create Pivot table

I would like to use awk to parse a file with three columns in, like: Chennai,01,1 Chennai,07,1 Chennai,08,3 Chennai,09,6 Chennai,10,12 Chennai,11,19 Chennai,12,10 Chennai,13,12 Kerala,09,2 AP,10,1 AP,11,1 Delhi,13,1 Kerala,13,3 Chennai,00,3 Chennai,01,1 Chennai,02,1 Chennai,07,5 (3 Replies)
Discussion started by: boston_nilesh
3 Replies

6. Shell Programming and Scripting

Convert file in csv or table

Hi there, i have a file like that in attachment (PLEVA3_280711_SAP.txt), i would extract some basic information from it and report in a new file or table like this: i try to use bash and i extract the single object in this way (see attach scriptino.sh), but i receive a strange... (5 Replies)
Discussion started by: alen192
5 Replies

7. Shell Programming and Scripting

Update the table using values from a csv file

i want to run update query for oracle which is in up.sql taking values from a.csv. I have implemented shell script to do it. extn="perl" ls -1 | while read file do echo "$file,$extn" > a.csv done up.sql contains update file_list set filename=$1 where extn=$2; The code to update is... (2 Replies)
Discussion started by: millan
2 Replies

8. Shell Programming and Scripting

Compare 2 files of csv file and match column data and create a new csv file of them

Hi, I am newbie in shell script. I need your help to solve my problem. Firstly, I have 2 files of csv and i want to compare of the contents then the output will be written in a new csv file. File1: SourceFile,DateTimeOriginal /home/intannf/foto/IMG_0713.JPG,2015:02:17 11:14:07... (8 Replies)
Discussion started by: refrain
8 Replies

9. Shell Programming and Scripting

Awk- Pivot Table Averages

Hi everyone, Has anyone figured out yet how to do pivot table averages using AWK. I didn't see anything with regards to doing averages. For example, suppose you have the following table with various individuals and their scores in round1 and round2: SAMPLE SCORE1 SCORE2 British ... (6 Replies)
Discussion started by: Geneanalyst
6 Replies

10. UNIX for Beginners Questions & Answers

1. This will insert the records into db table by reading from ta csv file

I have this code with me but the condition is If any of the mandatory columns are null then entire file will be rejected. LOAD DATA infile ' ' #specifies the name of a datafile containing data that you want to load BADFILE ' ' #specifies the name of... (1 Reply)
Discussion started by: raka123
1 Replies
All times are GMT -4. The time now is 02:33 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy