Need help pulling and formatting text with awk

[jfarmerjr]

I have a text file (actually many of them) that among other things has lines of text like the following:

AVCA   Advocat Q4 2009 Advocat Earnings Release $ 0.14  n/a  $ 0.21  9-Mar AMC

or

ACAD   ACADIA PHARMACEUTICALS INC Q4 2009 ACADIA PHARMACEUTICALS INC Earnings Release -$ 0.19  n/a  -$ 0.38  9-Mar AMC

The lines begin with a tab (or maybe it's just 4 spaces) There is a tab between the stock symbol and the company name, but all other fields are separated by 2 spaces.
What I need is the stock symbol, company name, estimate, actual, previous, and date in csv Using the above examples, what I'm looking for is:

AVCA,Advocat,0.14,n/a,0.21,9-Mar AMC

ACAD,ACADIA PHARMACEUTICALS INC,-0.19,n/a,-0.38,9-Mar AMC

I think awk is the right tool for the job (the awk command will make it into a bash script to do this everyday)

 

[feherke]

Hi

awk -F '\\t|  +' -vOFS=, '{gsub(/\$ /,"");print$2,$3,$5,$6,$7,$8}' /input/file

Tested with [tt]gawk[/tt] and [tt]mawk[/tt].

Feherke.

http://free.rootshell.be/~feherke/

 

[jfarmerjr]

Thank you kindly, it works like a charm!

 

[jfarmerjr]

One issue I have is that there is 2 spaces after each symbol before the comma on every line after running the command

 

[feherke]

Hi

I do not understand this. I compared my output with the one you posted and found no difference. Please post the output you get and using TGML tags mark with colors what should not be there.

By the way, which [tt]awk[/tt] implementation are you using ?

Feherke.

http://free.rootshell.be/~feherke/

 

[jfarmerjr]

I've attached a copy of an output file (named awktest.txt). I'm using whatever awk comes standard on ubuntu, I'm guessing it's gawk. The spaces issue really isn't a big deal, but is there anyway to exclude all the lines where the symbol (first field) has a '.' in it? Included in the share link is a file called converted.txt which is a sample of the converted html I'm starting with.

 

[feherke]

Hi

is there anyway to exclude all the lines where the symbol (first field) has a '.' in it?

awk -F '\\t|  +' -vOFS=, '[highlight]$2!~/\./[/highlight]{gsub(/\$ /,"");print$2,$3,$5,$6,$7,$8}' converted.txt

Regarding the "spaces issue" are you talking about the output lines looking like this ?

[tt],,,,,
,,,,,[/tt]

I guess you would like to eliminate them :

awk -F '\\t|  +' -vOFS=, '$2!~/\./[highlight]&&NF>=8[/highlight]{gsub(/\$ /,"");print$2,$3,$5,$6,$7,$8}' converted.txt

Feherke.

http://free.rootshell.be/~feherke/

 

[jfarmerjr]

The last one is essentially perfect, thank you kindly!!! Regarding the spaces issue, every line has 2 spaces after the symbol before the comma

example:

ACAD[COLOR=red red]  [/color],ACADIA PHARMACEUTICALS INC,-0.19 ,n/a ,-0.38 ,9-Mar AMC

 

[jfarmerjr]

Also seems to be adding a single space to the end of every field but the second (company name) But that's not a major issue. I'd like to thank you again for your exceptional help, so...Thank you!

 

[feherke]

Hi

Ah. There are also some characters with code 160 ( hexadecimal a0 ).

awk -F '\\t|[highlight][[/highlight] [highlight]\\xa0][[/highlight] [highlight]\\xa0][/highlight]+' -vOFS=, '$2!~/\./&&NF>=8{gsub(/\$ /,"");print$2,$3,$5,$6,$7,$8}' converted.txt

Feherke.

http://free.rootshell.be/~feherke/

 

[jfarmerjr]

Feherke, You Are Awesome! Works exactly as I need it to! Thanks for applying your obviously genius mind to my problem