current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems.. It's 100% free, no registration required.
up vote 2 down vote favorite

I have dumped the database table data into a flat file and below is how the data looks like : (Kindly copy from below 

;metier_code              ;
;-------------------------;

(0 rows affected)
;CRDS_Ptf_No;          ;                                ;                         ;          ;                                ; ;                         ;                         ;               ;               ;               ;                         ;                         ;     ;Status;
;-----------;----------;--------------------------------;-------------------------;----------;--------------------------------;-;-------------------------;-------------------------;---------------;---------------;---------------;-------------------------;-------------------------;-----;------;
;       NULL;ABCD      ;ABHJARS                         ;                         ;ABCD      ;ABCD                            ;Y;                         ;                         ;               ;               ;               ;                         ;                         ;     ;A     ;
;       1234;XEU-ANKD  ;XEU-AJKD                        ;                         ;ABCD      ;ABCD                            ;Y;                         ;                         ;               ;               ;               ;                         ;                         ;     ;A     ;
.
.
;      11745;ANJLDMAOKD;AMKDJ AN DJ JAHF AS CPFVH ACCR  ;NONE                     ;AN DJ JAHA;AN DJ JAHA                      ;Y;NO ANKIO GAP             ;YES AMK SCF              ;               ;               ;               ;                         ;                         ;     ;I     ;
;      11744;AMKDIONSKH;AMKDJ AN DJ JAHF AS CPFVH MTM   ;NONE                     ;AN DJ JAHA;AN DJ JAHA                      ;Y;NO ANKIO GAP             ;YES AMK SCF              ;               ;               ;               ;                         ;                         ;     ;I     ;

(5436 rows affected)
(return status = 0)

Return parameters:

;           ;
;-----------;
;       5436;


(1 row affected)
;       ;           ;
;-------;-----------;
;grepkey;       5436;

(1 row affected)

want to convert the above as below format:

Row should contain the seq no (Prefixed) Need to remove columns names and the blank spaces present in the original file at begining and ending.

BELOW IS THE FORMAT OF DATA THAT I AM GETTING BY USING THE SUGGESTED CODE:

awk -F ';' '/^;-----------;/ {start=1;next;};  start==0 {next;}; {gsub(" +",""); print NR "" $0;}' temp_file > test

FORMAT after executing above script :

7;NULL;ABCD;ABHJARS;;ABCD;ABCD;Y;;;;;;;;;A;
8;NULL;XEU-ANKD;XEU-AJKD;;ABCD;ABCD;Y;;;;;;;;;A;
.
.
5443;11744;AMKDIONSKH;AMKDJ AN DJ JAHF AS CPFVH MTM;;QWERDF;QWERDF;Y;;;;;;;;;A;
5444
5445(5436rowsaffected)
5446(returnstatus=0)
5447
5448Returnparameters:
5449
5450;;
5452;5436;
5453
5454
5455(1rowaffected)
5456;;;
5457;-------;-----------;
5458;grepkey;5436;
5459
5460(1rowaffected)

Above : the prefix row number is not coming in sequence(Incrementing by using the preceeding lines that is not the actual data). Initial file was containing additional info in flat file like column name @ begining , at the end of file few additional details that i wanted like count of records etc

I want the data in below format (Which shall have prefix row number and shall include only rows of table , not the additional preceeding and exceeding data) 

1;NULL;ABCD;ABHJARS;;ABCD;ABCD;Y;;;;;;;;;A;
2;NULL;XEU-ANKD;XEU-AJKD;;ABCD;ABCD;Y;;;;;;;;;A;
3;NULL;SWAPOLEIL;SWAPOLEIL;;QWERDF;QWERDF;Y;;;;;;;;;A;
.
.
5436;11744;AMKDIONSKH;AMKDJ AN DJ JAHF AS CPFVH MTM;;QWERDF;QWERDF;Y;;;;;;;;;A;

5436 - is the number of rows present in the table from where i am fetching the data.

Thanks in advance!

(Tried the other suggested solutions as well . However, dint got the desired result)

share|improve this question
    
Rajiv, welcome to U&L. You're kind of making a mess of the question & answer format that the site is suppose to adhere to. Can you please move the contents of the answer below into your question and mark those 2 answers as deleted? Please do this! –  slm yesterday
    
If that's MySQL, then there is a batch option you can use to get machine-readable output. -B if I remember correctly. –  derobert yesterday
add comment

3 Answers

up vote 4 down vote 
awk -F ' *; *' \
  '{printf "%d;",NR; for(i=2;i<=NF;i++) printf "%s;",$i; print "";}' file

-F sets the field separator ro the regex *; * (i.e. "semicolon with any number (uncluding zero) of spaces on both sides").

Or, simpler:

awk -F ' *; *' -v OFS=";" '{$1=NR; $(NF+1)=""; NF--; print}' file

$(NF+1)=""; NF--; just enforces the recomputing of $0 but now with the changed OFS as field separator.

Even simpler:

awk -F ';' '{gsub(" +",""); print NR "" $0;}' file

If awk shall "process" (i.e. ignore) the leading lines, too:

awk -F ';' '/^;-----------;/ {start=1;next;}; 
  start==0 {next;}; /^[^;]|^$/ { exit;}; { line_nr++; gsub(" +",""); print line_nr "" $0;}' file

edit

I didn't pay enough attention and forgot to adapt the line number in the last example. As read lines are not the same as written lines any more (plain) NR cannot be used any more.

edit 2

Addition of /^[^;]|^$/ { exit;}; stops the processing if a line occurs which does not start with ;.

share|improve this answer
add comment
up vote 3 down vote

Using sed and awk

sed -e '/^; *NULL/ s/ *//g' file | sed -n '/^;NULL.*/,$p' | awk '{print NR$0}'

  • sed -e '/^; *NULL/ s/ *//g' file - Removes the spaces in the lines starting with ; NLL.

  • sed -n '/^;NULL.*/,$p' - Removes all the lines before the line starting with ; NULL.

  • awk '{print NR$0}' - prints all the lines preceded by it's NR value.

share|improve this answer
    
sed '/NULL/!d;s/ //g;=' | sed 'N;s/\n/:/' does all the stuff in your thing, and you only need two sed's there to remove the \newline generated by sed's = line counter needs removing. It would be simpler - and faster - still to use only one and pipe it to grep -n . or nl. –  mikeserv 20 hours ago
    
In fact - you'd have to do that or a third sed I guess for the same reason I use two greps any removed lines would still count in the output so you'd start at line 7 or something. –  mikeserv 20 hours ago
add comment
up vote 0 down vote

I think even sed and awk might be a little overkill here.

grep '^;.*NULL' <<\DATA | tr -d '[:blank:]' | grep -n .

;metier_code              ;
;-------------------------;

(0 rows affected)
;CRDS_Ptf_No;          ;                                ;                         ;          ;
             ; ;                         ;                         ;               ;               ;
  ;                         ;                         ;     ;Status;
;-----------;----------;--------------------------------;-------------------------;----------;--------------------------------;-;-------------------------;-------------------------;---------------;---------------;---------------;-------------------------;-------------------------;-----;------;
;       NULL;ABCD      ;ABCD                            ;                         ;ABCS      ;ABCS
             ;Y;                         ;                         ;               ;               ;
  ;                         ;                         ;     ;A     ;
;       NULL;PKG-ABCP  ;UEX-SWAP                        ;                         ;ABCS      ;ABCS
             ;Y;                         ;                         ;               ;               ;
  ;                         ;                         ;     ;A     ;
;       NULL;SWAPOLEIL ;SWAPOLEIL                       ;                         ;QWERDF    ;QWERDF
             ;Y;                         ;                         ;               ;               ;
  ;                         ;                         ;     ;A     ;
DATA

OUTPUT

1:;NULL;ABCD;ABCD;;ABCS;ABCS;Y;;;;;;;;;A;
2:;NULL;PKG-ABCP;UEX-SWAP;;ABCS;ABCS;Y;;;;;;;;;A;
3:;NULL;SWAPOLEIL;SWAPOLEIL;;QWERDF;QWERDF;Y;;;;;;;;;A;
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.