Shell, Grep and Sed

Solved/Closed
Bugz - Dec 1, 2008 at 05:51 AM
Sacabouffe
Posts
9418
Registration date
Saturday August 18, 2007
Status
Member
Last seen
May 29, 2009
- Dec 5, 2008 at 12:08 PM
Hello, I need help. I have to sort information from a file and place into excel
I no that I have use a shell with the cmds 'grep' and 'sed' but unsure how to go about it

The file looks like this:

26/10/01 686856 70.00
26/10/01 STANDING ORDER 1301.25
27/10/01 686849 580.00
28/10/01 653937 21.00
29/10/01 653938 20.00
29/10/01 686855 76.72
29/10/01 DIRECT DEBIT 10.00
29/10/01 DIRECT DEBIT 44.00
29/10/01 STANDING ORDER 23.00


I have to sort it in to 3 columns
Date, description and money

Any help would be great thx

3 replies

Sacabouffe
Posts
9418
Registration date
Saturday August 18, 2007
Status
Member
Last seen
May 29, 2009
19
Dec 1, 2008 at 04:44 PM
Hi
Mmm... These strings are rather annoying because they correspond to 2 columns in fact.
The only idea I have (surely not the best) is first to replace the strings composed by 2 words and a space by only one full string.
Are STANDING ORDER and DIRECT DEBIT the only kind of strings you have ?
If it's the case, what I suggest is (suppose your file is called data.txt) :
sed 's/G O/G_O/g' data.txt > data_mod.txt
sed 's/T D/T_D/g' data_mod.txt > data.txt
rm data_mod.txt
Then you really have 3 columns and you can extract them with awk for example, it is :
cat data.txt | awk -F " " '{print $1}' > column1.txt
cat data.txt | awk -F " " '{print $2}' > column2.txt
cat data.txt | awk -F " " '{print $3}' > column3.txt
At last, you can modify the second file column2.txt to have the second column like it was :
sed 's/G_O/G O/g' column2.txt > column2_mod.txt
sed 's/T D/T_D/g' column2_mod.txt > column2.txt
rm column2_mod.txt
Is it more or less what you wanted to do ?
See you !
1
jipicy
Posts
40805
Registration date
Wednesday August 27, 2003
Status
Moderator
Last seen
August 10, 2020
17
Dec 4, 2008 at 12:04 PM
Hi,
[tmpfs]$ cat file.txt
26/10/01 686856 70.00
26/10/01 STANDING ORDER 1301.25
27/10/01 686849 580.00
28/10/01 653937 21.00
29/10/01 653938 20.00
29/10/01 686855 76.72
29/10/01 DIRECT DEBIT 10.00
29/10/01 DIRECT DEBIT 44.00
29/10/01 STANDING ORDER 23.00

[tmpfs]$ sed 's/ /,/1;s/\(.*\) \(.*\)/\1,\2/' file.txt
26/10/01,686856,70.00
26/10/01,STANDING ORDER,1301.25
27/10/01,686849,580.00
28/10/01,653937,21.00
29/10/01,653938,20.00
29/10/01,686855,76.72
29/10/01,DIRECT DEBIT,10.00
29/10/01,DIRECT DEBIT,44.00
29/10/01,STANDING ORDER,23.00

[tmpfs]$ 
;-))
0
Sacabouffe
Posts
9418
Registration date
Saturday August 18, 2007
Status
Member
Last seen
May 29, 2009
19
Dec 5, 2008 at 12:08 PM
Hi

sed 's/ /,/1;s/\(.*\) \(.*\)/\1,\2/' file.txt
O_o
I'll try to understand... one of these days... :-DDD

See you ;-)
0