Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

awk script to remove spaces - examples don't show up correctly

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting awk script to remove spaces - examples don't show up correctly
# 1  
Old 12-22-2008
Question awk script to remove spaces - examples don't show up correctly
I have the following data from a manual database dump. I need to format the columns so that I can import them into an excel spread sheet. So far I have been able to get past the hurdles with vi and grep. Now I have one last issue that I can't get past. Here is an example of the data.

Here is what it looks like. The third column is a part description with spaces and other characters, but none one is a consistent delimiter.
-----------------------------
Code:
2 910-0158                               ENCLOSURE,TOP,SMK,10-YR                  B   4052      1.00000 EA  U J M  M  No
2 910-0159                               ENCLOSURE,BOTTOM,SMK,10-YR               E   5404      1.00000 EA  U J M  M  No
2 910-0160                               TRIMPLATE,SMK,DC                         D   4735      1.00000 EA  U J M  M  No
2 910-0155                               ROTATE ACTIVATE,SMK,10-YR                C   4732      1.00000 EA  U J M  M  No
2 910-0156                               ROTATE DEACTIVATE,SMK,10-YR              B   4733      1.00000 EA  U J M  M  No
2 910-0157                               SLIDER,ACTIVATE-DEACTIVATE,SMK,10-YR     D   4734      1.00000 EA  U J M  M  No
2 910-0171                               SUPPORT PLATE,SMK,10-YR                  B   4737      1.00000 EA  U J M  M  No
2 910-0149                               BUTTON,PUSH TO TEST,SMK                  D   3828      1.00000 EA  U J M  M  No
2 910-0150                               BUTTON,HUSH,SMK                          E   4714      1.00000 EA  U J M  M  No
2 910-0151                               LIGHT PIPE,SMK,NGS                       A             1.00000 EA  U J M  M  No
2 940-0084                               CLICHE,KIDDE LOGO ONLY,5mm,"K"           A             0.00000 EA  U J M  M  No
2 0008-2305                              SCREW,PAN HEAD,M2.3X6                    A             2.00000 EA  U J M  M  No
1 800-0592                               ASY,SUB,PALLET,0910,4PK,C/S,288 PER      A             0.00438 EA  U J M  M  No
2 810-1339                               PKG,PALLET,CORR.PAPER,43.5x49x4.5        A             1.00000 EA  U P M  P  No
2 820-0157                               LABEL,DECAL,BARCODE,4x2,FINAL PKG,FHK    S   4988     57.00000 EA  U J M  M  No
3 810-1157                               LABEL,DECAL,4X2,WHT                      A             1.00000 EA  U P M  P  No
2 0000-7513                              PKG,EXT,43X2X7/32,HORIZONTAL             0             2.00000 EA  U J M  M  No
2 0000-7516                              PKG,EXT,39X2X7/34,HORIZONTAL             0             2.00000 EA  U J M  M  No


The first number of each line basically lets us know how many white spaces are needed to be removed from in front of the 3rd column in order for the following columns to line up correctly. My posting has removed the white spaces but if you could see it, it would have about 20-30 spaces between the 2nd and 3rd column depending on the first number. I can't seem to get the awk script to use the sub() function properly to replace a number of white spaces with just one or just to remove it altogether. I think its my regex. But I'm sure i'm skinning the cat the wrong way this time. Any help would be appreciated. I've read many posts on white space manipulation but none really help with this situation. The dump of the database is about 60,000 lines long. Too much to manually do line by line.


chris
Last edited by Franklin52; 12-22-2008 at 09:59 AM.. Reason: adding code tags
# 2  
Old 12-22-2008
Hi,

please post some examples of what your desired output should look like.
I don't quite understand how you mean the columns. So what exactly
is the 3rd column?

Regards

Chris
# 3  
Old 12-22-2008
I can't post anything better
The white spaces are removed from my posting. Is there a way I can post without that happening? Otherwise, I'd have to email you directly the examples of what it currently looks like and what is should look like.


