Remove Spaces in UNIX generated csv file. 1

[tokerago13]

Hello,
I am generating a csv file via unix. The file is bening generated via Oracle SQL. The csv output for some reason has a chunk of space. I have tried using sed to remove the space but it does not work. I'm not very good with sed. Any help will be appreciated.
I have enclosed the file.
2672,joe.bloggs@hotmail.com,First Name,mary^M2672,joe.bloggs@hotmail.com,Last Name,jane^M2672,joe.bloggs@hotmail.
com,known As,^M2672,joe.bloggs@hotmail.com,Start Date,01-JUN-98^M2672,joe.bloggs@hotmail.com,Cost Code,BC15^M2672,joe.
bloggs@hotmail.com,Division Code^M,BC00^M2672,
joe.bloggs@hotmail.com,Business Unit,B000^M2672,joe.bloggs@hotmail.com,Supervisor First Name,joe^M2672,joe.bloggs@
hotmail.com,Supervisor Last Name,bloggs^M^M

The carriage returns are added deliberatly for data format
Thanks.

 

[feherke]

Hi

Where are the spaces there ? What [tt]sed[/tt] command tried and what means that "does not work". You may try the [tt]tr[/tt] command too. Theoretically :

sed 's/ *//g' inputfile > outputfile
[gray]# or[/gray]
tr -d ' ' < inputfile > outputfile

This will remove all spaces. Anyway, you did not say about exceptions. But try this way too, maybe is closer to what you want :

sed 's/ \+/ /g' inputfile > outputfile

Feherke.

http://rootshell.be/~feherke/

 

[tokerago13]

Thanks for the reply. The extract of the file is the one that I have enclosed. Counting, after the 5th line, there is a space after the comma. This is the way the file is generated and it shouldn't be. As the SQL code does it correctly if I run it outside of unix. I would like to get rid of the space. The ideas given have not yet helped me in achieving my aim, so that the extract wraps continuosly. I thought sed will be able to do this for me. Any more ideas will be much appreciated.
Thanks

 

[PHV]

[tt]sed 's!, *!,!g' /path/to/input > output[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[feherke]

Hi

Please post the desired result for the above input data.

Feherke.

http://rootshell.be/~feherke/

 

[tokerago13]

Thanks for all the input. But I'm not getting the desired result when I view via CSV in excel. What I desire is this:

2672,joe.bloggs@hotmail.com,First Name,mary^M2672,joe.bloggs@hotmail.com,Last Name,jane^M2672,joe.bloggs@hotmail.com,known As,^M2672,joe.bloggs@hotmail.com,Start Date,01-JUN 98^M2672,joe.bloggs@hotmail.com,Cost Code,BC15^M2672,joe.
bloggs@hotmail.com,Division Code^M,BC00^M2672,joe.bloggs@hotmail.com,Business Unit,B000^M2672,joe.bloggs@hotmail.com,Supervisor First Name,joe^M2672,joe.bloggs@hotmail.com,Supervisor Last Name,bloggs^M^M

Hope this helps. Thanks once again.

 

[feherke]

Hi

Are you sure, there are space characters ? I think that lines are wrapped. In which case you can concatenate the lines where the last character is the separator, with the next line :

sed '/,$/{N;s/\n//}' inputfile > outputfile

Feherke.

http://rootshell.be/~feherke/

 

[tokerago13]

Thanks. But I got the following error:
sed: command garbled: /,$/{N;s/\n//} - Any ideas?

 

[feherke]

Hi

I have GNU [tt]sed[/tt] 4.1.2. You not. The same thing in [tt]awk[/tt] :

awk '/,$/{l=$0;next}l{$0=l $0;l=""}1' inputfile > outputfile

Feherke.

http://rootshell.be/~feherke/

 

[tokerago13]

I really appreciate the fact that you keep coming up with very good ideas, but the awk i just tried did not work. I got the following error:

syntax:
awk '/,$/{l=$0;next}l{$0=l $0;l=""}1' CBT_25102005.csv > CBT_25102005.nocsv
error:
awk: syntax error near line 1
awk: bailing out near line 1

Any more ideas?
Thanks a lot

 

[stefanwagner]

^M is oracles way of saying 'new line', so I guess this is more readable:

2672,joe.bloggs@hotmail.com,First Name,mary
2672,joe.bloggs@hotmail.com,Last Name,jane
2672,joe.bloggs@hotmail.com,known As,
2672,joe.bloggs@hotmail.com,Start Date,01-JUN-98
2672,joe.bloggs@hotmail.com,Cost Code,BC15
2672,joe.bloggs@hotmail.com,Division Code,BC00
2672,joe.bloggs@hotmail.com,Business Unit,B000
2672,joe.bloggs@hotmail.com,Supervisor First Name,joe
2672,joe.bloggs@hotmail.com,Supervisor Last Name,bloggs

right?

seeking a job as java-programmer in Berlin:

http://home.arcor.de/hirnstrom/bewerbung

 

[tokerago13]

That is bang on what I intend to read I was adding ^M as I need to start a new line after each record. Any ideas on how I can get this reproduced in sql to be read by excel in csv format? Ideas please. Thanks a lot so far.

 

[PHV]

Can you please explain the REAL issue you have ?
Cr (^M) prob ? Space(s) after comma prob ?
Can't Excel get the data with an ODBC link ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[tokerago13]

Thanks everyone so much for the ideas contributed. I have resorted to getting the data a different way using pl/sql's utility UTL_FILES.
I have learned a few things about sed from feherke which I believe will come in handy soon
Thanks

 

[smicro]

feherke ,

THis post help me! Thanks!