Change line into fixed columns

Hello People,

I want to create a script in korn shell that splits every lines of an LDAP export into columns. After, I want to import it as csv into Excel.

Let me give an example 2 lines :

user1,ou=SL7,ou=Operations,ou=TEST,o=company

user2,ou=SL3,ou=Operations,ou=TEST,ou=company,o=company

Export:
user1 ou=SL7 ou=Operations ou=TEST o=company
user2 ou=SL3 ou=Operations ou=TEST o=company
So every line has to be splitted after "," character. Every line have to be sorted by last column. (Not ever line has the same number of columns).
If may be that user3 has a supplemantary column. user3,ou=Admin Group ,ou=SL3,ou=Operations,ou=TEST,ou=company,o=company

Is this possible in shell or is perl the better language?

Thanks for every answer

Kind regards,
David

I don't understand the requirements. That file is already comma separated, so EXCEL should read it like a charm. What do you mean by

?

Hello,

Sorry it is quite difficult to explain.

Right, it is possible to import the fields in excel because is is comma seperated. The problem is that some users have not the same number of columns than others.

Example :

user1=ou=xxx,ou=yyy,o=zzz
user2=ou=www,ou=xxx,ou=yyy,o=zzz

so in this case if I'll import in excel,

xxx i in the same column than www. That's not what I want. I want to sort it right ajusted.

user1 "-   blank     " ou=xxx,ou=yyy,o=zzz
user2 ou=www  ou=xxx,ou=yyy,o=zzz

I hope that it is understandable now

Thanks,
David

Ok. Now tell me..
1) Is all other columns are same for other users too?(i know you have used dummy value here but still have a doubt)
2) If any column value is missing then are sure that which column it is?(here columns second). If not how we can check which column value is missing..?

Please explain:)

Still not clear. Where does the "-" sign come from? When to output blanks, when commas? Is there a max count of columns? Is there a fixed set of column values? What is "right-adjusted"?

Ok I try to explain again:

Some lines have fields that are 5 times comma seperated, other lines are 3 times comma seperated. Now I want that the last field (after the last comma) of every line is on the same column. the field before (after the second last comma) of every line is on the same column.

line1:aa,bb,cc,dd,ee
line2:cc,dd,ee
line3:bb,cc,dd,ee

line 1 output in 5 columns (column 5th position: ee, 4th position: dd, 3rd position: cc, 2nd position: bb, 1st position: aa)
line 2 output in 3 columns (column 5th position: ee, 4th position: dd, 3rd position: cc) 2nd and 1st are blank.
line 3 output in 4 columns (column 5th position: ee, 4th position: dd, 3rd position: cc, 2nd position: bb) 1st is blank

I hope it is clear now.

Kind regards,
David

try something like this..

This works for your given above input only..

awk -F "[,:]" '{for(i=2;i<=(7-NF);i++){$i=" Blank "}}1' OFS="," file

This will work for your given example, and shove in empty fields until total no. of fields is 6 (you may want to replace printf "," with printf "Blank,"):

awk -F, '{printf "%s", $1;
         for (i=0;i<6-NF;i++) printf ",";
         for (i=2;i<=NF;i++) printf ",%s", $i; print ""
         }' file

Hello,

Thank you for the effort but I think that is doesn't work... I didn't get the prefered format.

I sent you the input an output I wanted.

Please have a look what I exaclty want. Do you have an other solution?

Please have a look on the attachment.

Thank you very much,

David

Example if you want to test:

alayouni,ou=SL7,ou=Operations,ou=testCARGO,ou=testGroup,ou=test,o=test.lu
apalermo,ou=Outstation,ou=Outstations,ou=Airline,ou=testGroup,ou=test,o=test.lu
dbruck,ou=Airline Safety,ou=Airline Safety,ou=Airline,ou=testGroup,ou=test,o=test.lu
pcoelhomonte,ou=SL7,ou=Operations,ou=testCARGO,ou=testGroup,ou=test,o=test.lu
jmartinsvaz,ou=SL7,ou=Operations,ou=testCARGO,ou=testGroup,ou=test,o=test.lu
hklein,ou=Atelier Mecanique,ou=Ground Equipment,ou=Direction testCARGO,ou=testCARGO,ou=testGroup,ou=test,o=test.lu
mcomazzi,ou=Communication,ou=Corporate Communication,ou=General Services,ou=testGroup,ou=test,o=test.lu
npuscasu,ou=Key Account,ou=Airport Services,ou=Airline,ou=testGroup,ou=test,o=test.lu
fladwein,ou=Assurance Qualite,ou=Assurance Qualite,ou=Airline,ou=testGroup,ou=test,o=test.lu
jcostasemedo,ou=Ramp Services,ou=Chefs d'equipes,ou=Rampistes,ou=Ramp Handling,ou=Airport Services,ou=Airport Services,ou=Airline,ou=testGroup,ou=test,o=test.lu
nekpe,ou=Ramp Services,ou=Chefs d'equipes,ou=Rampistes,ou=Ramp Handling,ou=Airport Services,ou=Airport Services,ou=Airline,ou=testGroup,ou=test,o=test.lu
rvieirasoare,ou=Ramp Services,ou=Chefs d'equipes,ou=Rampistes,ou=Ramp Handling,ou=Airport Services,ou=Airport Services,ou=Airline,ou=testGroup,ou=test,o=test.lu

Well, then, replace the "6-NF" in line 2 with "11-NF" and test and show the result. How come you expect a correct result on your real file if you give a sample that does not represent reality?

Hello,

You're right.

It works! Thank you.

Kind regards,
David