Discussion:
split column
MOKRANI Rachid rachid.mokrani@ifpen.fr [sed-users]
2016-03-29 07:48:12 UTC
Permalink
Hi,

Do you have a simple proposal with sed / awk.

In this exemple, need to split column 7

Company A ; NAME A ; FIRSTNAME 1 ; JOB 1 ; Town 1 ; 145854 abVFrd ; COMMENT 1 ;



And obtain 8 columns (7 always a number - 8 always alphabetic)

Company A ; NAME A ; FIRSTNAME 1 ; JOB 1 ; Town 1 ; 145854 ; abVFrd ; COMMENT 1 ;



But sometime the colum 7 can have
; 145854 abVFrd ;
Need to have
; 145854 ; abVFrd;

; 145854;
Need to have
; 145854; ;


; abVFrd;
Need to have
; ; abVFrd;


; ;
Need to have
; ; ;





Company A;NAME A;FIRSTNAME 1;JOB 1;Town 1; 145854 abVFrd;COMMENT 1;

Company A;NAME A;FIRSTNAME 4;JOB 1;Town ;145862 dsfsd; COMMENT 5;

Company B;NAME T ;FIRSTNAME 1;JOB 2;Town 2;ABGHKYH; COMMENT 1235;

Company C;NAME M;FIRSTNAME 2;JOB 7;Town 1;259874; COMMENT 4;

Company Y;NAME G;FIRSTNAME 4;JOB 52;Town 1;; COMMENT 1;

Company Y;NAME F ;FIRSTNAME 8;JOB 82;Town 3;125478; COMMENT 15;



Need to obtain the result below.



Company A;NAME A;FIRSTNAME 1;JOB 1;Town 1; 145854;abVFrd;COMMENT 1;

Company A;NAME A;FIRSTNAME 4;JOB 1;Town ;145862;dsfsd; COMMENT 5;

Company B;NAME T ;FIRSTNAME 1;JOB 2;Town 2;;ABGHKYH; COMMENT 1235;

Company C;NAME M;FIRSTNAME 2;JOB 7;Town 1;259874;; COMMENT 4;

Company Y;NAME G;FIRSTNAME 4;JOB 52;Town 1;;; COMMENT 1;

Company Y;NAME F ;FIRSTNAME 8;JOB 82;Town 3;125478;; COMMENT 15;



Thanks,
Best regards.
__________________________
Avant d'imprimer, pensez à l'environnement ! Please consider the environment before printing !
Ce message et toutes ses piÚces jointes sont confidentiels et établis à l'intention exclusive de ses destinataires. Toute utilisation non conforme à sa destination, toute diffusion ou toute publication, totale ou partielle, est interdite, sauf autorisation expresse. IFP Energies nouvelles décline toute responsabilité au titre de ce message. This message and any attachments are confidential and intended solely for the addressees. Any unauthorised use or dissemination is prohibited. IFP Energies nouvelles should not be liable for this message.
__________________________


[Non-text portions of this message have been removed]
Thierry Blanc Thierry.Blanc@gmx.ch [sed-users]
2016-03-29 09:48:32 UTC
Permalink
ugly, but should work:

sed 's|;|\n|g' ff|sed -r 'n;n;n;n;n;s|([0-9 ]*)([ A-Za-z]*)|\1;\2|;n;n'
| sed 'N;N;N;N;N;N;N;s|\n|;|g'

