Shell, Grep and Sed [Solved/Closed]

Report
-
Posts
9418
Registration date
Saturday August 18, 2007
Status
Member
Last seen
May 29, 2009
-
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

Posts
9418
Registration date
Saturday August 18, 2007
Status
Member
Last seen
May 29, 2009
17
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
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 4223 users have said thank you to us this month

Posts
40805
Registration date
Wednesday August 27, 2003
Status
Moderator
Last seen
December 5, 2019
17
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]$ 
;-))
Posts
9418
Registration date
Saturday August 18, 2007
Status
Member
Last seen
May 29, 2009
17
Hi

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

See you ;-)