a shell script to generate an excel sheet from a text file..

Shell Programming and Scripting
1

hi,
i have a text file that looks like this!
i want to generate an excel sheet out of it, removing all the junk data except the addresses
that look like

 . [32]Arrow Electrical Services
       Rotating Machinery, Electrical Contracting & Mining Specialists
       Onsite maintenance, breakdown service. Rotating machinery,
       electrical contracting, variable speed drives, mining services.
       109 Auburn St, Wollongong NSW 2500
       ph: (02) 4229 8094
            [33]www.arrow-electrical.com.au

and

. [109]JOEST Australia Pty Ltd
       70-72 Sheffield Rd, Welshpool WA 6106
       ph: (08) 9358 3740

i am new to linux and i managed to make that file using shell script.. and struggling with the furthur process,
can somebody help me out..

2

Something like this?

awk '$2 ~ /\. \[/{$1=x; print}' RS=\* FS='\n' OFS='\n' infile
3

thats good buddy..
but how do i generate an excel sheet?
that was the issue i was facing!:confused:

4

By changing OFS:

awk '$2 ~ /\. \[/{$1=x; print}' RS=\*  FS='\n' OFS='|' infile
5

cool thanks a lot buddy!
but can i ask for another favour?
the problem is the address length is varying and the order in the csv isnt proper..
like one record shows two cells of address while other 1 cell.. can we do anythingto that?

btw thanks again:)

6

Do you mean like this?

awk '$2 ~ /\. \[/{print $2,$(NF-2),$(NF-1),$NF}' RS=\* FS='\n' OFS='|' infile
7

nope! that command is deleting a lot of data! :frowning:

8

What about all the descriptive data? All in one field?

awk '$2 ~ /\. \[/{s=x; for(i=2;i<=NF;i++)s=s ($i~/\[|ph:/?OFS:x) $i; print s }' RS=\*  FS='\n' OFS='|' infile
9

thanks a lot buddy! but that didn't do the trick:)
thanks for the effort you put in..
i'l fiddle with it :slight_smile:

---------- Post updated 06-15-12 at 01:48 AM ---------- Previous update was 06-14-12 at 04:12 AM ----------

hey.. you modify that script to fit in the title in one field, description in one field, address in one field and ph in one and the website in one field!
sorry , but as a beginner i couldn't grasp much of it.. so!