HI,
I have a little conversion problem and I'm not well versed in sed. I
know there are several sed-wizards on this list so therefore I hope
somebody can make the solution.

I have a an ASCII table in .csv format.
The table has 24 columns and >28000 rows (it is a 20 days of data of a
weather-station in 1-minute intervals).
Now the first 5 columns are day,month,year,hour.minute. all values
separated by comma's.
I would like to convert that to "day-month-year hour:minute:second",
meaning inserting an extra ":0" after the minute column.

To give an example: the second row is:
1,8,2011,10,0,....,...,..., etc.
I want this to convert to one date-time column with format:
1-8-2011 10:0:0 or eventually one date and one time column. The rest
must stay as is (i.e. comma separated).

I hope somebody can help me out.
Thanks in advance,
Joep


--
ubuntu-users mailing list
ubuntu-users@lists.ubuntu.com
Modify settings or unsubscribe at: https://lists.ubuntu.com/mailman/listinfo/ubuntu-users

Joep L. Blom wrote:
> I have a little conversion problem and I'm not well versed in sed. I
> know there are several sed-wizards on this list so therefore I hope
> somebody can make the solution.
> I have a an ASCII table in .csv format.

> Now the first 5 columns are day,month,year,hour.minute. all values
> separated by comma's.
> I would like to convert that to "day-month-year hour:minute:second",
> meaning inserting an extra ":0" after the minute column.
> To give an example: the second row is:
> 1,8,2011,10,0,....,...,..., etc.
> I want this to convert to one date-time column with format:
> 1-8-2011 10:0:0 or eventually one date and one time column. The rest
> must stay as is (i.e. comma separated).

I'm not a sed-wizard either, but this is what I would use:

~/ > echo '1,8,2011,10,0,a,b,c,...'|sed -e s/,/-/ -e s/,/-/ -e s/,/ / -e s/,/:/ -e s/,/:0,/
1-8-2011 10:0:0,a,b,c,...


Nils

--
ubuntu-users mailing list
ubuntu-users@lists.ubuntu.com
Modify settings or unsubscribe at: https://lists.ubuntu.com/mailman/listinfo/ubuntu-users

On 27-06-12 08:03, Nils Kassube wrote:



I'm not a sed-wizard either, but this is what I would use:

~/> echo '1,8,2011,10,0,a,b,c,...'|sed -e s/,/-/ -e s/,/-/ -e s/,/ / -e s/,/:/ -e s/,/:0,/
1-8-2011 10:0:0,a,b,c,...


Nils


Nils,
Thanks for your reply. I have made a small bash-script with it and it
works as expected. However, I also want the first line - which has the
headers - also changed so that the first three headers are change to
date and the next two to time. However, the script I made and expected
to work doesn't. The script part with your solution works OK.

This is my script:
__________________________________________________ _____________
#!/bin/bash
#Simple program make the first 5 fields in a .csv-file into a date time
format

echo "$1 en $2"
;
sed -e '1,1s/day,month,year/date/' -e '1s/hour,minute/time/' -e
'2,$s/,/-/' -e '2,$s/,/-/' -e '2,$s/,/ /' -e '2,$s/,/:/' -e
'2,$s/,/:0,/' $1 > $2;

__________________________________________________ ___________
It doesn't give errors but line one is not changed. Perhaps you can see
my error? In the result file line 1 is unchanged.

Joep


--
ubuntu-users mailing list
ubuntu-users@lists.ubuntu.com
Modify settings or unsubscribe at: https://lists.ubuntu.com/mailman/listinfo/ubuntu-users

Joep L. Blom wrote:
> Thanks for your reply. I have made a small bash-script with it and it
> works as expected. However, I also want the first line - which has
> the headers - also changed so that the first three headers are
> change to date and the next two to time. However, the script I made
> and expected to work doesn't. The script part with your solution
> works OK. This is my script:
> __________________________________________________ _____________
> #!/bin/bash
> #Simple program make the first 5 fields in a .csv-file into a date
> time format
> echo "$1 en $2"
;
> sed -e '1,1s/day,month,year/date/' -e '1s/hour,minute/time/'
> -e '2,$s/,/-/' -e '2,$s/,/-/' -e '2,$s/,/ /' -e '2,$s/,/:/'
> -e '2,$s/,/:0,/' $1 > $2;
> __________________________________________________ ___________
> It doesn't give errors but line one is not changed. Perhaps you can
> see my error? In the result file line 1 is unchanged.

Two remarks:
- I don't see a reason to use "1,1s…" for the first command, "1s…"
should suffice.
- The backslash for the fifth command is not needed because the
expression is now inside quotes.