what about non alphanumerical signs like %^&*#?
Post by MOKRANI Rachid ***@ifpen.fr [sed-users]
Hi,
Do you have a simple proposal with sed / awk.
In this exemple, need to split column 7
Company A ; NAME A ; FIRSTNAME 1 ; JOB 1 ; Town 1 ; 145854 abVFrd ; COMMENT 1 ;
And obtain 8 columns (7 always a number - 8 always alphabetic)
Company A ; NAME A ; FIRSTNAME 1 ; JOB 1 ; Town 1 ; 145854 ; abVFrd ; COMMENT 1 ;
But sometime the colum 7 can have
; 145854 abVFrd ;
Need to have
; 145854 ; abVFrd;
; 145854;
Need to have
; 145854; ;
; abVFrd;
Need to have
; ; abVFrd;
; ;
Need to have
; ; ;
Company A;NAME A;FIRSTNAME 1;JOB 1;Town 1; 145854 abVFrd;COMMENT 1;
Company A;NAME A;FIRSTNAME 4;JOB 1;Town ;145862 dsfsd; COMMENT 5;
Company B;NAME T ;FIRSTNAME 1;JOB 2;Town 2;ABGHKYH; COMMENT 1235;
Company C;NAME M;FIRSTNAME 2;JOB 7;Town 1;259874; COMMENT 4;
Company Y;NAME G;FIRSTNAME 4;JOB 52;Town 1;; COMMENT 1;
Company Y;NAME F ;FIRSTNAME 8;JOB 82;Town 3;125478; COMMENT 15;
Need to obtain the result below.
Company A;NAME A;FIRSTNAME 1;JOB 1;Town 1; 145854;abVFrd;COMMENT 1;
Company A;NAME A;FIRSTNAME 4;JOB 1;Town ;145862;dsfsd; COMMENT 5;
Company B;NAME T ;FIRSTNAME 1;JOB 2;Town 2;;ABGHKYH; COMMENT 1235;
Company C;NAME M;FIRSTNAME 2;JOB 7;Town 1;259874;; COMMENT 4;
Company Y;NAME G;FIRSTNAME 4;JOB 52;Town 1;;; COMMENT 1;
Company Y;NAME F ;FIRSTNAME 8;JOB 82;Town 3;125478;; COMMENT 15;
Thanks,
Best regards.
__________________________
Avant d'imprimer, pensez à l'environnement ! Please consider the environment before printing !
Ce message et toutes ses piÚces jointes sont confidentiels et établis à l'intention exclusive de ses destinataires. Toute utilisation non conforme à sa destination, toute diffusion ou toute publication, totale ou partielle, est interdite, sauf autorisation expresse. IFP Energies nouvelles décline toute responsabilité au titre de ce message. This message and any attachments are confidential and intended solely for the addressees. Any unauthorised use or dissemination is prohibited. IFP Energies nouvelles should not be liable for this message.
__________________________
[Non-text portions of this message have been removed]
------------------------------------
------------------------------------
Jim Hill gjthill@gmail.com [sed-users]
2016-03-29 15:35:22 UTC
Permalink
sed -E 's/(([^;]*;){6} *[0-9]*) ?/\1;/' should do it.
Post by Thierry Blanc ***@gmx.ch [sed-users]
sed 's|;|\n|g' ff|sed -r 'n;n;n;n;n;s|([0-9 ]*)([ A-Za-z]*)|\1;\2|;n;n'
| sed 'N;N;N;N;N;N;N;s|\n|;|g'
what about non alphanumerical signs like %^&*#?
Post by MOKRANI Rachid ***@ifpen.fr [sed-users]
Hi,
Do you have a simple proposal with sed / awk.
In this exemple, need to split column 7
Company A ; NAME A ; FIRSTNAME 1 ; JOB 1 ; Town 1 ; 145854 abVFrd ;
COMMENT 1 ;
Post by MOKRANI Rachid ***@ifpen.fr [sed-users]
And obtain 8 columns (7 always a number - 8 always alphabetic)
Company A ; NAME A ; FIRSTNAME 1 ; JOB 1 ; Town 1 ; 145854 ; abVFrd ;
COMMENT 1 ;
Post by MOKRANI Rachid ***@ifpen.fr [sed-users]
But sometime the colum 7 can have
; 145854 abVFrd ;
Need to have
; 145854 ; abVFrd;
; 145854;
Need to have
; 145854; ;
; abVFrd;
Need to have
; ; abVFrd;
; ;
Need to have
; ; ;
Company A;NAME A;FIRSTNAME 1;JOB 1;Town 1; 145854 abVFrd;COMMENT 1;
Company A;NAME A;FIRSTNAME 4;JOB 1;Town ;145862 dsfsd; COMMENT 5;
Company B;NAME T ;FIRSTNAME 1;JOB 2;Town 2;ABGHKYH; COMMENT 1235;
Company C;NAME M;FIRSTNAME 2;JOB 7;Town 1;259874; COMMENT 4;
Company Y;NAME G;FIRSTNAME 4;JOB 52;Town 1;; COMMENT 1;
Company Y;NAME F ;FIRSTNAME 8;JOB 82;Town 3;125478; COMMENT 15;
Need to obtain the result below.
Company A;NAME A;FIRSTNAME 1;JOB 1;Town 1; 145854;abVFrd;COMMENT 1;
Company A;NAME A;FIRSTNAME 4;JOB 1;Town ;145862;dsfsd; COMMENT 5;
Company B;NAME T ;FIRSTNAME 1;JOB 2;Town 2;;ABGHKYH; COMMENT 1235;
Company C;NAME M;FIRSTNAME 2;JOB 7;Town 1;259874;; COMMENT 4;
Company Y;NAME G;FIRSTNAME 4;JOB 52;Town 1;;; COMMENT 1;
Company Y;NAME F ;FIRSTNAME 8;JOB 82;Town 3;125478;; COMMENT 15;
Thanks,
Best regards.
__________________________
Avant d'imprimer, pensez à l'environnement ! Please consider the
environment before printing !
Post by MOKRANI Rachid ***@ifpen.fr [sed-users]
Ce message et toutes ses piÚces jointes sont confidentiels et établis à
l'intention exclusive de ses destinataires. Toute utilisation non conforme
à sa destination, toute diffusion ou toute publication, totale ou
partielle, est interdite, sauf autorisation expresse. IFP Energies
nouvelles décline toute responsabilité au titre de ce message. This message
and any attachments are confidential and intended solely for the
addressees. Any unauthorised use or dissemination is prohibited. IFP
Energies nouvelles should not be liable for this message.
Post by MOKRANI Rachid ***@ifpen.fr [sed-users]
__________________________
[Non-text portions of this message have been removed]
------------------------------------
------------------------------------
[Non-text portions of this message have been removed]
sharma__r@hotmail.com [sed-users]
2016-03-29 17:42:46 UTC
Permalink
Actually from the data shown it is column number 6 rather than 7 that is being processed. Hence, we do as follows:


sed -e '
s/;/\n/6; # mark the 6th field at its right end
s/^/;/; # add a dummy field
s/;/\n/6; # mark the 6th field at its left end
s/.//; # remove the dummy field


;# split the 6th field into their numeric & alphabetic counterparts
;# and then separate them by the separator semicolon
s/\(\n[ ]*[0-9]*\)[ ]*\([a-zA-Z]*\n\)/\1;\2/


;# finally, reverse the markers
y/\n/;/
' yourcsvfile




---In sed-***@yahoogroups.com, <***@...> wrote :
Hi,
Do you have a simple proposal with sed / awk.
In this exemple, need to split column 7

Company A ; NAME A ; FIRSTNAME 1 ; JOB 1 ; Town 1 ; 145854 abVFrd ; COMMENT 1 ;


And obtain 8 columns (7 always a number - 8 always alphabetic)

Company A ; NAME A ; FIRSTNAME 1 ; JOB 1 ; Town 1 ; 145854 ; abVFrd ; COMMENT 1 ;

But sometime the colum 7 can have
; 145854 abVFrd ;
Need to have
; 145854 ; abVFrd;

; 145854;
Need to have
; 145854; ;


; abVFrd;
Need to have
; ; abVFrd;


; ;
Need to have
; ; ;




[Non-text portions of this message have been removed]
Thierry Blanc Thierry.Blanc@gmx.ch [sed-users]
2016-03-29 17:59:37 UTC
Permalink
we can shorten ...

sed -r 's|;|\n|5;s|;|\n|5;s|\n([ 0-9]*)([a-zA-Z ]*)\n|;\1;\2;|'

1. replace the 5th separator with mark
2. replace the 6th! separator (6th becomes 5th after line above) with mark
3. add semicolon and reverse the markers

spaces remain as is.
Post by MOKRANI Rachid ***@ifpen.fr [sed-users]
Hi,
Do you have a simple proposal with sed / awk.
In this exemple, need to split column 7
Company A ; NAME A ; FIRSTNAME 1 ; JOB 1 ; Town 1 ; 145854 abVFrd ; COMMENT 1 ;
And obtain 8 columns (7 always a number - 8 always alphabetic)
Company A ; NAME A ; FIRSTNAME 1 ; JOB 1 ; Town 1 ; 145854 ; abVFrd ; COMMENT 1 ;
But sometime the colum 7 can have
; 145854 abVFrd ;
Need to have
; 145854 ; abVFrd;
; 145854;
Need to have
; 145854; ;
; abVFrd;
Need to have
; ; abVFrd;
; ;
Need to have
; ; ;
Company A;NAME A;FIRSTNAME 1;JOB 1;Town 1; 145854 abVFrd;COMMENT 1;
Company A;NAME A;FIRSTNAME 4;JOB 1;Town ;145862 dsfsd; COMMENT 5;
Company B;NAME T ;FIRSTNAME 1;JOB 2;Town 2;ABGHKYH; COMMENT 1235;
Company C;NAME M;FIRSTNAME 2;JOB 7;Town 1;259874; COMMENT 4;
Company Y;NAME G;FIRSTNAME 4;JOB 52;Town 1;; COMMENT 1;
Company Y;NAME F ;FIRSTNAME 8;JOB 82;Town 3;125478; COMMENT 15;
Need to obtain the result below.
Company A;NAME A;FIRSTNAME 1;JOB 1;Town 1; 145854;abVFrd;COMMENT 1;
Company A;NAME A;FIRSTNAME 4;JOB 1;Town ;145862;dsfsd; COMMENT 5;
Company B;NAME T ;FIRSTNAME 1;JOB 2;Town 2;;ABGHKYH; COMMENT 1235;
Company C;NAME M;FIRSTNAME 2;JOB 7;Town 1;259874;; COMMENT 4;
Company Y;NAME G;FIRSTNAME 4;JOB 52;Town 1;;; COMMENT 1;
Company Y;NAME F ;FIRSTNAME 8;JOB 82;Town 3;125478;; COMMENT 15;
Thanks,
Best regards.
__________________________
Avant d'imprimer, pensez à l'environnement ! Please consider the environment before printing !
Ce message et toutes ses piÚces jointes sont confidentiels et établis à l'intention exclusive de ses destinataires. Toute utilisation non conforme à sa destination, toute diffusion ou toute publication, totale ou partielle, est interdite, sauf autorisation expresse. IFP Energies nouvelles décline toute responsabilité au titre de ce message. This message and any attachments are confidential and intended solely for the addressees. Any unauthorised use or dissemination is prohibited. IFP Energies nouvelles should not be liable for this message.
__________________________
[Non-text portions of this message have been removed]
------------------------------------
------------------------------------
[Non-text portions of this message have been removed]

Loading...