How to format file into comma separated text file?

Login or Register to Ask a Question and Join Our Community

How to format file into comma separated text file?

Tags

Login to Discuss or Reply to this Discussion in Our Community

Top Forums Shell Programming and Scripting How to format file into comma separated text file?

06-08-2011

Registered User

60, 0

Join Date: Jul 2007

Last Activity: 18 December 2011, 2:24 PM EST

Posts: 60

Thanks Given: 11

Thanked 0 Times in 0 Posts

How to format file into comma separated text file?

Hi Guys,

I have text file which is tab/space separated but I want it to re-format into a comma separated and trim the spaces in between. Can someone spare me a perl or sed script that can do the job?

INPUT FILE:

Code:

500010245623                500 21-APR-11 05.58.21 PM                                                                1
500010245637                500 21-APR-11 05.58.21 PM                                                                0
500010245644                500 21-APR-11 05.58.21 PM                                                                1
500010245645                500 21-APR-11 05.58.21 PM                                                                1
500010245654                500 21-APR-11 05.58.21 PM                                                                1
500010245662                500 21-APR-11 05.58.21 PM                                                                1
500010245664                500 21-APR-11 05.58.21 PM                                                                1
500010245666                500 21-APR-11 05.58.21 PM                                                                1
500010245667                500 21-APR-11 05.58.21 PM                                                                1
500010245677                500 21-APR-11 05.58.21 PM                                                                1
500010245679                500 21-APR-11 05.58.21 PM                                                                1

DESIRED OUTPUT:

Code:

500010245623,500,21-APR-11 05.58.21 PM,1
500010245637,500,21-APR-11 05.58.21 PM,0
500010245644,500,21-APR-11 05.58.21 PM,1
500010245645,500,21-APR-11 05.58.21 PM,1
500010245654,500,21-APR-11 05.58.21 PM,1
500010245662,500,21-APR-11 05.58.21 PM,1
500010245664,500,21-APR-11 05.58.21 PM,1
500010245666,500,21-APR-11 05.58.21 PM,1
500010245667,500,21-APR-11 05.58.21 PM,1
500010245677,500,21-APR-11 05.58.21 PM,1
500010245679,500,21-APR-11 05.58.21 PM,1

Thanks in advance.

Br,
Pinpe

pinpe

View Public Profile for pinpe

Find all posts by pinpe

06-08-2011

Registered User

124, 29

Join Date: May 2011

Last Activity: 16 July 2018, 1:31 AM EDT

Location: India

Posts: 124

Thanks Given: 2

Thanked 29 Times in 29 Posts

Code:

 
perl -lane 'BEGIN{$,=","}print @F' input

getmmg

View Public Profile for getmmg

Find all posts by getmmg

06-08-2011

Registered User

18, 2

Join Date: Jun 2011

Last Activity: 24 October 2011, 10:38 AM EDT

Posts: 18

Thanks Given: 0

Thanked 2 Times in 2 Posts

Code:

sed -i 's/[ |\t]\+/,/g' input-file

---------- Post updated at 06:32 AM ---------- Previous update was at 06:28 AM ----------

But I see from your example that you want single spaces to not be replaced with commas, so you should use:

Code:

sed 's/ \{2,\}/,/g' input-file | sed 's/\t\+/,/g' > output-file

---------- Post updated at 06:33 AM ---------- Previous update was at 06:32 AM ----------

the output will be:

Code:

500010245623,500 21-APR-11 05.58.21 PM,1
500010245637,500 21-APR-11 05.58.21 PM,0
500010245644,500 21-APR-11 05.58.21 PM,1
500010245645,500 21-APR-11 05.58.21 PM,1
500010245654,500 21-APR-11 05.58.21 PM,1
500010245662,500 21-APR-11 05.58.21 PM,1
500010245664,500 21-APR-11 05.58.21 PM,1
500010245666,500 21-APR-11 05.58.21 PM,1
500010245667,500 21-APR-11 05.58.21 PM,1
500010245677,500 21-APR-11 05.58.21 PM,1
500010245679,500 21-APR-11 05.58.21 PM,1