But of course that doesn't explain why line 1 is unchanged. I would
assume that the expressions you used don't match, e.g. because the
original line uses upper case instead of the lower case in your
expression or extra space characters somewhere. If you don't find the
reason, could you post the original first line of the file?


Nils

--
ubuntu-users mailing list
ubuntu-users@lists.ubuntu.com
Modify settings or unsubscribe at: https://lists.ubuntu.com/mailman/listinfo/ubuntu-users

It could be easier with awk

awk -F"," '{printf "%02d-%02d-%02d %02d:%02d ", $1, $2, $3, $4, $5; for ( i = 6; i <= NF; i++ ) printf "%s", $i; printf "
"}' your_file


or put that code in a file t.awk

BEGIN { FS="," }
{
Â*Â*Â* printf "%02d-%02d-%02d %02d:%02d ", $1, $2, $3, $4, $5
Â*Â*Â* for ( i = 6; i <= NF; i++ )
Â*Â*Â* Â*Â*Â* printf "%s", $i

Â*Â*Â* printf "
"
}

and execute awk -f t.awk your_file


2012/6/28 Nils Kassube <kassube@gmx.net>


Joep L. Blom wrote:

> Thanks for your reply. I have made a small bash-script with it and it

> works as expected. However, I also want the first line - which has

> the headers - also changed so that the first three headers are

> change to date and the next two to time. However, the script I made

> and expected to work doesn't. The script part with your solution

> works OK. This is my script:

> __________________________________________________ _____________

> #!/bin/bash

> #Simple program make the first 5 fields in a .csv-file into a date

> time format

> echo "$1 en Â*$2"
;

> sed -e '1,1s/day,month,year/date/' -e '1s/hour,minute/time/'

> -e '2,$s/,/-/' -e '2,$s/,/-/' -e '2,$s/,/ /' -e '2,$s/,/:/'

> -e '2,$s/,/:0,/' $1 > $2;

> __________________________________________________ ___________

> It doesn't give errors but line one is not changed. Perhaps you can

> see my error? In the result file line 1 is unchanged.



Two remarks:

- I don't see a reason to use "1,1s…" for the first command, "1s…"

should suffice.

- The backslash for the fifth command is not needed because the

_expression_ is now inside quotes.



But of course that doesn't explain why line 1 is unchanged. I would

assume that the expressions you used don't match, e.g. because the

original line uses upper case instead of the lower case in your

_expression_ or extra space characters somewhere. If you don't find the

reason, could you post the original first line of the file?





Nils



--

ubuntu-users mailing list

ubuntu-users@lists.ubuntu.com

Modify settings or unsubscribe at: https://lists.ubuntu.com/mailman/listinfo/ubuntu-users



--
Normand Marion

normand.marion@gmail.com



--
ubuntu-users mailing list
ubuntu-users@lists.ubuntu.com
Modify settings or unsubscribe at: https://lists.ubuntu.com/mailman/listinfo/ubuntu-users

2012/6/28 Normand Marion <normand.marion@gmail.com>:
> It could be easier with awk
>
> awk -F"," '{printf "%02d-%02d-%02d %02d:%02d ", $1, $2, $3, $4, $5; for ( i
> = 6; i <= NF; i++ ) printf "%s", $i; printf "
"}' your_file
>
> or put that code in a file t.awk
>
> BEGIN { FS="," }
> {
> Â*Â*Â* printf "%02d-%02d-%02d %02d:%02d ", $1, $2, $3, $4, $5
> Â*Â*Â* for ( i = 6; i <= NF; i++ )
> Â*Â*Â* Â*Â*Â* printf "%s", $i
> Â*Â*Â* printf "
"
> }
>
> and execute awk -f t.awk your_file

I realise that I should take the time to learn awk. Do you know of any
good tutorials?



Kind regards

Johnny Rosenberg
ジョニー・ãƒ*ーゼンãƒ�ーグ

