Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Print each paragraph in a text file into excel file

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Print each paragraph in a text file into excel file
# 1  
Old 03-08-2013
Print each paragraph in a text file into excel file
Hi,

I have a below text file, which needs to be printed into attached excel file. Please help me.

Quote:
dn: cn=BR-PF-FTLRIG34,ou=role,O=test,C=com
user_ok: CN=Bruno Garcone,ou=employee,O=test,C=com

dn: cn=Mustafa B. Biterge,ou=employee,O=test,C=com
user_ok: cn=Jalal Khazanedari,ou=employee,O=test,C=com
user_ok: cn=Omer M. Gurpinar,ou=employee,O=test,C=com
user_ok: cn=Pedro Rene Romero Navarre,ou=employee,O=test,C=com

dn: cn=SMS-Las-Morochas,ou=list,O=test,C=com
user_bad: cn=Belkis Machado,ou=employee,O=test,C=com
Thanks
Book1.xls (42.0 KB) 
# 2  
Old 03-08-2013
You cannot write straight into an .xls file (well, at least not without a perl module or something clever like that). But you can make .csv file that you can load into Excel.
Try this:
Code:
awk '
NR==1||/^ *$/{rec++}
{
   sep=a[rec,$1]?"\n":""; 
   a[rec,$1]=a[rec,$1] sep $2
}
END{printf("dn\tuser_ok\tuser_bad\n"); 
  for(i=1;i<=rec;i++)
     printf("\"%s\"\t\"%s\"\t\"%s\"\n", a[i,"dn"], a[i,"user_ok"], a[i,"user_bad"])
}' FS=": *" input.txt  > output.csv

This will create a tab-separated csv file.
# 3  
Old 03-08-2013
Not sure i am getting below syntax errors.

Quote:
awk '
NR==1||/^ *$/{rec++}
{
sep=a[rec,$1]?"\n":"";
a[rec,$1]=a[rec,$1] sep $2
}
END{printf("dn\tuser_ok\tuser_bad\n");
for(i=1;i<=rec;i++)
printf("\"%s\"\t\"%s\"\t\"%s\"\n", a[i,"dn"], a[i,"user_ok"], a[i,"user_bad"])
}' FS=": *"


Quote:
test@unix1 >./test.sh input.txt > out.csv
awk: syntax error near line 4
awk: illegal statement near line 4
awk: syntax error near line 5
awk: illegal statement near line 5
awk: syntax error near line 9
awk: illegal statement near line 9
# 4  
Old 03-08-2013
What system are you on? What awk are you using?
If on Solaris, use nawk instead.
# 5  
Old 03-08-2013
Like mirni mentioned in previous post, using Perl is the best option to generate a spreadsheet from your input data.

But here is an awk code which produces a hash # separated output, I chose hash # because you already have comma , in your data.
Code:
awk -F':' ' BEGIN {
                print "user_ok#user_bad#dn"
} NF > 1 {
                a[++j] = $0
} END {
                for ( i = 1; i <= j; i++ )
                {
                        if ( a[i] ~ "user_ok" )
                                ok = (ok == "" ? a[i] : ok RS a[i] )
                        if ( a[i] ~ "user_bad")
                                bd = (bd == "" ? RS "#" a[i] : bd RS "#" a[i] )
                        if ( a[i] ~ "dn" )
                                dn = (dn == "" ? RS "##" a[i] : dn RS "##" a[i] )
                }
                print ok, bd, dn
} ' file

Now you can paste the output in spreadsheet and use Text to Columns feature and separate fields by putting hash # as field separator.

Finally some cell shifting might be required! Smilie
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Match text to lines in a file, iterate backwards until text or text substring matches, print to file

hi all, trying this using shell/bash with sed/awk/grep I have two files, one containing one column, the other containing multiple columns (comma delimited). file1.txt abc12345 def12345 ghi54321 ... file2.txt abc1,text1,texta abc,text2,textb def123,text3,textc gh,text4,textd... (6 Replies)
Discussion started by: shogun1970
6 Replies

2. UNIX for Beginners Questions & Answers

Convert Excel File (xls) to tab delimited text file on AIX

Hi i have a problem in my job i try to convert an excel file (xls extention) to text file (tab delimited), but no result with this comand cat xxx.xls > xxx.txt Do you have eny idea? PS: sorry for my english Thanks!! (4 Replies)
Discussion started by: frisso
4 Replies

3. Shell Programming and Scripting

Need help to write a shell script to convert text file to excel file.

Hi Everyone, I want your help to write a script which will take text file as input and on the basis of delimiter ":"script will create excel sheet. Example input: IpAdress:InstanceName:Port:ServerName 10.255.255.1:abc:2232:xyz_abc Output should be an excel sheet like below: Column... (8 Replies)
Discussion started by: akabhinav18
8 Replies

4. Shell Programming and Scripting

Need to convert delimited text file in UNIX to an Excel file

Dear Users , Need to convert delimited text files in UNix server to an Excel file and move the excel file to Windows environment. Am trying to automate the whole process. Can anyone share the ideas,if they have done similar ones before...Thanks -Meera (1 Reply)
Discussion started by: meerakrish
1 Replies

5. Shell Programming and Scripting

How to convert excel file to csv file or text file?

Hi all, I need to find a way to convert excel file into csv or a text file in linux command. The reason is I have hundreds of files to convert. Another complication is the I need to delete the first 5 lines of the excel file before conversion. so for instance input.xls description of... (6 Replies)
Discussion started by: johnkim0806
6 Replies

6. Shell Programming and Scripting

How to print excel file / csv file in solaris server ?

Hi, I have to print a excel file in solaris server. I am using lexmark printer with post script printer driver . But the problem is when i am using " lp -d <printer name> <somefile name.xls>" command it is not printing correctly. but for pdf file using the same command it printing correctly. ... (2 Replies)
Discussion started by: Sambuddha
2 Replies

7. Shell Programming and Scripting

Easy way to pull a paragraph from a text file?

I have a collection of text files that comprise a mailing list archive. I grep them to find an email that interests me, then open the file in a text editor to find the surrounding paragraph of text. Is there an easy way to do this from the shell instead? (2 Replies)
Discussion started by: CRGreathouse
2 Replies

8. UNIX for Dummies Questions & Answers

Output text from 1st paragraph in file w/ a specific string through last paragraph of file w/ string

Hi, I'm trying to output all text from the first paragraph in a file that contains a specific string through the last paragraph in that file that contains that string. Previously, I was outputting just each paragraph with that search string with: cat in_file | nawk '{RS=""; FS="\n";... (2 Replies)
Discussion started by: carpenn
2 Replies

9. UNIX for Dummies Questions & Answers

Script to generate text file from excel file

Hello, I have a excel file which has almost ten columns on the shared drive. I have to write a shell script to ftp that daily to unix server and then extract some columns from there and generate oracle standard text file. The columns should be in proper order and aligned properly, otherwise... (1 Reply)
Discussion started by: isingh786
1 Replies

10. Shell Programming and Scripting

text file to excel or csv

Hi, I have a text file like ---------------------- aaa|bbb|ccc|ddd|eee fff|gggg|hhhhhh|kkkk -------------------- I have a script which will transfer(thourgh FTP) this text file to windows system. But I want to covert it into excel or CSF then upload into windows system.. thanks (9 Replies)
Discussion started by: srikanthus2002
9 Replies
Login or Register to Ask a Question