ciupinet

View Public Profile for ciupinet

Find all posts by ciupinet

06-08-2011

Registered User

60, 0

Join Date: Jul 2007

Last Activity: 18 December 2011, 2:24 PM EST

Posts: 60

Thanks Given: 11

Thanked 0 Times in 0 Posts

Hi getmmg/ciupinet,

Thanks for your prompt response. I forgot something, I want to put some headers on the output file. Because I want them to be separated into 4, I need to have also 4 headers as shown below...

DESIRED OUTPUT:

Code:

Header1,Header2,Header3,Header4
500010245623,500,21-APR-11 05.58.21 PM,1
500010245637,500,21-APR-11 05.58.21 PM,0
500010245644,500,21-APR-11 05.58.21 PM,1
500010245645,500,21-APR-11 05.58.21 PM,1
500010245654,500,21-APR-11 05.58.21 PM,1
500010245662,500,21-APR-11 05.58.21 PM,1
500010245664,500,21-APR-11 05.58.21 PM,1
500010245666,500,21-APR-11 05.58.21 PM,1
500010245667,500,21-APR-11 05.58.21 PM,1
500010245677,500,21-APR-11 05.58.21 PM,1
500010245679,500,21-APR-11 05.58.21 PM,1

Thanks in advance.

Br,
Pinpe

pinpe

View Public Profile for pinpe

Find all posts by pinpe

06-08-2011

Registered User

124, 29

Join Date: May 2011

Last Activity: 16 July 2018, 1:31 AM EDT

Location: India

Posts: 124

Thanks Given: 2

Thanked 29 Times in 29 Posts

Code:

 
perl -lane 'BEGIN{print "Header1,Header2,Header3,Header4"}print "$F[0],$F[1],$F[2] $F[3],$F[4]"' input

getmmg

View Public Profile for getmmg

Find all posts by getmmg

06-08-2011

Registered User

894, 183

Join Date: Jul 2010

Last Activity: 2 November 2018, 11:07 AM EDT

Location: IN

Posts: 894

Thanks Given: 15

Thanked 183 Times in 174 Posts

--ignore the post--

michaelrozar17

View Public Profile for michaelrozar17

Find all posts by michaelrozar17

06-08-2011

Registered User

60, 0

Join Date: Jul 2007

Last Activity: 18 December 2011, 2:24 PM EST

Posts: 60

Thanks Given: 11

Thanked 0 Times in 0 Posts

Quote:

Originally Posted by getmmg

Code:

 
perl -lane 'BEGIN{$,=","}print @F' input

Hi getmmg,

This is the output of your perl script...

Code:

100116144521,100,15-APR-11,10.41.58,AM,1
100116144524,100,15-APR-11,10.41.58,AM,1
100116144549,100,15-APR-11,10.41.58,AM,1
100116145555,100,15-APR-11,10.41.58,AM,1
100116145559,100,15-APR-11,10.41.58,AM,1
100116145562,100,15-APR-11,10.41.58,AM,1
100116145563,100,15-APR-11,10.41.58,AM,1
100116145603,100,15-APR-11,10.41.58,AM,1
100116146597,100,15-APR-11,10.41.58,AM,1
100116146601,100,15-APR-11,10.41.58,AM,1
100116146604,100,15-APR-11,10.41.58,AM,1
100116146606,100,15-APR-11,10.41.58,AM,1
100116146609,100,15-APR-11,10.41.58,AM,1
100116146613,100,15-APR-11,10.41.58,AM,1
100116146615,100,15-APR-11,10.41.58,AM,1
100116146616,100,15-APR-11,10.41.58,AM,1
100116146617,100,15-APR-11,10.41.58,AM,1

If you will notice the date '21-APR-11 05.58.21 PM' become also in comma separated. I don't want the date to be in comma separated. I want to treat them as a whole field.

Can you treat the date as one field as shown below?

Code:

Header1,Header2,Header3,Header4
500010245623,500,21-APR-11 05.58.21 PM,1       
500010245637,500,21-APR-11 05.58.21 PM,0       
500010245644,500,21-APR-11 05.58.21 PM,1       
500010245645,500,21-APR-11 05.58.21 PM,1       
500010245654,500,21-APR-11 05.58.21 PM,1       
500010245662,500,21-APR-11 05.58.21 PM,1       
500010245664,500,21-APR-11 05.58.21 PM,1       
500010245666,500,21-APR-11 05.58.21 PM,1       
500010245667,500,21-APR-11 05.58.21 PM,1       
500010245677,500,21-APR-11 05.58.21 PM,1       
500010245679,500,21-APR-11 05.58.21 PM,1

Thanks in advance mate!

Br,
Pinpe

pinpe

View Public Profile for pinpe

Find all posts by pinpe

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

How to cut a pipe delimited file and paste it with another file to form a comma separated outputfile

Hello ppl I have a requirement to split (cut in unix) a file (A.txt) which is a pipe delimited file into A1.txt and A2.txt Now I have to join (paste in unix) this A2.txt with external file A3.txt to form output file A4.txt which should be CSV (comma separated file) so that third party can...

2. Shell Programming and Scripting

Convert comma separated file to fix length

Hi, I am converting a comma separated file to fixed field lenght and I am using that: COLUMNS="25 24 67 26 39 63 20 34 35 14 397" ( cat $indir/input_file.dat | \ $AWK -v columns="$COLUMNS" ' BEGIN { FS=","; OFS=""; split(columns, arr, " "); } { for(i=1; i<=NF;...

3. Shell Programming and Scripting

How to split the comma separated file?

Hi, I have a filein unix like ABC,CDE BCD,KHL and the output i need is like column1 column2 ABC,CDE ABC ABC,CDE CDE BCD,KHL BCD BCD,KHL KHL. Can some body help me out? Hi, The code is working fine. But in my file each row does not have always 1 comma. It may...

4. Shell Programming and Scripting

Comma separated file

Hi all, I have the following files types: FileA: 100, 23, 33, FileB: 22, 45, 78, and i want to make File C: 100,22 23,45 33,78 any nice suggestions for making it easy.

5. Shell Programming and Scripting

Inserting string in between field in comma separated file

Hello Mates, I have one txt file having commo seperated values. I have to insert string "FALSE" in 2nd field from the end. E.G SE18 6RN,,,,5439070,1786840,,1000002148671600,123434 Out put should be: SE18 6RN,,,,5439070,1786840,FALSE,1000002148671600,123434 Can some one help me to...

6. Shell Programming and Scripting

parsing comma separated file

Hi, I have a file with th elist of patches separated by comma, like below: patch1, patch 2, patch 3................ t\The number of patches is not known as it changes every time. I need assistance in writing a routine such as it will take patch1 as first variable and performs the...

7. Shell Programming and Scripting

How to format file into comma separated field

Guys, Need you help, i have a a file content that look like this. Nokia 3330 <spaces><spaces><more spaces>+76451883874 Nokia 3610 +87467361615 so on and so forth, - there are so many spaces in between. - e.g....

8. Shell Programming and Scripting

Converting comma separated to pipe delimited file

Hi, I came across a very good script to convert a comma seperated to pipe delimited file in this forum. the script serves most of the requirement but looks like it does not handle embedded double quotes and commas i.e if the input is like 1234, "value","first,second", "LDC5"monitor",...

9. Shell Programming and Scripting

Convert comma text file to Column in html format

I am trying to generate a report with below file : File1 : EQADM,edrtere9-phys,8122caef0,gpatmon,/bin/ksh,nuten Erick EQADM,edrtere11-phys,8227caef0,gpatmon,/bin/ksh,nuten Erick EQADM,edrtere3-phys,822caef0,gpatmon,/bin/ksh,nuten Erick can you help me convert it to html and add...

10. UNIX for Dummies Questions & Answers

Remove whitespaces between comma separated fields from file

Hello all, I am a unix dummy. I am trying to remove spaces between fields. I have the file in the following format 12332432, 2345 , asdfsdf ,100216 , 9999999 12332431, 2341 , asdfsd2 ,100213 , 9999999 &...

Login or Register to Ask a Question