chris
# 4  
Old 12-22-2008
Use code tags. Highlight your code-segment and press
the fourth button from the right of the above tool menu.
It is the litte "#" to the left of "<>".
This should preserve the whitespaces and give an
example of the desired output.
# 5  
Old 12-22-2008
what is should look like
--------------------------
Code:
1 21007527                          LID,DET,C/S,BULK,PEZ,7 1/2x18 1/2x X     A             1.00000 EA  U J M  M  No
1 21007528                          TRAY,DET,C/S,BULK,PEZ,7 1/2x18 1/2x X    A             1.00000 EA  U J M  M  No
1 820-1625                          LABEL,INFO,BULK C/S,900-0076             A             1.00000 EA  U J M  M  No
2 810-2399                          LABEL,DIESTRIKE,6X4                      A             1.00000 EA  U J M  M  No
1 900-0076-003                      CO,900-0076,C/S,UL,SMT,EN                A            12.00000 EA  U J M  M  No
2 800-0056                          ASSY,CO,SUB,MOUNTING_KIT,(SCREW/ANCHOR)  C   1552      2.00000 EA  U P M  P  No
3 810-1292                          BAG,POLY,CLR,2 X 3                       A   2748      1.00000 EA  L I O  M  No



What it does look like
------------------------
Code:
3 810-2024                              RES,FXD,10_OHM,1%,1/8W,MET               A             1.00000 EA  L I O  M  No
3 810-2373                              RES,FXD,10_OHM,5%,1/8W,CAR               O             1.00000 EA  U J M  M  No
3 810-2453                              CAP,CM,15pf,20%,AXIAL                    B   4136      2.00000 EA  L I O  M  No
3 820-0163                              LABEL,DECAL,.2X.6,WHT,PRINTED,CO/SMK,PCB 0             1.00000 EA  U J M  M  No
4 810-1119                             LABEL,.20X.60,WHT,PAPER,(FACTORY ID)     0             1.00000 EA  U J M  M  No
3 2505-9501                             ASY,SUB,SMOKE CHAMBER                    2   1685      1.00000 EA  U J M  M  No
4 0910-4101                            REFERENCE PLATE                          PR            1.00000 EA  U J M  M  No
4 0910-4102                            SOURCE PLATE                             3             1.00000 EA  L I O  M  No
4 0910-4103                            SOURCE HOLDER                            5             1.00000 EA  U J M  M  No
4 0915-4101                            SOURCE                                   A   2689      1.00000 EA  L I O  M  No
4 0915-4107                            CHAMBER,SMOKE                            PR            1.00000 EA  U J M  M  No
4 910-0118                             CHAMBER,PLASTIC HOUSING                  A   1769      1.00000 EA  L I O  M  No
3 950-0012                              SCHEMATIC,ATWD,BAT,CO/SMOKE,4.5V,UL      19  4304      0.00000 EA  U J M  M  No
2 800-0387                               ASY,SUB,PCBA,CO/SMK,VOICE,UL             B   3293      1.00000 EA  U J M  M  No
3 810-0990                              SPEAKER,MYLAR,8_OHM,36mm                 D   5502      1.00000 EA  U J M  M  No
3 810-1223                              RES,SMD,10K,5%,1/8W,0805                 O             1.00000 EA  U J M  M  No
3 810-1226                              RES,SMD,1K,5%,1/8W,0805                  O             1.00000 EA  U J M  M  No
3 810-1227                              CAP,SMD,47pF,10%                         O             1.00000 EA  U J M  M  No
3 810-1233                              INDUCTOR,200uh,0.5OHM,Radial             0             1.00000 EA  U J M  M  No


Thanks for the instruction on how to keep the form entact.
# 6  
Old 12-22-2008
So, try:

Code:
awk -F'[ ][ ]+' '\
NF==7{printf "%-13s %45s %15s %10s %10s %10s %s\n", $1,$2,$3,$4,$5,$6,$7};\
NF==6{printf "%-13s %45s %15s %10s %10s %10s\n", $1,$2,$3,$4,$5,$6}' file4

The code uses multiple spaces as field delimiter. It works pretty well
except for cases where there is only one space as delimiter. I would replace
them with another little script and then run this one. Sorry, but at
the moment i have no time.