>
>
> 2012/6/28 Nils Kassube <kassube@gmx.net>
>>
>> Joep L. Blom wrote:
>> > Thanks for your reply. I have made a small bash-script with it and it
>> > works as expected. However, I also want the first line - which has
>> > the headers - also changed so that the first three headers are
>> > change to date and the next two to time. However, the script I made
>> > and expected to work doesn't. The script part with your solution
>> > works OK. This is my script:
>> > __________________________________________________ _____________
>> > #!/bin/bash
>> > #Simple program make the first 5 fields in a .csv-file into a date
>> > time format
>> > echo "$1 en Â*$2"
;
>> > sed -e '1,1s/day,month,year/date/' -e '1s/hour,minute/time/'
>> > -e '2,$s/,/-/' -e '2,$s/,/-/' -e '2,$s/,/ /' -e '2,$s/,/:/'
>> > -e '2,$s/,/:0,/' $1 > $2;
>> > __________________________________________________ ___________
>> > It doesn't give errors but line one is not changed. Perhaps you can
>> > see my error? In the result file line 1 is unchanged.
>>
>> Two remarks:
>> - I don't see a reason to use "1,1s…" for the first command, "1s…"
>> should suffice.
>> - The backslash for the fifth command is not needed because the
>> expression is now inside quotes.
>>
>> But of course that doesn't explain why line 1 is unchanged. I would
>> assume that the expressions you used don't match, e.g. because the
>> original line uses upper case instead of the lower case in your
>> expression or extra space characters somewhere. If you don't find the
>> reason, could you post the original first line of the file?
>>
>>
>> Nils
>>
>> --
>> ubuntu-users mailing list
>> ubuntu-users@lists.ubuntu.com
>> Modify settings or unsubscribe at:
>> https://lists.ubuntu.com/mailman/listinfo/ubuntu-users
>
>
>
>
> --
> Normand Marion
>
> normand.marion@gmail.com
>
> --
> ubuntu-users mailing list
> ubuntu-users@lists.ubuntu.com
> Modify settings or unsubscribe at:
> https://lists.ubuntu.com/mailman/listinfo/ubuntu-users
>

--
ubuntu-users mailing list
ubuntu-users@lists.ubuntu.com
Modify settings or unsubscribe at: https://lists.ubuntu.com/mailman/listinfo/ubuntu-users

On 28 June 2012 16:16, Johnny Rosenberg <gurus.knugum@gmail.com> wrote:

> I realise that I should take the time to learn awk. Do you know of any
> good tutorials?

http://awk.info/?Learn

Additionally, the O'reilly AWK book is excellent.


--
Steve

When one person suffers from a delusion it is insanity. When many
people suffer from a delusion it is called religion.

--
ubuntu-users mailing list
ubuntu-users@lists.ubuntu.com
Modify settings or unsubscribe at: https://lists.ubuntu.com/mailman/listinfo/ubuntu-users

2012/6/28 Steve Flynn <anothermindbomb@gmail.com>:
> On 28 June 2012 16:16, Johnny Rosenberg <gurus.knugum@gmail.com> wrote:
>
>> I realise that I should take the time to learn awk. Do you know of any
>> good tutorials?
>
> http://awk.info/?Learn
>
> Additionally, the O'reilly AWK book is excellent.

Thanks! Sorry if I hi-jacked the thread.


Kind regards

Johnny Rosenberg
ジョニー・ãƒ*ーゼンãƒ�ーグ

>
>
> --
> Steve
>
> When one person suffers from a delusion it is insanity. When many
> people suffer from a delusion it is called religion.
>
> --
> ubuntu-users mailing list
> ubuntu-users@lists.ubuntu.com
> Modify settings or unsubscribe at: https://lists.ubuntu.com/mailman/listinfo/ubuntu-users

--
ubuntu-users mailing list
ubuntu-users@lists.ubuntu.com
Modify settings or unsubscribe at: https://lists.ubuntu.com/mailman/listinfo/ubuntu-users

On 28-06-12 08:21, Nils Kassube wrote:



Two remarks:
- I don't see a reason to use "1,1s…" for the first command, "1s…"
should suffice.
- The backslash for the fifth command is not needed because the
expression is now inside quotes.

But of course that doesn't explain why line 1 is unchanged. I would
assume that the expressions you used don't match, e.g. because the
original line uses upper case instead of the lower case in your
expression or extra space characters somewhere. If you don't find the
reason, could you post the original first line of the file?


Nils


Nils,
Thanks for the reply. You made me look more carefully to the first line
(I used hexedit to look at the key-codes) and silly me had not detected
the spaces in the text. Now it works perfect.

@Normand,
Thanks for the suggestion and the complete realization for my problem.
However, I'm as Johnny Rosenberg, I have never mastered AWK and I don't
need that very much. When I have more complex scripts to make I use perl
instead of bash. There I know how to manipulate strings.

Everybody, thanks for the help anyway,
Joep


--
ubuntu-users mailing list
ubuntu-users@lists.ubuntu.com
Modify settings or unsubscribe at: https://lists.ubuntu.com/mailman/listinfo/ubuntu-users