HTH Chris
# 7  
Old 12-22-2008
Thanks Chris
I played around with your snippet of code. It is definitely useful but it has solved my problem just yet. Although I get alignment on column 3, the following columns are out of place. Any other suggestions? Is there any way to set a variable based on the first column and subtract spaces from between the 2nd and 3rd columns?
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Script to remove spaces and mv a file

I've tried various solutions to move a file name with spaces and nothing seems to work. I need to take a date as input, prepend it to a filename with spaces then remove the spaces and mv the file to the new name. #!/bin/ksh # if (( $# != 1 )) then echo "Usage: `basename $0` <DATE> " ... (5 Replies)
Discussion started by: w_s_s
5 Replies

2. Shell Programming and Scripting

Why does this awk script not work correctly?

I have a large database with English on the left hand side and Indic words on the left hand. It so happens that since the Indic words have been entered by hand, there are duplicates in the entries. The structure is as under: English headword=Indic gloss,Indic gloss A small sample will... (6 Replies)
Discussion started by: gimley
6 Replies

3. AIX

Showmount don't show NFS4

Configure nfs4 on aix ok i did all chnfsdom #return correct domain chnfs -r /export chnfs -p /export configured and shared pseudo root #etc/exports /export -nfsroot /var/pubblica -exname=/export/pubblica,vers=4,sec=sys:krb5p:krb5i:krb5:dh,rw exportfs -a mount correct mount... (1 Reply)
Discussion started by: Linusolaradm1
1 Replies

4. Shell Programming and Scripting

Remove spaces / tabs from variable in script

I want to remove extra spaces from variable in aix script. We retrieve the data from oracle database and then print the values. We have a value on 90th position. When we execute the query on sqlplus it shows the length of 90th position as 3, but when we use the same query in aix script it shows... (5 Replies)
Discussion started by: lodhi1978
5 Replies

5. Shell Programming and Scripting

AWK: Remove spaces before processing each line?

Hi, all I have a file containing the following data: name: PRODUCT_1 date: 2010-01-07 really_long_name: PRODUCT_ABCDEFG I want to get the date (it is "2010-01-07" here), I could use the following code to do that: awk... (6 Replies)
Discussion started by: kevintse
6 Replies

6. Shell Programming and Scripting

How to remove spaces using awk,sed,perl?

Input: 3456 565 656 878 235 8 4 8787 3 7 35 878 Expected output: 3456 565 656 878 235 8 4 8787 3 7 35 878 How can i do this with awk,sed and perl? (10 Replies)
Discussion started by: cola
10 Replies

7. Shell Programming and Scripting

how to remove blank spaces of a file with awk??

hello how to remove blank spaces of a file with awk?? i´m trying awk '{gsub(" ","",$0); print $0;}' filename.txt but it answers syntax error near line first of all i did this for download from netbackup database jobs privilege bpdbjobs -report -M sv88 -gdm -header |cut -c-1024... (4 Replies)
Discussion started by: pabloli150
4 Replies

8. Shell Programming and Scripting

Don't show keyboard input on terminal

I am developing a script that will run with '/bin/ksh' shell. The script is intended to receive a password by keyboard input, but for security reasons I would like to hide what the user is typing. The keyboard input is being caught by 'read' command. exmaple : echo "Please type your new... (1 Reply)
Discussion started by: marianor31
1 Replies

9. Shell Programming and Scripting

Variable with $ do not show correctly

Hey guys i need help i have a script to push a password to a remote server the only problem is the $ENCRYPT variable has $'s in it (from the encrypted password in the shadow file) and they drop out when apending to the shadow file via the usermod command so $1$Q/6a08n$EoAcBuR/YnoCQC shows up as... (3 Replies)
Discussion started by: insania
3 Replies

10. Linux

Why don't my clock show am or pm?

Is it not possible to get the "Digital" clock in KDE 3.3 to show am or pm? Well I just noticed the plain clock setting is the only one that shows it. (2 Replies)
Discussion started by: CTroxtell21
2 Replies
Login or Register to Ask a Question